Is there any documentation on how to drive the Office "Equation Editor" through VBA in Excel?

Question

Has anyone worked on using one of the Microsoft equation editors in your VBA code? If so, can you give me some direction where I can find some programming guidance? I want to stick with the editor built in to Excel 2018 or even the OMaths from Word 2018 if at all possible.

The best that I got was if I manually inserted an equation, and it's allocated name was "TextBox 55" then:

ActiveSheet.Shapes("TextBox 55").DrawingObject.Text = "(a+b)/c"

I want to be able to create and edit/read the contents of an "Equation Editor" equation through VBA but there is virtually no information out there. Thought someone would have a clue where to start.

To be clearer, my goal is to write an equation in a cell in a basic text format, but a have a macro that will create the equation object and place it where I want automatically (for now, lets say in the adjacent cell). Then if I edit the equation in the cell my macro will automatically update it. All the add-ins I have seen still have limitations on how they are controlled and I would also like to not have an add-in for when I am distributing my app to my colleagues. I can handle the macro part, its just the documentation on the equation editor that I lack. — Shawn Pauliszyn, Apr 02 '19 at 15:28
I let me proof wrong but I'm pretty sure it is not possible to handle the "Equation Editor" through VBA. Actually that's probably the reason why there is no documetation about it too. — Pᴇʜ, Apr 02 '19 at 15:36
Ok fine, skip the documentation. What about tips and suggestions if not actual documentation. I was able to get this far, someone else must have gotten further. — Shawn Pauliszyn, Apr 02 '19 at 15:57
I was't saying there is no documentation so it does not work. I said because this is not possible at all there is no documentation for it. • Actually if you read the text of your shape and print it `Debug.Print ActiveSheet.Shapes("TextBox 55").DrawingObject.Text` you will see something like this `??+?? ?? = ??=0 ?? ?? ?? ?? ?? ?? ??-??` because VBA cannot handle the formula signs at all. So anything that is not using normal letters will not work. Something simple like `(a+b)/c` will work but anything that uses formula signs cannot be handled by VBA. — Pᴇʜ, Apr 02 '19 at 16:02
For example put a complicated formula into your box and run `ActiveSheet.Shapes("TextBox 55").DrawingObject.Text = ActiveSheet.Shapes("TextBox 55").DrawingObject.Text` even this makes your formula useless. Which is a proof that this cannot work at all. — Pᴇʜ, Apr 02 '19 at 16:06
@ Pᴇʜ: You *can*, but it's getting really complicated. You can compose special characters by concatenating two `chrW`-characters, see https://stackoverflow.com/a/55418901/7599798. But I doubt it will be possible to create reliable formulas with that technique. All in all it's not only dealing with the right characters, you have also find a way how to arrange them - think about sums, roots, fractions... — FunThomas, Apr 02 '19 at 16:28
I have no problem with translating the 'italic' characters using chrw() like FunThomas mentioned. Again, I can handle all the programming in VBA. If you set the text to a simple formula and press the 'professional' button, the equation editor will convert things. I can learn what the editor likes using trial and error. But how to 'problematically' access the 'professional' or 'linear' method is what I would like to do. Please stop trying to discourage and let some other's have a crack at this. — Shawn Pauliszyn, Apr 02 '19 at 16:40
@ShawnPauliszyn Actually I didn't try to discourage you but to mention what we cannot do. Sometimes it is good to know what is not possible so you can think of new ways. • But anyway this discussion resulted in your note about that it works somehow better if the equation is in linear mode, which made me think off a workaround. I posed it as an answer. Still this is not perfect and still no nice solution. But since VBA doesn't support the equation editor this is probably the only way to workaround it. — Pᴇʜ, Apr 03 '19 at 09:38
@Pᴇʜ If you are talking about workarounds, I wonder if something ugly but usable could be cobbled together with the evil `SendKeys` — John Coleman, Apr 03 '19 at 10:18

Pᴇʜ · Answer 1 · 2019-04-03T14:26:07.000

Note that this is a workaround only (since there is no official way):

You can use Application.CommandBars.ExecuteMso to run any buttons on the ribbon. Eg Application.CommandBars.ExecuteMso "EquationProfessional" to convert the formula into professional style.

