Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: "Volatile" use of concatenated worksheetfunction expression
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 ! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: "Volatile" use of concatenated worksheetfunction expression
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 "?B?RmFjdGl2YXRvcg==?=" wrote: 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 ! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: "Volatile" use of concatenated worksheetfunction expression
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 "keepITcool" wrote in message ... 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 "?B?RmFjdGl2YXRvcg==?=" wrote: 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 ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
whyis "paste spl" copying concatenated cell changing lastdigit2 0 | Excel Worksheet Functions | |||
CELL("filename") acting Volatile? (in assoc. with PULL) | Excel Worksheet Functions | |||
How can I get "=TODAY()" results to be non-volatile? | Excel Worksheet Functions | |||
=CELL("filename") VOLATILE? | Excel Worksheet Functions | |||
Excel expression "sumproduct" in Access | Excel Programming |