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

[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.

 

[mdlueck]

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.

That was the needed detail.... I had to hard code define which Sheet the VBA code is attached to that I was trying to invoke. Maddening few hours.

Yes, I am trying to be fast with this task.... just put up with code built into the sheet, not as a separate Class or Module. In Access, I do not have to code VBA with the Form name in the call context. Maddening.

I like VBA under Access best. The other apps, the VBA feel is just not the same.

Thanks very much!

 

[The_Doc_Man]

I like VBA under Access best. The other apps, the VBA feel is just not the same.

And yet it is the same... because for all of MS Office, there is only one VBA library.

The difference may be that internally, Access is closer to being an object-oriented program and so VBA takes better advantage. But technically, ALL of the Office members have an object-oriented framework (called the Component Object Model).

This fact often gets obscured because Excel starts life as a flat-file structure where every cell is initially independent and it is hard to tie a particular function or sub to anything specific. It is no better with Word because the main body of the document is a linear stream of characters. For PowerPoint it is a linear stream of frames. And let's not talk about "old" Outlook. (But then, let's not talk about "new" Outlook either...)

 

[cheekybuddha]

In Access, I do not have to code VBA with the Form name in the call context. Maddening.

Are you sure?

If you create a public sub or function in a form's module you will need to use the form reference or class name to qualify if you want to call that sub/function from the Immediate Window (or from outside the form's module)