See Are the command codes for ExecuteMso documented? for how to get the identifiers.

Here are some ideas. Note that you need to create an equation shape object and call it Textfeld 1 before you run this codes. I didn't manage to create one via VBA but you can at least change the content of an existing equation.

TestWriteFormulaA writes the formula
TestWriteFormulaB writes the formula
MakeEquationLinear converts eg the FormulaA into the linear form
MakeEquationProfessional converts it back to the professional form
GenerateAscWFromString generates the ChrW combination of a equation. Note that the equation can only be changed in the linear form MyEquation.DrawingObject.Text =. So make sure it is linear befor you generate the ChrW combination.

Note that you can convert most signs into commands like \sum for the sum sign ∑. So instead of the Chrw combination you can use eg

MyEquation.DrawingObject.Text = "(x+a)^n=\sum_(k=0)^n (n¦k)x^k a^(n-k)"

to write FormulaA.

Here you can find an inoffical documentation about the equation commands:
http://www.iun.edu/~mathiho/useful/Equation%20Editor%20Shortcut%20Commands.pdf

Option Explicit

' write a formula A
Public Sub TestWriteFormulaA()
    Dim MyEquation As Shape
    Set MyEquation = ThisWorkbook.Worksheets("Sheet1").Shapes("Textfeld 1")

    MakeEquationLinear MyEquation

    MyEquation.DrawingObject.Text = ChrW(40) & ChrW(-10187) & ChrW(-9115) & ChrW(43) & ChrW(-10187) & ChrW(-9138) & ChrW(41) & ChrW(94) & ChrW(-10187) & ChrW(-9125) & ChrW(61) & ChrW(8721) & ChrW(50) & ChrW(52) & ChrW(95) & ChrW(40) & ChrW(-10187) & ChrW(-9128) & ChrW(61) & ChrW(48) & ChrW(41) & ChrW(94) & ChrW(-10187) & ChrW(-9125) & ChrW(9618) & ChrW(12310) & ChrW(40) & ChrW(-10187) & ChrW(-9125) & ChrW(166) & ChrW(-10187) & ChrW(-9128) & ChrW(41) & ChrW(32) & ChrW(-10187) & ChrW(-9115) & ChrW(94) & ChrW(-10187) & ChrW(-9128) & ChrW(32) & ChrW(-10187) & ChrW(-9138) & ChrW(94) & ChrW(40) & ChrW(-10187) & ChrW(-9125) & ChrW(8722) & ChrW(-10187) & ChrW(-9128) & ChrW(41) & ChrW(32) & ChrW(12311)
    'same as below which was converted manually to the command structure
    'MyEquation.DrawingObject.Text = "(x+a)^n=\sum_(k=0)^n (n¦k)x^k a^(n-k)"
    MakeEquationProfessional MyEquation
End Sub


' write another forumla B
Public Sub TestWriteFormulaB()
    Dim MyEquation As Shape
    Set MyEquation = ThisWorkbook.Worksheets("Sheet1").Shapes("Textfeld 1")

    MakeEquationLinear MyEquation

    MyEquation.DrawingObject.Text = ChrW(40) & ChrW(49) & ChrW(43) & ChrW(-10187) & ChrW(-9115) & ChrW(41) & ChrW(94) & ChrW(-10187) & ChrW(-9125) & ChrW(61) & ChrW(49) & ChrW(43) & ChrW(-10187) & ChrW(-9125) & ChrW(-10187) & ChrW(-9115) & ChrW(47) & ChrW(49) & ChrW(33) & ChrW(43) & ChrW(40) & ChrW(-10187) & ChrW(-9125) & ChrW(40) & ChrW(-10187) & ChrW(-9125) & ChrW(8722) & ChrW(49) & ChrW(41) & ChrW(32) & ChrW(-10187) & ChrW(-9115) & ChrW(94) & ChrW(50) & ChrW(41) & ChrW(47) & ChrW(50) & ChrW(33) & ChrW(43) & ChrW(8230)

    MakeEquationProfessional MyEquation
End Sub


