VBA - excel does not recognize comma of external data

Question

Good morning to all!

I am trying to integrate excel to other software (the EES - Engineering Equation Solver) through a code in vba.

My program copies certain columns of a table, transfers to the other program, performs the calculations, copies the results and pastes in the corresponding columns of my table in excel. The EES help site itself provides an example program for operation, which I am using as a basis.

The problem is that my program returns decimal values and excel is not recognizing the comma of the pasted data! The strange thing is that decimal data copied from excel to EES is interpreted correctly and if I copy and paste the EES data manually in the spreadsheet the number comes with the comma correctly!

My excel is already set to recognize comma as decimal separator, equal to EES. The code follows below:

Private Sub cmdDDE_Click()
Dim ChNumber As Integer
Dim myShell As String

ChNumber = -1
myShell = frmEESDDE.txtApp.Text

On Error Resume Next

'Copy selected rows into clipboard
Range("B2:G1401").Select
Selection.Copy

Shell_R = Shell(myShell, 1)

If Shell_R <> "" Then
    'Initiate DDE
    ChNumber = Application.DDEInitiate(app:="ees", topic:="")

    If ChNumber <> -1 Then
        'Open EES
        Application.DDEExecute ChannelNumber, "[Open C:\EES\Tablesolve.ees]"
        'Paste data
        Application.DDEExecute ChannelNumber, "[Paste Parametric 'Table 1' R1 C1]"
        'Solve parametrictable
        Application.DDEExecute ChannelNumber, "[SOLVETABLE 'TABLE 1' Rows=1..1400]"
        'Copy results
        Application.DDEExecute ChannelNumber, "[COPY ParametricTable 'Table 1' R1 C7:R1400 C14]"
        'Choose separators
        Application.DecimalSeparator = ","
        Application.ThousandsSeparator = "."
        Application.UseSystemSeparators = False
        'Paste results from EES into EXCEL
        Application.Paste Destination:=Worksheets("Sheet1").Range("H2:O1440")
        Application.UseSystemSeparators = True
        'Quit EES and Terminate DDE
        DDEExecute ChNumber, "QUIT"
        Application.DDETerminate ChNumber
    Else
        MsgBox "Unable to initiate connection to EES", vbExclamation, "EES DDE"
    End If

    frmEESDDE.Hide

Else
        MsgBox "The application, " & myShell & ", was not found", vbExclamation, "EES DDE"
End If

End Sub

PS = Note that I have tried using the command Application.Decimalseparator = "," but it didn’t work. My result of, for example, "15.47" appears in excel as "1.55E+12" or as "1547421377050".

Thanks for your help!

Answer 1

I don’t know this application (ESS), so it is difficult to help you in a more specific way.

However, exsite something generic that you can do and that will most likely solve your problem, you will include it in your VBA in such a way that the process is automatic.

As you say, what is happening is what Excel is expecting "," as decimal separator, but is being pasted ".". The 5 steps below are summarized as follows:

• Change the type of all cells that will receive number to text
• Paste data from the ESS
• Replace, for each cell, "." by ","
• Change the type of all cells to General
• Force each cell to be recognized as Double

See the detailed steps below:

1. Declare the range to be used as a range, to avoid repeating code:

   Dim interval as Range

   Set interval = Worksheets("Sheet1"). Range("H2:O1440")

2. Before from the Paste Data step in Excel, change all cells that will receive numbers to the Text type. This can be done with the following instruction:

   interval.Numberformat = "@"

3. Done the above step, you must execute the instruction that pastes the data in Excel and, soon after, execute the code snippet below. This section replaces, for each cell of the range, the character "." by the character ",".

   Dim Cell as Range

   For Each Cell In Interval Cell.Value = Replace(Cell.Value, ",", ".") Next

4. Now return all cells of the range to the general format:

   Numberformat = "General"

5. As a last step, run the code snippet below, which will convert the value of each cell into a Double. This should be done because Excel will recognize each value as text and you will not be able to apply accounts and formulas in the same.

   Dim Cell as Range

   For Each Cell In Interval Cell.Value = Cdbl(Cell.Value) Next

Some Comments

Step 3 could be done using Replace from the Range itself. This would eliminate the need for the loop. However, while it works when you write a macro, it doesn’t work when you run the macro. People have already reported this (see: https://stackoverflow.com/questions/21437372/selection-replace-doesnt-replace-commas-with-a-dot)

Depending on the number of cells that must be traversed by the two for loop, the solution may become slow.

Both step 3 and step 5 are performed by some kind of Excel bug and these are the slowest steps as they require the loop.