How to assign a value to a variable with a function that's from a COM Add-In?

[KS_user]

Hello everyone,

I have a COM Add-In on my Excel 2010 sheet. Does anyone have experience with such Add-Ins, and if so, have you found difficulty with using the Add-In in the same way that you would use other excel functions in VBA?

For example, I have this function "=EDS_Value" that comes from the Add-In. I used it in a macro as part of the following statement:

.Cells(i, 5).FormulaR1C1 = "=EDS_Value(MasterTagList!R" & f & "C4, RC[-1])"

and it works absolutely fine. However, when I try to assign a function output value to a variable, it flips out. The following statement:

PowerLoad = "=EDS_Value(MasterTagList!R" & f & "C5, R" & i & "C4)"

does not work. I've tried adding Application.WorksheetFunction.EDS_Value in front, but that doesn't work either. The Add-In itself has a button to make user-defined functions, so I used that to make the same function as "EDS_Value", except I called it "PointValue" (I confirmed that the PointValue and EDS_Value functions are identical by entering them into cells in a worksheet, and they generate the same value) Then, a module popped up in the VBA project window titled "EDSExcel_UserDefinedFunctions" with the following statement:

Public Function PointValue(point1, timestamp1)
PointValue = Application.COMAddIns("EDSExcel.AddinModule").Object.CallUserDefinedFunction(ThisWorkbook.Name, "PointValue", point1,timestamp1)
End Function

So I used tried to use that function syntax in a test module:

Sub Practice2()
Dim z As Double
z = Application.COMAddIns("EDSExcel.AddinModule").Object.CallUserDefinedFunction(ThisWorkbook.Name, "PointValue", Range("D9"), Range("D18"))
MsgBox z
End Sub

But 'z' is consistently zero, when the value should be around 8.9. Does anyone know why this might be? Why is it that I can use the Add-In function just fine when I enter it into a cell on the worksheet, or when I use it as part of a macro in FormulaR1C1 format, but not when I try and assign the function's output to a variable?

Last bit of information: When I started writing lines of code that use the Add-In function in ways that don't work (i.e. not R1C1 format), now, every time I save my workbook I get the message "This workbook contains features that may not be compatible with Excel Macro-Enabled workbook. Do you want to save the workbook in a new format?" I only think this might be important because the message never showed up before when I was using the Add-In functions in the R1C1 format.

Any thoughts would be appreciated, thank you!

 

[shg]

How about ...

PowerLoad = Evaluate("EDS_Value(MasterTagList!R" & f & "C5, R" & i & "C4)")

 

[KS_user]

Darn, it doesn't work. Thanks though! I actually didn't know about Evaluate before, it sounds like a powerful thing to have in your toolkit

 

[shg]

Oops. Convert the formula to A1-style references and try that.

 

[KS_user]

I keep getting a "Type Mismatch" error

 

[KS_user]

Thanks anyway though!

 

[shg]

What did you do to get it in A1 notation?

 

[KS_user]

Here are three tries:

Sub EvalPractice1()
PowerLoad = Evaluate("EDS_Value(D18, E18)")
MsgBox PowerLoad
End Sub

(1) This one generates a "Type Mismatch" Error. When I hit 'debug' it highlights the MsgBox Powerload line. Can't figure out why....

Sub EvalPractice2()
PowerLoad = Evaluate("EDS_Value(D18, E18)")
Range("F18").Value = PowerLoad
End Sub

(2) This one does insert a value into cell F18, but it's "#NULL"

Sub EvalPractice3()
Dim point1 As String
Dim timestamp As Date
Dim PowerLoad As Double

point1 = Cells(18, 4).Value
timestamp = Cells(18, 5).Value
PowerLoad = Evaluate("EDS_Value(point1, timestamp)")
MsgBox PowerLoad
End Sub

(3) This generates the error "Type Mismatch." When I hit 'debug' the 'PowerLoad = ...' line is highlighted.

 

[shg]

In the last one, the code attempts to pass the literal strings "point1" and "timestamp" to the function.

 

[KS_user]

Hmm...interesting. When I run the debugger with a breakpoint at the "PowerLoad = ..." line, and hover my mouse over the 'point 1 = ..." and "timestamp = ....", the correct values appear. But when I hover my mouse over the 'point 1' and 'timestamp' within the "PowerLoad = ..." expression, no values show up. Do you know why that would be?