MS Access VBA new Module subroutine calling excel file that is opened by Form Subroutine 1

[03Explorer]

Trying to create a module that I can call from the form code that will manipulate an open Excel file.
[Code Form-Subroutine]Dim MyXL As Object
Dim objActivdeWkb As Object
Set MyXL = CreateObject("Excel.Application")
'--Further down in the code I want to call a Subroutine in a Module so I can simplify coding repeating

Call SoftSkills()
[/Code]

[Code SoftSkills]MyXL.Sheets(1).copy after:=MyXL.Worksheets(MyXL.Worksheets.Count)
MyXL.ActiveSheet.range("A23") = Forms![frmAAS].cboPracticeType.Value & " CRM Assessment Form"
MyXL.Rows("32:41").Delete
MyXL.ActiveSheet.Protect[/Code]

I get an error in SoftSkills "Object Required" I believe it's tied to MyXL... do I need to reDefine? and then point to the open Excel file I am manipulating?

 

[MajP]

If you declare the variable at the top of your module instead of inside a subroutine it will be a module level variable available to all routines. If you declare it inside a routine it will be a local variable.

https://support.microsoft.com/en-us/help/141693/scope-of-variables-in-visual-basic-for-applications

You can also pass a local variable if you do not want to declare a module level variable
Public someSub ()
Call SoftSkills myXL
end sub

Public Sub SoftSkills(myXL as object)
.....
End sub

 

[SkipVought]

Hi,

All you’ve done is instantiate an Excel Application Object.

You have neither Added a new Workbook nor Opened an existing Workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

A part of your code that is in form or class module is available only when form is opened or class instantiated. Their variables are object properties, their procedures - methods (if any of them declared as public). So, as I proposed in my earlier reply to one of your posts, you may consider declaring objects and other variables in standard module if a part of your code that uses those variables is in standard module. Otherwise you have to pass the variable to subroutine as argument, as MajP above and Andy in the referred thread suggest.

combo

 

[03Explorer]

MajP, your suggestion nailed it! THANK YOU!!! =)

Calling the Subroutine with the variable myXL works perfectly!

[CODE Form-Subroutine]Public someSub ()
Dim MyXL As Object
Dim objActivdeWkb As Object
Set MyXL = CreateObject("Excel.Application")
'--Further down in the code I want to call a Subroutine in a Module so I can simplify coding repeating

Call SoftSkills(myXL)
End Sub
[/CODE]

[Code Module-Subroutine]Public Sub SoftSkills(myXL as object)
MyXL.Sheets(1).copy after:=MyXL.Worksheets(MyXL.Worksheets.Count)
MyXL.ActiveSheet.range("A23") = Forms![frmAAS].cboPracticeType.Value & " CRM Assessment Form"
MyXL.Rows("32:41").Delete
MyXL.ActiveSheet.Protect
End Sub [/CODE]