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