Function does not return value unless in debugging mode

[JTBorton]

Excel 2007. I have a function that returns a value from a process control server. The function runs perfectly as long as I am stepping into it. But if I pull out the stops and try to call it straight from the spreadsheet then it only returns a value of 0. I know it is connecting to the server because I have coded it to say so if it doesn't. If I step through it to debug it, the function magically starts working normally again and returns values to the spreadhseet. I know that all of the parameters being passed are correct because it works perfectly when I step into it. Any ideas what may cause this?

Here is the code. The objects used here are OPC class modules.

Public Function CurrentValue(strTagName As String, strParameter As String)
    Dim grpsDAGroupColl             As OPCGroups
    Dim grpDAGroup                  As OPCGroup
    Dim itmsDAItemColl              As OPCItems
    Dim itmDAItem                   As OPCItem
    Dim strGroupName                As String
    Dim lngClientHandle             As Long
    Dim strItemID                   As String
    
    Call ConnectLamarDA
    If IsConnected(svrDALamar) Then
        Set grpsDAGroupColl = svrDALamar.OPCGroups
        grpsDAGroupColl.DefaultGroupIsActive = True
        strGroupName = "NewGroup"
        Set grpDAGroup = grpsDAGroupColl.Add(strGroupName)
        Set itmsDAItemColl = grpDAGroup.OPCItems
    
        strItemID = Trim(strTagName) & "." & Trim(strParameter)
        itmsDAItemColl.AddItem strItemID, lngClientHandle
        Set itmDAItem = itmsDAItemColl(1)
        grpDAGroup.IsSubscribed = True
        itmDAItem.IsActive = True
    
        CurrentValue = Round(itmDAItem.Value, 2)
    Else
        CurrentValue = "Unable to Connect"
    End If
    
Proc_Exit:
    Set itmDAItem = Nothing
    Set itmsDAItemColl = Nothing
    Set grpDAGroup = Nothing
    Set grpsDAGroupColl = Nothing
    Call DisconnectLamarDA

End Function

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[PHV]

Seems like a timing issue, so I'd try to call DoEvents after each Set

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JTBorton]

PHV,

Thanks for the suggestion. I tried placing DoEvents in several locations but the problem still exists

If IsConnected(svrDALamar) Then
        Set grpsDAGroupColl = svrDALamar.OPCGroups
        grpsDAGroupColl.DefaultGroupIsActive = True
        strGroupName = "NewGroup"
        Set grpDAGroup = grpsDAGroupColl.Add(strGroupName)
        Set itmsDAItemColl = grpDAGroup.OPCItems
        [highlight]DoEvents[/highlight]
        strItemID = Trim(strTagName) & "." & Trim(strParameter)
        itmsDAItemColl.AddItem strItemID, lngClientHandle
        Set itmDAItem = itmsDAItemColl(1)
        [highlight]DoEvents[/highlight]
        grpDAGroup.IsSubscribed = True
        itmDAItem.IsActive = True
        
        [highlight]DoEvents[/highlight]
        CurrentValue = Round(itmDAItem.Value, 2)
        [highlight]DoEvents[/highlight]
    Else
        CurrentValue = "Unable to Connect"
    End If

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]