locked
Combining strings from 2 cells, adding a hyfin and sending using DDEPoke RRS feed

  • Question

  • Hello, thanks for reading.

    I'm trying to sort something out that an ex-coworker created. I'm not much of a PC programmer.

    For iBit = 0 To 15
                Sheets("TestPoints").Cells(3, 5) = iMod
                Sheets("TestPoints").Cells(4, 5) = iBit
                
                'IO_ScreenDescriptionStorage[0]
                           
                
                sWriteString = "IO_DescriptionStorage[" & iMod & "," & iBit & "]"
                If Sheets("TestPoints").Cells(iIONameRowNum + iBit, iColumn) = "" Then
                   -->DDEPoke RSIchan, sWriteString, "Spare"<--
                Else
                    Value = Sheets("TestPoints").Cells(iIONameRowNum + iBit, iColumn) & " - " &           Sheets("TestPoints").Cells(iDeviceStartRow + iBit, iColumn)
                   -->DDEPoke RSIchan, sWriteString, Value<--
                End If
                           
              
        Next iBit

    Next iMod

    I've been able to figure out where the problem is which I highlighted in bold. Edit: I just noticed the bold doesn't show up. Marked with --> <-- instead.

    DDEPoke RSIchan, sWriteString, "Spare" is just trying to send the word spare if the cell is empty

    DDEPoke RSIchan, sWriteString, Value is trying to send the contents of the variable Value.

    I did some digging trying to figure out the DDEPoke and through trial and error / google I can get the DDEPoke to work but only with one cell and the Set keyword. I haven't figured anything out on how to send the word "Spare". See below:

    sWriteString = "IO_DescriptionStorage[" & iMod & "," & iBit & "]"
                If Sheets("TestPoints").Cells(iIONameRowNum + iBit, iColumn) = "" Then
                    DDEPoke RSIchan, sWriteString, "Spare"   
                Else
              --> Set Value = Sheets("TestPoints").Cells(iIONameRowNum + iBit, iColumn)  'only one cell
                    DDEPoke RSIchan, sWriteString, Value
                End If

    I've tried   Set Value = Sheets("TestPoints").Cells(iIONameRowNum + iBit, iColumn) & " - " &           Sheets("TestPoints").Cells(iDeviceStartRow + iBit, iColumn)   but it throws up errors.

    How Can I send the word Spare?

    How can I combine strings from 2 different cells, separate the data with a - and send it? Ex: Cell1 - Cell2

    Any help is appreciated.

    Thanks

    Shane


    • Edited by shane2977 Sunday, January 7, 2018 2:54 PM Bold text doesn't show
    Sunday, January 7, 2018 2:35 AM

