VBA: "Volatile" use of concatenated worksheetfunction expression

[Guest]

Help urgently needed for this:

Sub x()
Dim a As String
Dim b As Variant
ReDim b(1 To 2, 1 To 1)
b(1, 1) = 1
b(2, 1) = 2
a = "Sum"

'This Works fine: "3"
MsgBox WorksheetFunction.Sum(b)

'This does not work: "WorksheetFunction.Sum(b)"
'I need to "force" an evaluation of the expression "WorksheetFunction.Sum(b)"
MsgBox "WorksheetFunction." & a & "(b)"
End Sub

Thanks a lot in advance !

 

[keepITcool]

CallByName can only be used with vba6 => NOT in xl97

Sub x()
Dim a As String
Dim b As Variant
ReDim b(1 To 2, 1 To 1)
b(1, 1) = 1
b(2, 1) = 2
a = "SUM"

MsgBox CallByName(Application, a, VbGet, b)

End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Charles Williams]

You can use Evaluate:
Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If not IsEmpty(theInput) then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(Cstr(theInput))
Else
vEval = Application.Evaluate(cstr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

regards

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com