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]

Because it's part of a literal string. VBA doesn't see them as variables.

 

[KS_user]

So, I know the answer now in case you're curious. I talked to the people who built my plug-in, and they pointed out something that should've been obvious. One of the arguments of the EDS_Value function, "point 1", is pulled from an external data source. It shows a #NULL value until I hit the 'recalculate' button that is built into the plug-in. This recalculate button cannot be automated from VBA, unfortunately. So this means that I can still enter the EDS_Value formula into a cell, which will generate #NULL, but that becomes a numerical value when I hit recalculate. Any formulas dependent on that cell register as #VALUE until I hit recalculate, and then they also become numerical values.

However, the downside of this is that I cannot store an EDS_Value as part of a variable, because the value is #NULL until I hit recalculate. And because I can't automate recalculate through VBA, Excel rejects my attempt to store the value in a variable. Oh well.

Thanks anyway for your advice! Especially about the 'Evaluate' function

 

[KS_user]

Test