'get the ChrW combination of an equation in the immediate window
Public Sub TestGetChrWFromEquation()
    Dim MyEquation As Shape
    Set MyEquation = ThisWorkbook.Worksheets("Sheet1").Shapes("Textfeld 1")

    MakeEquationLinear MyEquation

    GenerateAscWFromString MyEquation.DrawingObject.Text

    MakeEquationProfessional MyEquation
End Sub




Public Sub MakeEquationLinear(ByVal Equation As Shape)
    Dim OriginalSheet As Object
    If Equation.Parent.Name <> ActiveSheet.Name Then
        Set OriginalSheet = ActiveSheet
        Equation.Parent.Activate
    End If

    Equation.Select
    Application.CommandBars.ExecuteMso "EquationLinearFormat"

    If Not OriginalSheet Is Nothing Then OriginalSheet.Activate
End Sub

Public Sub MakeEquationProfessional(ByVal Equation As Shape)
    Dim OriginalSheet As Object
    If Equation.Parent.Name <> ActiveSheet.Name Then
        Set OriginalSheet = ActiveSheet
        Equation.Parent.Activate
    End If

    Equation.Select
    Application.CommandBars.ExecuteMso "EquationProfessional"

    If Not OriginalSheet Is Nothing Then OriginalSheet.Activate
End Sub

Public Sub GenerateAscWFromString(ByVal InputString As String)
    Dim OutputString As String

    Dim ChrIdx As Long
    For ChrIdx = 1 To Len(InputString)
        OutputString = OutputString & IIf(OutputString <> vbNullString, " & ", "") & "ChrW(" & AscW(Mid$(InputString, ChrIdx, 1)) & ")"
    Next ChrIdx
    Debug.Print OutputString
End Sub

Very impressive, but despite all the work probably not very usable since the Excel object model doesn't provide the tools that you would need for a nice solution. — John Coleman, Apr 03 '19 at 09:10
@JohnColeman I added some information about how to write formulas with commands instead of `ChrW`. So you can write FormulaA with something like `(x+a)^n=\sum_(k=0)^n (n¦k)x^k a^(n-k)` which makes it pretty usable. The only missing thing is that I did not manage to create an equation shape with VBA so we can still only change existing equations. — Pᴇʜ, Apr 03 '19 at 09:33
That does make it more flexible. At this stage you are essentially writing LaTex. I find it surprising that there isn't an open-source project which embeds LaTex formulas into Excel. I have found references to things which work with PowerPoint. Perhaps one of those could be tweaked. — John Coleman, Apr 03 '19 at 10:16
@JohnColeman Actually it looks very similar to TeX but it isn't exactly the same syntax. But actually [Word Version 1707 ( Build 8326.2058) and higher](https://support.office.com/en-ie/article/write-an-equation-or-formula-1d01cabc-ceb1-458d-bc70-7f9737722702) supports LaTeX Math Equation syntax. Still no idea why the feature didn't make it into Excel yet. But it looks like Microsoft understood that people want to see that feature. — Pᴇʜ, Apr 03 '19 at 10:30
I think that you have done about as much as can be done (without heroic low-level undocumented API calls). Hopefully OP will accept this answer since it is unlikely that there is anything better. — John Coleman, Apr 03 '19 at 10:43
My intention is to simply write equations in a cell and have the macro automatically update it in the equation object. I can handle all the math to show step by step itterations. And I am only using it for engineering equations so they are never extremely complex. But @PEH, these are the types of solutions I are what I need to get things happening, even if its brute force. — Shawn Pauliszyn, Apr 05 '19 at 02:58
Sorry if I offended you @PEH but not so sorry because look what it spurred you on to do!! Great job! I will look forward to trying all this when I get back from my vacation — Shawn Pauliszyn, Apr 05 '19 at 03:00
@ShawnPauliszyn No worries, sometimes we need a little dispute to be able to go new ways and think out of the box. Without your initial information (that the linear form works) I would have said it is not possible. That was the missing information to think out of the box. — Pᴇʜ, Apr 05 '19 at 08:23

Is there any documentation on how to drive the Office "Equation Editor" through VBA in Excel?

1 Answers1

Linked