Calling a SubProc with variable

[AUGuy]

I'm attempting to call a subprocedure from within a FOR statement. Which procedure is called depends on which iteration of the loop it's currently on. the chkPLA/PM/CAM are check boxes. If its checked, then it would call the sub procedure related to that category. The Call portion is what is currently broke. It is telling me i must list call a function or procedure when i run it. I'm sure this hs something to do with how i've declared the variable, but i'm not sure which type is correct in this situation.

The code i currently have is:

Private Sub Command11_Click()
Dim strChkID(2) As String
Dim strCallSubName(2) As String
 
strChkID(0) = Me.chkPLA
strChkID(1) = Me.chkPM
strChkID(2) = Me.chkCAM
strCallSubName(0) = "ExportPLA"
strCallSubName(1) = "ExportPM"
strCallSubName(2) = "ExportCAM"
 
For Counter = LBound(strChkID) To UBound(strChkID)
    If strChkID(Counter) = True Then
 [B][COLOR=red]      Call strCallSubName(Counter)[/COLOR][/B]
    End If
Next
End Sub

 

[DJkarl]

The Call function cannot be used in this way, look at the Eval function, that should do what you need.

 

[AUGuy]

Thanks, DJKarl. I changed the red line to:
Eval ("strCallSubName(Counter)()")
Yet, now i am getting an error box saying 'The Expression you entered has a function name that (the database) can't find.

I have the subprocedure defined as follows:

Sub ExportPLA()
(export code removed to conserve space)
MsgBox "ExportPLA Called"
End Sub

Any Thoughts?
Thanks Again,
Guy

 

[boblarson]

Thanks, DJKarl. I changed the red line to:
Eval ("strCallSubName(Counter)()")
Yet, now i am getting an error box saying 'The Expression you entered has a function name that (the database) can't find.

I have the subprocedure defined as follows:

Sub ExportPLA()
(export code removed to conserve space)
MsgBox "ExportPLA Called"
End Sub

Any Thoughts?
Thanks Again,
Guy

You have to leave the variables OUTSIDE of quotes:

Eval (strCallSubName(Counter))

 

[DJkarl]

Try removing the double quotes

so Eval ("strCallSubName(Counter)()")

would become
Eval (strCallSubName(Counter))

 

[boblarson]

Oh, and you would need to modify this slightly (I believe):

strCallSubName(0) = "ExportPLA"
strCallSubName(1) = "ExportPM"
strCallSubName(2) = "ExportCAM"

To this

strCallSubName(0) = "ExportPLA[B][COLOR=red]()[/COLOR][/B]"
strCallSubName(1) = "ExportPM[B][COLOR=red]()[/COLOR][/B]"
strCallSubName(2) = "ExportCAM[B][COLOR=red]()[/COLOR][/B]"

 

[AUGuy]

Oh, because as i had it it was actually reading the line as: Eval (""ExportPLA"()")?

 

[AUGuy]

I made the change to the Eval code as you both suggest, as well as tried it with and without Bob's suggested change to the variables, and I'm still getting the same error at the same point.

The code line now reads:

[COLOR=blue][B]       Eval (strCallSubName(Counter))
[/B][/COLOR]

It seems like it is something small i'm overlooking, i just can't seem to find it.

 

[vbaInet]

Follow what Bob pointed out in his last post. You should normally try all suggestions before posting back.

 

[AUGuy]

Follow what Bob pointed out in his last post. You should normally try all suggestions before posting back.

You'll notice I did mention in my last post that I had tried both with and without Bob's suggestions as to the naming of the variables [to include the()]. I'm assuming that's the post you're referring to unless there's an additional one he made that I'm not seeing for some reason.

 

[boblarson]

You'll notice I did mention in my last post that I had tried both with and without Bob's suggestions as to the naming of the variables [to include the()]. I'm assuming that's the post you're referring to unless there's an additional one he made that I'm not seeing for some reason.

I already tested my suggestion:

strCallSubName(0) = "ExportPLA()"
strCallSubName(1) = "ExportPM()"
strCallSubName(2) = "ExportCAM()"

And calling it

Eval(strCallSubName(Counter))

worked fine for me in my test database

 

[AUGuy]

Here's the code in its entirety as it appears in the VBA window to perhaps identify the problem (the line in which the error appears is highlighted red). Thanks again for looking at this, Guy

