VBA: Call Subrountine From A Dynamic Module Reference

Question

If you check out my history you will see that I have been calling Worksheets references through method calls. A working example of one such method:

'In module GlobalRefs

Function W_ExampleWS() As Worksheet
    Set W_ExampleWS = ActiveWorkbook.Worksheets("ExampleWS")
End Function

So when I do normal worksheet related calls, they work:

...
GlobalRefs.W_ExamplesWS.Range("A1").Value = 42 'works
...

However, the worksheet ExampleWS has a subroutine called "DesignGraph". I want to be able to call that subroutine through a method call like so.

'In module AnotherRandomModule

...
GlobalRefs.W_ExampleWS.DesignGraph arg1 arg2
...

I keep receiving errors like the method is undefined even though the VBA Editor's intellisense acknowledges the the existence of the subroutine as it corrects the capitalization of DesignGraph if spelt mis-cased. I am giving it the correct arguments.

Why is this happening?

@JosieP has the answer (and a suggestion). A different approach might be to "wrap" the worksheet object in a custom class which exposes DesignGraph (and any other routines) as a method. This is a good approach if you want to model a specific worksheet "type", of which there could be many instances: it also avoids duplicating code in each instance of that type. — Tim Williams
– Tim Williams, Commented Jun 3, 2013 at 22:18

JosieP · Accepted Answer · 2013-06-03 21:04:03Z

1

the Worksheet class does not have a DesignGraph method - only your specific sheet does. Assuming DesignGraph is a public sub, if you change the function to return an Object instead you should not have an issue.

answered Jun 3, 2013 at 21:04

JosieP

3,4101 gold badge15 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1275094 Over a year ago

I decided to go a different direction but your tid bit of knowledge helped me understand the issue I was facing.

Collectives™ on Stack Overflow

VBA: Call Subrountine From A Dynamic Module Reference

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related