none
Read string from cell in VBA RRS feed

  • Question

  • I'm a complete novice with VBA, so hopefully this won't be too difficult for someone in the know.  I'm trying to read the text from a cell in Excel (a PLC tagname) into a variable in VBA, then use the string variable to read that tag from the PLC.  I've successfully used the DDERequest instruction to read known tagnames from the PLC, but I now want to be able to read variable tagnames from cells in Excel, but the line TagName = Cells("i", "D").Value returns runtime error 13, type mismatch.  Can anyone advise the error I'm making?  The tagname text is in column D, starting at row (i).  I've also tried TagName = Cells("i", "D").Text, but get the same error.

    Private Sub CommandButton1_Click()

        rslinx = OpenRSLinx() 'Open connection to RSlinx

        Dim LENGTH As Long
        Dim TagName As String
        LENGTH = Cells(1, "B").Value

        'Loop through reading the CLX tags and
        'put the values into cells
        For i = 2 To (LENGTH + 2)
            'Read TagName
            TagName = Cells("i", "D").Value
            'Read Tag Value
            'Get the value from the DDE link
            TagValue = DDERequest(rslinx, "TagName,L1,C1")

            'If there is an error, display a message box
            If TypeName(TagValue) = "Error" Then
                If MsgBox("Error reading Parameter[" & i & "] " & _
                    "Continue with Read?", vbYesNo + vbExclamation, _
                    "Error") = vbNo Then Exit For
            Else
                'No error, place data in cell
                Cells(2 + i, 5) = TagValue
            End If

        Next i

        'Terminate the DDE connection
        DDETerminate rslinx

    End Sub

                               
    Thursday, July 26, 2018 3:26 PM

Answers

  • Hello Roksan,

    Please try

    TagName = Cells(i, "D").Value

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Roksan Friday, July 27, 2018 12:54 PM
    Friday, July 27, 2018 1:18 AM

All replies

  • Hello Roksan,

    Please try

    TagName = Cells(i, "D").Value

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Roksan Friday, July 27, 2018 12:54 PM
    Friday, July 27, 2018 1:18 AM
  • Many thanks Terry - sorted!  I don't think I'll ever make a VBA programmer (too long in the tooth now).

    Your help is much appreciated. :)

    Friday, July 27, 2018 12:56 PM