What is the trick to be able to execute from the Immediate window a Sub defined on a Sheet of an Excel xlsm file? (4 Viewers)

[mdlueck]

Greetings,

Working this time in Excel VBA, which is giving me challenges....

Excel spreadsheet, saved to xlsm file format so I can put VBA code in and execute it to manipulate cell contents on the sheet.

On the sheet, have a very basic "Hello world!" amount of initial functionality:

Option Explicit

Public Sub UpdateTestCaseFilenames()

....

End Sub

In the immediate window I am trying to just drop in

UpdateTestCaseFilenames

which comes up with error:

Compiler Error
Sub of Function not defined

What am I missing?

I am thankful,

 

[Edgar_]

That’s strange. It should work, but if it’s not found, it likely means it’s not defined in that scope. Are you sure you’re not trying to run the function from the Excel VBA IDE while it’s defined in the Access VBA IDE? Make sure you’re in the right environment.

 

[The_Doc_Man]

What Edgar_ said, ... or vice-versa - defined in Excel and you are trying to run it from Access.

 

[theDBguy]

I also think that might work; but also, maybe try moving the sub to This Worksheet or a separate standard module out of the specific sheet.

Sent from phone...

 

[cheekybuddha]

What am I missing?

Use the worksheet name as a qualifier, eg:

Sheet1.UpdateTestCaseFilenames

If your sub were in a standard module, then you could call it without qualification.

 

[CJ_London]

try changing it to a function?

 

[Edgar_]

I opened a workbook to test the behavior.

If the code is in a sheet, it won't run from the immediate window unless the sheet's Code Name is specified, like:

Sheet1.UpdateTestCaseFilenames

Just please do not confuse sheet name with sheet Code Name. They're not the same and I myself have made the mistake at some point. The Code Name is visible from the Project Explorer: it's the one that is NOT between parentheses.

You can also write the code in a module and it will be available for all sheets.