DDE limitation?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Julian Cox (jac_at_nospam.pagewood.demon.co.uk)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 14:02:52 +0100

Hi all,

I've written a function (DDEsend, see below) to send some work***
cells to another application. DDEsend opens a DDE channel, loops
through the cells sending the value if valid and then closes the
channel. The function returns the number of items sent and only does
the DDEpoke if a commandbar button is down (on/off control). The
function works absolutely perfectly if it is called from another
command button or by running this function from the VB editor:

Function FakeIt()

    Set QuickRange = Active***.Range("AB12:AB19011")
    Sent = DDEsend(QuickRange)
    Debug.Print Sent

End Function

The value printed is exactly what I expect and the data arrives at my
target app. All very nice.

However, if I call the function from a cell everything works except
the DDE. Here's what is in the cell:

        =DDEsend(AB12:AB19011)

The value displayed in the cell is exactly what I expect and exactly
the same value as in the immediate window from FakeIt. But nothing
arrives by DDE. I want to call the function from a cell so that the
data is updated on change.

Here's the function:

Function DDEsend(SendRange)
    Dim ChannelNumber As Long
    Dim CellToPoke As Variant
    Dim IDToPoke As Variant
            
    ' Setup handle to command bar button
    Set ButtonHandle = CommandBars("Triguard").Controls(8)
    
    ' Only do DDE send if button is down
    If ButtonHandle.State = msoButtonDown Then
  
        ' Setup DDE channel
        ChannelNumber = Application.DDEInitiate( _
            app:="WWserver", topic:="AUG_OUT")
        
        ' Counters
        SentCells = 0
        SkippedCells = 0

        For Each Cell In SendRange
            Set CellToPoke = Cell
            If Not (Cell = "") Then
                ' Is it a discrete or a register
                TypeToPoke = Cell.Offset(0, -17)
                
                ' Check the value is legal
                Valid = CheckBounds(TypeToPoke, Cell)
    
                If Valid Then
                    IDToPoke = Left(TypeToPoke, 1) & _
                        LTrim(Str(Cell.Offset(0, -18)))
                    ' Send it
                    Application.DDEpoke _
                        ChannelNumber, IDToPoke, CellToPoke
                    SentCells = SentCells + 1
                Else
                    SkippedCells = SkippedCells + 1
                End If
            End If
        Next Cell
        
        Application.DDETerminate ChannelNumber

        If SkippedCells > 0 Then
            PromptText = "Sent " & SentCells & " to WWServer. " & _
                SkippedCells & " not sent due to invalid values."
            Response = MsgBox(PromptText, vbOKOnly, _
                "Wonderware stimulation")
        End If
    
        DDEsend = SentCells
    Else
        DDEsend = "Off"
    End If
   
End Function

If I step through the code I can't see any differences in the data
types and values using the two call methods. What have I missed?
Have I hit a limitation of DDE, Excel or VBA?

Thanks in advance

Julian

Quantcast