Using a declared function in a module in a cell

[mtheriault2000]

Hello again

Is it possible to use a function that as been declared in a module directly in a cell

Declare Function GV_GetNamedString Lib "C:\Program Files (x86)\TS Support\MultiCharts\GlobalVariable.dll" _
                                    (ByVal strElementLoc As String, ByVal strOther As String) As String

When I'm editing a formula in a cell, I see the function as available, but it give me an "#Value" error

Formula for cell ("D18") =GV_GetNamedString("DataString"; "Error")

In VBA, the formula work and the appropriate value is captured in a variable

Martin

 

[RoryA]

I suspect you might need an intermediate VBA function that simply calls the declared function.

 

[mtheriault2000]

I suspect you might need an intermediate VBA function that simply calls the declared function.

Hummm you lost me

The goal is to have a cell that will display the value of a global variable. This variable to be updated from an external application. Don't know it is is possible yet

In my VBA, i changed my declaration to be a Public function. Now, I don't get an error, but the text of my formula is displayed as the value of my cell

Martin

 

[RoryA]

Is the cell formatted as text? If so, format as General and re-enter the formula.

 

[mtheriault2000]

Re: Using a declared function in a module in a cell Resolve

As suggested by Rorya

I succeeded to create an intermediary function

Public Declare Function GV_SetNamedString Lib "C:\Program Files (x86)\TS Support\MultiCharts\GlobalVariable.dll" _
                                    (ByVal strElementLoc As String, ByVal intSetValue As String) As Long
                                    
Public Declare Function GV_GetNamedString Lib "C:\Program Files (x86)\TS Support\MultiCharts\GlobalVariable.dll" _
                                    (ByVal strElementLoc As String, ByVal strOther As String) As String

' Here is the intermediate function

Public Function Call_GetNamedString() As String
   Dim String_i As String
   String_i = GV_GetNamedString("DataString", "error ")
   Call_GetNamedString = String_i
End Function

Now Cell ("D17") formula = Call_GetNamedString()
and the value is the appropriate one

Martin

P.S. RoRya, I ought you another beer "Mary"

 

[mtheriault2000]

But, How do i force the update of the cell?

Value is updated only if i select the cell then press enter. At that time the function formula is called and the cell value is updated.

Is it possible to have it done automatically?

I will post a specific question for that

Martin

 

[RoryA]

Add:

application.volatile true

to your function and it will update whenever your workbook calculates.

 

[mtheriault2000]

Thanks for the suggestion, but not working yet. I did add your line

Public Function Call_GetNamedString() As String
   Dim String_i As String
   Application.Volatile True
   String_i = GV_GetNamedString("DataString", "error ")
   Call_GetNamedString = String_i
End Function

The cell is updated only when i select it the press enter. Even the "calculate" button on the toolbar does not do it

Martin

 

[mtheriault2000]

Now, the cell value is updated when the page is recalculated.

Is it possible to have done automatically? My calculate option is set to that.

Martin

 

[RoryA]

No because there's nothing to trigger it. You'd have to run a timer to update it periodically.