All replies

  • Hi shane2977,

    you had asked,"How can I combine strings from 2 different cells, separate the data with a - and send it? Ex: Cell1 - Cell2"

    refer example below.

    code to merge value using Range:

    Sub demo()
    Dim sht As Worksheet
    Set sht = Sheets(1)
    Dim str As String
    str = sht.Range("A1").Value & " - " & sht.Range("B1").Value
    Debug.Print str
    
    End Sub

    Output:

    code to merge value using Cells:

    Sub demo()
    Dim sht As Worksheet
    Set sht = Sheets(1)
    Dim str As String
    str = sht.Cells(1, 1).Value & " - " & sht.Cells(1, 2).Value
    Debug.Print str
    
    End Sub
    

    Output:


    other thing you had asked,"How Can I send the word Spare?"

    first you did not mentioned that what is "DDEPoke" and what it do in your code?

    you did not posted the full code so we don't know that what do you mean by "Send the Word".

    where you want to send the word? outside Excel? in any other Office Application?

    please try to clear it. it will help us to understand the issue in better way.

    if you just want to set the value on Worksheet then my example code will do that.

    you can also post the error. it will give us more information about the issue.

    if possible for you then you can post your sample code here. we will try to make a test with it and try to reproduce the issue on our side.

    we will try to provide further suggestions to correct it.

    Regards

    Deepak


    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.


    Monday, January 8, 2018 2:53 AM
  • Thanks for your time Deepak

    Answers:

    • The program is designed to take data from the worksheet and send it to a PLC (Programmable Logic Controller). The Excel worksheet doesn't do anything with the data. So yes outside excel.
    • The word "Spare" is just a string containing Spare. It doesn't come from anywhere in the program. If the cell has nothing in it when the program looks at it, then I want the program to send the word Spare to the PLC.
    • I thought DDEPoke was a predefined method of Excel VBA. See link (https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-ddepoke-method-excel). It sends data via a previously open channel (using DDEInitiate).
    • I would prefer not to make the full program public as it's proprietary. I can email it to you.
    • In it's original form the program doesn't throw up any errors. But it doesn't write the data to the PLC.

    Through trial and error I found there is 2 ways I can get the program to send data but only the data in 1 cell. 

    1.

    Set Value = Sheets("TestPoints").Cells(iIONameRowNum + iBit, iColumn) 

                  DDEPoke RSIchan, sWriteString, Value

    2.

    DDEPoke RSIchan, sWriteString, Sheets("TestPoints").Cells(iIONameRowNum + iBit, iColumn)

    What I'm trying to do is combine and send, so I tried the following:

    1.

    Set Value = Sheets("TestPoints").Cells(iIONameRowNum + iBit, iColumn) & " - " & Sheets("TestPoints").Cells(iDeviceStartRow + iBit, iColumn)

    DDEPoke RSIchan, sWriteString, Value

    This throws an error when it tries to Set the Value Run-time error '13': Type mismatch. So this isn't the right way to use the Set keyword

    2.

    DDEPoke RSIchan, sWriteString, Sheets("TestPoints").Cells(iIONameRowNum + iBit, iColumn) & " - " & Sheets("TestPoints").Cells(iDeviceStartRow + iBit, iColumn)

    This doesn't throw any errors but it doesn't write anything to the PLC.

    Question:

    1. Is there a way to combine the cells and add the "-" using Set? or Can the cells be combined and "-" added then the value Set after its combined?

    Thanks again for your help

    Monday, January 8, 2018 3:30 PM
  • Hi shane2977,

    you had asked,"Is there a way to combine the cells and add the "-" using Set? or Can the cells be combined and "-" added then the value Set after its combined?"

    Answer is Yes.

    I already provided you a code in my last post, which can solve the issue.

    further , lets check your code.

    below is the exact code posted by you.

    Set Value = Sheets("TestPoints").Cells(iIONameRowNum + iBit, iColumn) & " - " & Sheets("TestPoints").Cells(iDeviceStartRow + iBit, iColumn)
    
    DDEPoke RSIchan, sWriteString, Value
    

    you had use the 'Value' as an Object.

    you need to declare it like string variable.

    then after no need to use 'set' keyword.

    so updated code will look like below.

    Dim Value As String
    Value = Sheets("TestPoints").Cells(iIONameRowNum + iBit, iColumn) & " - " & Sheets("TestPoints").Cells(iDeviceStartRow + iBit, iColumn)
    
    DDEPoke RSIchan, sWriteString, Value

    you can make a test on your side and let me know about the result.

    I will try to provide further suggestions, if issue persist.

    Regards

    Deepak


    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.

    Tuesday, January 9, 2018 7:55 AM
  • Hi shane2977,

    Is your issue resolved?

    You did not done any follow up on this thread for a long time.

    If your issue is solved then I suggest you to share your solution and mark it as an answer.

    If your issue is still persist then I suggest you to refer the suggestions given by the community members may help you to solve your issue.

    If then after you have any further questions then you can let us know about it.

    We will try to provide further suggestions to solve it.

    I suggest you to update the status of this thread. This thread is still open and will remain open until you mark the answer.

    So take appropriate steps to close this thread , If your issue is resolved.

    Thanks for your understanding.

    Regards

    Deepak


    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.

    Thursday, January 18, 2018 9:20 AM