Using With and Module to access variables, macros, functions in another module

[JimminyJim]

As per the heading: I'm trying to access variables, macros, functions in another module

I know that I can use (if calling from Module 1):

Module2.variable
Module2.sub
Module2.function

(assuming declared as public)

But how can i use 'With' statement to access multiple?

I've tried:

Dim myMod As VBIDE.VBComponent
Set myMod = ThisWorkbook.VBProject.VBComponents("Module2")

With myMod
   .variable = 15
   .sub
   .function
End With

I get "Object doesn't support this property or method" on each line item after 'With myMod'. Yes I have the extensibility library loaded.

This example might seem trivial, but in my actual project I wish to access many different variables and functions in many different modules to do bulk data retrieval and writing.

Is there something I can use to identify a module that can be used in a WITH statement?

 

[JimminyJim]

I should add that the .variable, .sub and .variable lines are not the names I use; they are just used here for illustration

 

[StephenCrump]

Welcome to the Forum!

But sorry, I don't understand your question.

Module 1 can directly call any function or Sub in Module 2, unless you have declared those Functions and Subs as Private.

And if SomeVariable is Public in Module 2, you can use that variable in Module 1.

 

[JimminyJim]

Welcome to the Forum!

But sorry, I don't understand your question.

Module 1 can directly call any function or Sub in Module 2, unless you have declared those Functions and Subs as Private.

And if SomeVariable is Public in Module 2, you can use that variable in Module 1.

Thanks for the welcome Stephen!

Specifically I'm wanting to use a variable to hold a reference to the module I want to call so I can then use it in a 'With' statement.

I know that I can directly call access, but I'm looking to use 'With'

 

[StephenCrump]

Can you provide a more concrete example?

Given some Sub, Function or variable (or a combination thereof?) what do you envisage doing inside a With statement?

 

[JimminyJim]

Can you provide a more concrete example?

Given some Sub, Function or variable (or a combination thereof?) what do you envisage doing inside a With statement?

It's more or less in the original post. I want to use a variable to hold the module, so I can assign it dynamically. Then use that variable in a With statement to do mass variable/sub/function access.

The module to access can change depending on user input

 

[JimminyJim]

This is issue is still not fixed and I have not found a solution that works. If anyone finds this in future and knows the answer - please do let me know!

 

[RoryA]

You cannot do that. Why do you need to specify the module at all?

 

[JimminyJim]

Hi RoryA

1)
I wish to use the construct like I would any 'With' statement: to reduce the need to hardcode the value.

i.e. instead of:

Module1.myFunc1
Module1.myFunc2
Module1.myFunc3
Module1.myFunc4
...
...
etc

Simplify to:

Dim MyModule As Object: Set MyModule = ....

With MyModule

.myFunc1
.myFunc2
.myFunc3
.myFunc4
...
...
etc

There is an additional, more complex reason but if the above is not possible as you say, then it's a deadend.

 

[RoryA]

It's not possible with a normal module, and you didn't actually answer my question. Why do you need to use the module name? Typically, just:

myFunc1
myFunc2
myFunc3
myFunc4

works just as well, unless you are duplicating routine names.