Excel: How to call functions in Sheet modules from VbScript

Question

Using VBScript, I'm trying to run a subroutine that resides in one of the code modules. Now this particular sub calls several other subs/functions, including few that reside on the sheet modules. And on calling these subs residing in Sheet Modules, I get can error:

"Sub or Function undefined!"

How can I get around this? There are a lot of such functions and I don't want to move all of them from Sheet modules to code modules. Is there a better work around for this?

VBScript that calls a sub (residing in Code modules) that does not call any code on Sheet modules is working fine, so I'm sure it is the above bit that is causing problem.

Answer 1

If you are calling Sub Routines or functions from different modules then you need to make sure of two things

1. The Sub / Function should not be declared Private
2. You call the Sub / Function by prefixing the module name from which the code lives in

So if you have a Sub called TestSub and it lives in Sheet1's code module, then you should call it like Sheet1.TestSub. Calling it by using just TestSub will result in the error Sub or Function not defined.

As mentioned in the comments, the name of the sheet isn't always the same as the display name. You can find the name of the sheet from the object explorer in the VBE

In this example, the real sheet name is on the left, whereas the display name is on the right. You should refer to the sheet using the name on the left.