Option Compare Database
 
Sub ExportPLA()
MsgBox "ExportPLA Called" 'Temporary
End Sub
 
Sub Command11_Click()
Dim strChkID(2) As String
Dim strCallSubName(2) As String
 
strChkID(0) = Me.chkPLA
strChkID(1) = Me.chkPM
strChkID(2) = Me.chkCAM
strCallSubName(0) = "ExportPLA()"
strCallSubName(1) = "ExportPM()"
strCallSubName(2) = "ExportCAM()"
 
For Counter = LBound(strChkID) To UBound(strChkID)
If strChkID(Counter) = True Then
[COLOR=red]Eval (strCallSubName(Counter))[/COLOR]
End If
Next
End Sub

 

[vbaInet]

Ah, yes you did. Apologies! The eyes get blurry when you're sat at the table having a nice steak

Keeping the variables as they are, use this instead:

Eval (strCallSubName(Counter) & "()")

Then you must change the Sub to Function. That is, for example, change this:

Sub ExportPLA()

to this:

Function ExportPLA()

 

[boblarson]

Keeping the variables as they are, use this instead:

Eval (strCallSubName(Counter) & "()")

I would include it in the variables myself but that's me.

Then you must change the Sub to Function. That is, for example, change this:

Sub ExportPLA()

to this:

Function ExportPLA()

That is the difference. Good Call - I had used FUNCTION instead of SUB (I tend to use functions always over subs because there is just so many times where functions can be seen where subs can't and there is no good reason to not use function over a sub - it doesn't matter if a function doesn't return a value.

 

[vbaInet]

Either way, but I suppose putting it in the variables helps for readability, indicating that those are function names.

I use a mix of both. Not had problems with either of them. There's still this debate about whether a function runs faster than a sub because of one returning a value and the other not returning anything. In some enviroments there is a perormance advantage but in VBA I don't think it matters. They can continue arguing

 

[smig]

I just wanted to ask a very similar question, so I'll aks it here.
I have a set of functions/subroutines that their names are stored in a table.
I'm trying to use a listbox to select the function/sub to run.

I use his code, with no lack

Dim ExportData As String
 
ExportData = Me.DataToExport.Column(1)

Call Eval(ExportData)

I tried both with the Call function and without
no need to pass any parameters to the function/sub

the data in table looks like, just as suggested:
ExportHeshbonyot_Kabalot()
ExportPCN847()


Thanks,
Tal

 

[AUGuy]

Ok guys, still having an issue here. I made the changes that you suggested of changing the SUB to FUNCTION. This gave a new error of 'Invalid use of Null'. Which I figured was caused by no default value given to the checkboxes. So I changed the default value to FALSE of the checkboxes within the form properties. I still get 'The expression you entered has a function name that (database) can't find.'
Sorry this is such a handle, Attaching the entire code for further debugging:

Option Compare Database
 
Function ExportPLA()
    MsgBox "ExportPLA Called" 'Temporary
End Function
 
Function ExportPM()
   'Placeholder
End Function
 
Function ExportCAM()
   'Placeholder
End Function
 
Private Sub Command11_Click()
Dim strChkID(2) As String
Dim strCallSubName(2) As String
 
strChkID(0) = Me.chkPLA
strChkID(1) = Me.chkPM
strChkID(2) = Me.chkCAM
strCallSubName(0) = "ExportPLA()"
strCallSubName(1) = "ExportPM()"
strCallSubName(2) = "ExportCAM()"
 
For Counter = LBound(strChkID) To UBound(strChkID)
    If strChkID(Counter) = True Then
       Eval (strCallSubName(Counter))
    End If
Next
End Sub

 

[boblarson]

These functions:

Function ExportPLA()
    MsgBox "ExportPLA Called" 'Temporary
End Function
 
Function ExportPM()
   'Placeholder
End Function
 
Function ExportCAM()
   'Placeholder
End Function

should be in a STANDARD MODULE not a form module.

 

[AUGuy]

These functions:
should be in a STANDARD MODULE not a form module.

Ugggghhhhhh! Thanks, Bob. Ashamed I didn't catch that...

Guy

 

[smig]

cahnged Sub to Function and all is OK now
both with Call and without