My DDEPoke Command Works with 1 variable- Why won't it work with 2?

[JackDaley]

Hey Everyone!

I'm stumped on the last step of a VBA challenge. I'd really appreciate any advice or suggestions. I feel like I'm doing something obviously wrong.

Summary of my problem:

(Scroll to the bottom to go directly to the code and screenshots)

I'm using an OPC connection to send data between Excel 2010 and an external location. I can't poke directly to the OPC cells because the formulas will be overwritten with values. So I copy the values and formulas to sheets 2 and 3 and use them as variables to make a DDEPoke command. This pokes the data to the cell location currently selected on Sheet3

My solution:

• All live OPC data is on Sheet1
• Macro1 copies Sheet1 and pastes as values to Sheet2 (Works)
• A macro copies Sheet1 and pastes as formulas to Sheet3 (Works)
• A macro sets the active cell on Sheet3 as a variable, and makes 2 variables (representing the row and column numbers of the cell) (Works)
• Variable "External_Tag" is made by taking the selected cell value from Sheet3 (Works)
• Sheet2 is activated (Works)
• The OPC connection is initiated (Works)
• The selected cell from Sheet3 is set as the selected cell on Sheet2 (Works)
• The DDEPoke command runs using this line: "DDEPoke connection, External_Tag, Cells(Y, X)"

The problem:

• The DDEPoke command runs without errors, but the data is NOT poked to the External_Tag
• A msgbox command before the DDEPoke shows that all of the variables used in the command are loading correctly

My (probably wrong) gut feeling:

• The variables are right (confirmed by msgbox test), but the 3 parts of the command aren't being joined together right.

Working example which uses the external tag as a literal variable instead of grabbing it from another sheet:

'variables for X and Y, literal variable for Tag - WORKS
                  Sub Button2_Click()
                                  'sets variable as string.
                                          Dim Tag1 As String
                                  'brings focus to Sheet2
                                              Worksheets("Sheet2").Activate
                          'sets variable to literal tag
                                              Tag1 = "Location.Data[1].Description.Number"
                          'opens connection            
                                          connection = OpenConnection()
                          'sets selected cell as variable, sets X and Y location values as variables
                        Set Active_Cell = Selection
                          X = Active_Cell.Column
                          Y = Active_Cell.Row
                    'sends data to OPC       
                                          DDEPoke connection, Tag1, Cells(Y, X)
                End Sub

Non-Working Example (My question) which uses a (not literal) variable for the external tag:

  'Variables for X and Y, Variables for Tag - DOESN'T WORK
                      Sub Button6_Click()
        'sets variable as string.
                                Dim External_Tag As String
            'brings focus to Sheet3, sets highlighted cell address to variable Active_Cell - WORKS
                                Worksheets("Sheet3").Activate
                    Set Active_Cell = Selection
        'sets X and Y to the column and row index numbers. eg. X for cell C5 becomes 3 and Y for C5 becomes 5 - WORKS
                  X = Active_Cell.Column
                  Y = Active_Cell.Row
        'sets this variable to the contents of this cell - WORKS
                                External_Tag = Worksheets("Sheet3").Cells(Y, X).Value
            'brings focus to sheet - WORKS
                                Worksheets("Sheet2").Activate
            'opens opc connection - WORKS
                                connection = OpenConnection()
            'uses X Y variable numbers to set highlighted cell - WORKS 
                  ActiveSheet.Cells(Y, X).Select
          'msgbox prints all of the correct variables out individually. - WORKS
                              MsgBox External_Tag & Y & X
          'trouble spot - runs without errors, but ultimately doesn't send the data - DOESN'T WORK
                              DDEPoke connection, External_Tag, Cells(Y, X)
                End Sub

Any ideas?

Thanks

 

[JackDaley]

Update: SOLVED!

The two variables were not really equal. The non-working one had quotation marks.