Convert Excel Equation Editor output into a form that can more easily be converted into an Excel Worksheet formula

Question

I am attempting to convert formulas produced by the Excel Equation Editor to functional Excel cell formulas. So starting with a formula like: I used Selection.Text method and got: ??= ?? -1.93703+5.334373?? 1+ ?? -1.93703+5.334373?? -80.864 ?? 9 +243.06 ?? 8 -228.74 ?? 7 +21.697 ?? 6 +72.108 ?? 5 -21.632 ?? 4 -8.2818 ?? 3 +2.2854 ?? 2 +0.44679??-0.045565 ?? 2 =0.971315
where the ?? pairs are a representation of the italic y, e, R, x and minus signs

This code allowed me to figure out what was going on with that:

Sub subCheckASCII()
'Select a range or textbox created by excel formula editor and extract the formula from it
'This program developed to start to figure out how to convert output into a functional Excel formula
'Run first time to figure out what the sOutputChar is for each negative AscW and add
'  :sOutputChar = <appropriate translation character> to each Case element

Dim sInput As String
Dim sChar As String
Dim lPos As Long
Dim lLength As Long
Dim sDesc As String
Dim lCorrectUnicode As Long
Dim x As Variant
Dim sOutput As String
Dim sOutputChar As String

Set x = Selection
If VarType(x) = 9 Then   'VarType 9 = Object
    'Text Boxes
    sInput = x.Text
Else
    'Ranges
    sInput = Selection.Text
End If

Debug.Print sInput, Len(sInput)

For lPos = 1 To Len(sInput)
    sChar = Mid(sInput, lPos, 1)
    Select Case AscW(sChar)
    Case -10187: sDesc = vbNullString   '"Surrogate Marker"
    Case -9114: sDesc = "Italic y": sOutputChar = "y"
    Case -9147: sDesc = "Italic R": sOutputChar = "R"
    Case -9115: sDesc = "Italic x": sOutputChar = "x"
    Case -9134: sDesc = "Italic e": sOutputChar = "e"
    Case Is < 0
         MsgBox "Add another Case element to code for " & AscW(sChar)
         Stop
    Case 8722: sDesc = "Minus sign": sOutputChar = "-"
    Case 32: sDesc = "<space>": sOutputChar = " "
    Case 63: sDesc = "Real Question mark": sOutputChar = "?"
    Case Else: sDesc = sChar: sOutputChar = sDesc
    End Select
    sOutput = sOutput & sOutputChar
    Debug.Print lPos, LenB(sChar), sChar, Asc(sChar), AscW(sChar), sDesc
    
    If lPos / 175 = lPos \ 175 Then Stop  'So first lines don't scroll out of Immediate window
Next

Debug.Print "Input:"
Debug.Print sInput
Debug.Print "Output:"
Debug.Print sOutput

End Sub

But even after I converted all of the ?? to a more useful form:

From:

??= ?? -1.93703+5.334373?? 1+ ?? -1.93703+5.334373?? -80.864 ?? 9 +243.06 ?? 8 -228.74 ?? 7 +21.697 ?? 6 +72.108 ?? 5 -21.632 ?? 4 -8.2818 ?? 3 +2.2854 ?? 2 +0.44679??-0.045565 ?? 2 =0.971315

To:

y= e -1.93703+5.3343733x 1+ e -1.93703+5.3343733x -80.864 x 9 +243.06 x 8 -228.74 x 7 +21.697 x 6 +72.108 x 5 -21.632 x 4 -8.2818 x 3 +2.2854 x 2 +0.446799x-0.045565 R 2 =0.971315

Easy to split out the R formula, but there is still a lot of work to be done to convert that string into a functional formula and with the lack of parenthesis in the exponents, I am not sure how I could do it.

Alternately, If I highlight the first row of the text box and copy paste it into a cell, I get: y=e^(-1.93703+5.334373x)/(1+e^(-1.93703+5.334373x) )-80.864x^9+243.06x^8-228.74x^7+21.697x^6+72.108x^5-21.632x^4-8.2818x^3+2.2854x^2+0.44679x-0.045565

Which has "magically" added parenthesis and carets to indicate exponents. This I can convert more readily.

I will be dealing with a lot of formulas and would like to automate the import process. Is there a way to extract the first formula (or even all formulas from the text box) to obtain the second results without manually selecting the contents of the text box?

Answer 1

After more searching I found an answer: This answer: Is there any documentation on how to drive the Office "Equation Editor" through VBA in Excel? Gave me this command:

Application.CommandBars.ExecuteMso "EquationLinearFormat"

which converts the Equation Editor professional output into a linear form.

I posted this anyway for general info and to see if others had any other solutions that might be more efficient.