A tricky one: anything like an array of functions?

[Charles]

Hello

I am trying to do something specific but not sure of the right syntax
to use in VBA. Basically I have the option to do several kind of
calculations, on some given variables like

Option 1: do the sum
Option 2: take the max
Option 3: do the product

Now in my code I have a parameter that will say whether I should do
the calculation of the option 1, 2 or 3.

The easy way to formulate it is simply by doing a select/case, or by
running a loop going through every option and comparing if this is the
option I want to use.

Now if the number of different options is significant, and this is
looped many times, it can be quite time consuming and unnecessary.
What I would like to do is having an integer "N" that would say which
calculation to do. N would be worth 1, 2 or 3 and I would go directly
to the right option, but I am stuck there. How would you select a
specific calculation based on N?

Ideally I would like to have a sort of array of functions
"TheArrayOfSubs" (for instance subs) such that I can do

call TheArrayOfSubs(2)

and it would directly run the sub corresponding to option 2.

Or something like that.

Would any one have an idea of how to code that?

thanks
Charles

 

[Don Guillett]

Maybe??

Sub selectsubs()
n = InputBox("Enter number to run")
Select Case n
Case 1
Call suba
Call subb
Call subc
Case 2
Call subb
Case 3
Call subd
Case Else
End Select
End Sub

Sub suba()
MsgBox "hi"
End Sub

 

[Tom Ogilvy]

Public Function ArrayofSubs( choice as Long)
select Case choice
Case 1
mymacro1
Case 2
myMacro2
case 3
myMacro3
Case Else
msgbox "Invalid"
end Select
End function

 

[Ron Rosenfeld]

Hello

I am trying to do something specific but not sure of the right syntax
to use in VBA. Basically I have the option to do several kind of
calculations, on some given variables like

Option 1: do the sum
Option 2: take the max
Option 3: do the product

Now in my code I have a parameter that will say whether I should do
the calculation of the option 1, 2 or 3.

The easy way to formulate it is simply by doing a select/case, or by
running a loop going through every option and comparing if this is the
option I want to use.

Now if the number of different options is significant, and this is
looped many times, it can be quite time consuming and unnecessary.
What I would like to do is having an integer "N" that would say which
calculation to do. N would be worth 1, 2 or 3 and I would go directly
to the right option, but I am stuck there. How would you select a
specific calculation based on N?

Ideally I would like to have a sort of array of functions
"TheArrayOfSubs" (for instance subs) such that I can do

call TheArrayOfSubs(2)

and it would directly run the sub corresponding to option 2.

Or something like that.

Would any one have an idea of how to code that?

thanks
Charles

Since you are only returning a value, why not use a function instead of a sub?

Then you could do something like (not debugged)

Function Foo (args, N as long)
select case N
Case = 1
Foo = sum(args)
Case = 2
Foo = Max(args)
Case = 3
Foo = Product(args)
Case Else
Foo = "Error Message"
end select
end function


--ron

 

[Charles]

All these things would do the trick, but I thought select/case would
do a "compare". Let's say I have 100 different functions, then VBA
would have to compare N to the first case, then the second case, then
the third case (select/case does a compare, isn't it?)

My point is that since I know exactly that I want to do the Nth
function, is there a way I can go directly to this one?

It's a bit like two different ways to get a value:

First way, to do a select case

select case N
case=1
value=44
case=2
value=55
case=3
value=66
end select

whereas is you define MyArray as
MyArray(1)=44
MyArray(2)=55
MyArray(3)=66

then if you want the 3rd value, you just need to do MyArray(3) and VBA
will go directly to the right location in the memory. You do need VBA
to compare N to 1, then go to the next case, compare N to 2, then go
the next case, and compare N to 3 and then only process value=66

Not sure if I am clear enough

 

[Tom Ogilvy]

That is true. However you said you wanted to call a function base the
argument.

If you have

v = Array("mysum", "mymax", "mycount")

you can do

application.Run v(n-1), arg

but that is much slower than using select case where using the Run command
is not involved. (using RUN is the slowdown. Using the array part is
probably faster than Select case, but I think the difference would be
trivial. Using run would not be as trivial).

 

[Charles]

I think that's what I want to do. But if it is slower, I guess I will
have to use the select/case.

I do not have a preference on whether the calculations should be
contained in a sub, a function or a procedure of a class. But I have
(had) no idea of how to create an "index of sub/functions/procedures"
and to pick directly the one I need.

Thanks for your help!
Charles

 

[Robert Bruce]

I think that's what I want to do. But if it is slower, I guess I will
have to use the select/case.

I do not have a preference on whether the calculations should be
contained in a sub, a function or a procedure of a class. But I have
(had) no idea of how to create an "index of sub/functions/procedures"
and to pick directly the one I need.

Thanks for your help!
Charles

OK. Maybe this is overkill, but here we go:

Create a class called iFunction. Add this code...

Option Explicit

Public Function Exec(Arg As Variant) As Variant
'
End Function

Now add some classes to contain your functions. I've called them Function1
and Function2 for simplicity. You might want to give them more descriptive
names. In Function1 add the following code...

Option Explicit

Implements iFunction

Private Function iFunction_Exec(Arg As Variant) As Variant
iFunction_Exec = "Returned From Function1"
End Function

....and in Function2 add the following....

Option Explicit

Implements iFunction

Private Function iFunction_Exec(Arg As Variant) As Variant
iFunction_Exec = "Returned From Function2"
End Function

.... Note that your actual business code will replace the line iFunction_Exec
= "Returned From FunctionX" in each class.

Now add another class to tie it all together. Call it Functions. This will
be a collection of classes that implement iFunction. Add the following
code...

Option Explicit

' The private collection used to hold the real data
Private m_clnFunctions As Collection

Private Sub Class_Initialize()
Dim objFunction As iFunction
' explicit assignment is slightly faster than auto-instancing
Set m_clnFunctions = New Collection
Set objFunction = New Function1
Add objFunction, "Function1"
Set objFunction = New Function2
Add objFunction, "Function2"
End Sub

' Add a new iFunction item to the collection
Private Sub Add(newItem As iFunction, Optional Key As Variant)
' add to the private collection
m_clnFunctions.Add newItem, Key
End Sub

' Return a iFunction item from the collection
Public Function Item(index As Variant) As iFunction
Set Item = m_clnFunctions.Item(index)
End Function

' Return the number of items the collection
Public Function Count() As Long
Count = m_clnFunctions.Count
End Function

....Note that you will need to update the class_initialise event to load all
of your separate Function class objects - in my case I've only got 2.

To demonstrate how this works, add a regular module, add this code...

Option Explicit

Sub Test()
Dim objColl As New Functions
MsgBox objColl.Item(1).Exec(Nothing)
MsgBox objColl.Item(2).Exec(Nothing)
End Sub

.... and run it. I've sent Nothing as the param in both cases, you'll add
your own value, of course.

I've no idea what the overhead of doing this the 'object' way is, so you may
need to benchmark against a Select Case alternative.

HTH

Rob

 

[Charles]

Hum! Very smart. I'll try that. My short experience with objects let
me think they usually are relatively slower than working directly in a
sub but not having to go through 25 compares at each run might make it
worth

Thanks!
Charles