Hi Everyone,

This problem has been bugging me for the past couple of days. I have
looked for a solution on the web for hours, but wasn't able to get a
satisfactory answer.

Basically, I have a series of modules that have the same structure,
and I'd like to be able to switch from one to another by changing a
cell of my spreasheet. Ideally, my program would look like this :

Public Function MyMainFunction()

Dim moduleName As String

moduleName = Range("A1").Value ' - I select which module to use
from cell A1

result = moduleName.Function1() ' - This doesn't work, since
moduleName is a String

End Function

Unfortunately, it looks like there is no way in VBA to execute a line
such as:

result = Eval( moduleName & ".Function1()")

Or something similar.

Any thought on how I could do this ?

Thanks a lot for your help !!

Jerome

Sounds like you should be looking at class modules. You might have multiple
instances of a given class, each with different properties; or multiple
class modules doing different things with similarly named functions and
methods. Why not give an overview of what you are trying to accomplish.

Regards,
Peter T

"gromeg" wrote in message
...
Hi Everyone,

This problem has been bugging me for the past couple of days. I have
looked for a solution on the web for hours, but wasn't able to get a
satisfactory answer.

Basically, I have a series of modules that have the same structure,
and I'd like to be able to switch from one to another by changing a
cell of my spreasheet. Ideally, my program would look like this :

Public Function MyMainFunction()

Dim moduleName As String

moduleName = Range("A1").Value ' - I select which module to use
from cell A1

result = moduleName.Function1() ' - This doesn't work, since
moduleName is a String

End Function

Unfortunately, it looks like there is no way in VBA to execute a line
such as:

result = Eval( moduleName & ".Function1()")

Or something similar.

Any thought on how I could do this ?

Thanks a lot for your help !!

Jerome

Jerome,

You could be explicit about it:

Public Function MyMainFunction(moduleName As String)
If moduleName = "Module1" Then MyMainFunction = Module1.Function1()
If moduleName = "Module2" Then MyMainFunction = Module2.Function1()
End Function

And use it like:

=MyMainFunction("Module1")

or

=MyMainFunction(A1)


HTH,
Bernie
MS Excel MVP


"gromeg" wrote in message
...
Hi Everyone,

This problem has been bugging me for the past couple of days. I have
looked for a solution on the web for hours, but wasn't able to get a
satisfactory answer.

Basically, I have a series of modules that have the same structure,
and I'd like to be able to switch from one to another by changing a
cell of my spreasheet. Ideally, my program would look like this :

Public Function MyMainFunction()

Dim moduleName As String

moduleName = Range("A1").Value ' - I select which module to use
from cell A1

result = moduleName.Function1() ' - This doesn't work, since
moduleName is a String

End Function

Unfortunately, it looks like there is no way in VBA to execute a line
such as:

result = Eval( moduleName & ".Function1()")

Or something similar.

Any thought on how I could do this ?

Thanks a lot for your help !!

Jerome

You can do what you want by passing a parameter to you UDF.

result = MyFunction(A1:A20, "Add")


Function MyFunction(Target as Range, Operation as String)

Select Case Operation
Case "Add"
'add code here
Case "Subtract"
'add code here

Case "Multiply"
'add code here

Case "Divide"
'add code here

End Select

End function

"Peter T" wrote:

Sounds like you should be looking at class modules. You might have multiple
instances of a given class, each with different properties; or multiple
class modules doing different things with similarly named functions and
methods. Why not give an overview of what you are trying to accomplish.

Regards,
Peter T

"gromeg" wrote in message
...
Hi Everyone,

This problem has been bugging me for the past couple of days. I have
looked for a solution on the web for hours, but wasn't able to get a
satisfactory answer.

Basically, I have a series of modules that have the same structure,
and I'd like to be able to switch from one to another by changing a
cell of my spreasheet. Ideally, my program would look like this :

Public Function MyMainFunction()

Dim moduleName As String

moduleName = Range("A1").Value ' - I select which module to use
from cell A1

result = moduleName.Function1() ' - This doesn't work, since
moduleName is a String

End Function

Unfortunately, it looks like there is no way in VBA to execute a line
such as:

result = Eval( moduleName & ".Function1()")

Or something similar.

Any thought on how I could do this ?

Thanks a lot for your help !!

Jerome

Create a new module (module1) and insert the following:-

Public Function function1() As String
function1 = "You Chose Module1"
End Function

Now create another new module (module2) and insert the following:-

Public Function function1() As String
function1 = "You Chose Module2"
End Function

Finally create a third module and insert your master function code, eg:-

Public Function MyFunction() As String
Select Case Range("A1")
Case "Module1"
MyFunction = Module1.function1
Case "Module2"
MyFunction = Module2.function1
End Select
End Function

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"gromeg" wrote:

Hi Everyone,

This problem has been bugging me for the past couple of days. I have
looked for a solution on the web for hours, but wasn't able to get a
satisfactory answer.

Basically, I have a series of modules that have the same structure,
and I'd like to be able to switch from one to another by changing a
cell of my spreasheet. Ideally, my program would look like this :

Public Function MyMainFunction()

Dim moduleName As String

moduleName = Range("A1").Value ' - I select which module to use
from cell A1

result = moduleName.Function1() ' - This doesn't work, since
moduleName is a String

End Function

Unfortunately, it looks like there is no way in VBA to execute a line
such as:

result = Eval( moduleName & ".Function1()")

Or something similar.

Any thought on how I could do this ?

Thanks a lot for your help !!

Jerome

Hi guys,

Thanks a lot for your answers.

To give you a bit more color, I'm working on a project with multiple
users. My program is forecasting the sales in my shop. What I'm trying
to accomplish is to have one piece of code (module "MainModule") that
is shared among users and never modified, and another part (module
"Shoes", module "Jackets", module "Ties" etc) that can be modified
independently.
The modules "Shoes", "Jackets", "Ties" etc have a similar structure,
(ie, same functions name), but the code of these functions is
different for each.
Ideally, I'm trying to obtain a spreadsheet where I could select which
module to use by simply inputing in cell A1 the module name. (ie
"Shoes", "Jackets" or "Ties"). And even later on add new modules, like
"Shirts", etc.
The module "MainModule" would call a function "getForecast" contained
in the right module (the right module being the one selected in cell
A1), and run a few operations to make the results look pretty on the
page.

I hope it's more clear.

- Peter, the problem is that except for the name, the functions in
each module have nothing in common. So I would need to create a class
per object (ie a class "Shoes", a class "Ties", etc). Which would
eventually not solve my pb - right ?

- Bernie, yes, that would work, but it means that I would need to
modify the code in "MainModule" each time I create a new module, which
I'm trying to avoid. I guess I can create an intermediate module that
could be modified that would only contain that parsing function.

-Joel, I don't know the name of the module yet. So I can't really
separate by case.

I guess so far the best option I have is Bernie's :To create a special
module that is called by "MainModule", that needs to be modified each
time a new module is added, and that would select the right module to
use according to the input of A1.
Not ideal, but workable.
Ideally, I would not need to modify anything in the code when a new
module is added.
Any remaining thought welcome.

And thanks again for the help

Jerome

And to add one more thing, the code is going to be quite ugly, since I
have multiple functions for each module. It will look like:

Public Function MyMainFunction(moduleName As String)
If moduleName = "Module1" Then
FunctionA = Module1.FunctionA()
FunctionB = Module1.FunctionB()
FunctionC = Module1.FunctionC()
FunctionD = Module1.FunctionD()
FunctionE = Module1.FunctionE()
If moduleName = "Module2" Then
FunctionA = Module2.FunctionA()
FunctionB = Module2.FunctionB()
FunctionC = Module2.FunctionC()
FunctionD = Module2.FunctionD()
FunctionE = Module2.FunctionE()

etc...

End Function

And each time I have to had a new module, I would need to copy paste
these 5 lines with the corresponding module name. Ugly. I wish there
was a way to call Eval(moduleName & . "FunctionA()" )

Thanks anyway

Ugly is correct, and hard to maintain. You could (and should) write code that is 'reusable' - what
is the difference between Module1.FunctionA and Module2.FunctionA?

Since you can pass parameters to the function, you should be able to make the functions
universal..... post your code, and someone will help you.

HTH,
Bernie
MS Excel MVP


"gromeg" wrote in message
...
And to add one more thing, the code is going to be quite ugly, since I
have multiple functions for each module. It will look like:

Public Function MyMainFunction(moduleName As String)
If moduleName = "Module1" Then
FunctionA = Module1.FunctionA()
FunctionB = Module1.FunctionB()
FunctionC = Module1.FunctionC()
FunctionD = Module1.FunctionD()
FunctionE = Module1.FunctionE()
If moduleName = "Module2" Then
FunctionA = Module2.FunctionA()
FunctionB = Module2.FunctionB()
FunctionC = Module2.FunctionC()
FunctionD = Module2.FunctionD()
FunctionE = Module2.FunctionE()

etc...

End Function

And each time I have to had a new module, I would need to copy paste
these 5 lines with the corresponding module name. Ugly. I wish there
was a way to call Eval(moduleName & . "FunctionA()" )

Thanks anyway

This may be one way:

' Located in Module1
Function Function1() As String
Function1 = "Test result from Module1"
End Function

' Located in Module2
Function Function1() As String
Function1 = "Test result from Module2"
End Function

Sub Tester()
Dim result As String
Range("A1").Value = "Module1"
result = Application.Run(Range("A1").Value & ".Function1")
MsgBox result
End Sub


--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions

"gromeg" wrote in message
...
Hi Everyone,

This problem has been bugging me for the past couple of days. I have
looked for a solution on the web for hours, but wasn't able to get a
satisfactory answer.

Basically, I have a series of modules that have the same structure,
and I'd like to be able to switch from one to another by changing a
cell of my spreasheet. Ideally, my program would look like this :

Public Function MyMainFunction()

Dim moduleName As String

moduleName = Range("A1").Value ' - I select which module to use
from cell A1

result = moduleName.Function1() ' - This doesn't work, since
moduleName is a String

End Function

Unfortunately, it looks like there is no way in VBA to execute a line
such as:

result = Eval( moduleName & ".Function1()")

Or something similar.

Any thought on how I could do this ?

Thanks a lot for your help !!

Jerome

Thanks a lot, Tim, Application.Run works fine !