+ Reply to Thread
Results 1 to 7 of 7

Using Excel equation builder in VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    05-29-2015
    Location
    Croatia
    MS-Off Ver
    2013
    Posts
    19

    Using Excel equation builder in VBA

    Hello,
    Is it possible to use Excel VBA to build equations? When I say equations, I mean this:

    https://i.ytimg.com/vi/mAujt1Fs0VY/maxresdefault.jpg

    I've tried recording a macro but it doesn't help. I was trying to see how the code for the fraction 2/3 would look like, but the macro code just reads "?2?3?".
    Any help would be appreciated.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Using Excel equation builder in VBA

    I don't believe the equation editor is exposed in VBA. It's a third-party product; the smaller brother of Design Science's MathType.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Using Excel equation builder in VBA

    I'm not sure equation builder will cooperate with vba very well, but if you can sort out the math/coding on your own, you can definitely program new functions into excel. A quick example would be a quadratic formula UDF:

    
    Function QuadraticFunc(Aval As Long, Bval As Long, Cval As Long)
    Dim Ans1, Ans2 As double
    Ans1 = ((Bval * -1) + WorksheetFunction.ImSqrt((Bval ^ 2) - (4 * Aval * Cval))) / 2 * Aval
    Ans2 = ((Bval * -1) - WorksheetFunction.ImSqrt((Bval ^ 2) - (4 * Aval * Cval))) / 2 * Aval
    QuadraticFunc = Ans1 & " or " & Ans2
    End Function
    =QuadraticFunc(1,6,8) representing x^2 + 6x + 8 returns a value of "-2 or -4"
    Last edited by CAntosh; 06-20-2016 at 12:30 PM. Reason: was hasty, works better as double

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Using Excel equation builder in VBA

    A kludge would be to use a symbol(s). e.g.
    Sub Macro1()
      'Symbols Subset, Number Forms, Vulgar Fraction Two Thirds
      Range("A2").Value = ChrW(WorksheetFunction.Hex2Dec(2154))
    End Sub
    
    Sub Macro2()
        Range("A1").FormulaR1C1 = "2" & Chr(10) & "—" & Chr(10) & "3"
        With ActiveCell.Characters(Start:=1, Length:=2).Font
            .Name = "Calibri"
            .FontStyle = "Regular"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
    End Sub
    Another method might be to copy/paste a pre-created one.

  5. #5
    Registered User
    Join Date
    05-29-2015
    Location
    Croatia
    MS-Off Ver
    2013
    Posts
    19

    Re: Using Excel equation builder in VBA

    The above code works well as a substitute. How would you go on with these symbols if you wanted to have parts of the equation which are not in a fraction, e.g. 5+ 2/3 +4?

  6. #6
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Using Excel equation builder in VBA

    String concatenation:
    Sub Macro3()
      'Symbols Subset, Number Forms, Vulgar Fraction Two Thirds
      With Range("A3")
        .Value = "5 + " & _
          ChrW(WorksheetFunction.Hex2Dec(2154)) & " + 4"
        .EntireColumn.AutoFit
      End With
    End Sub

  7. #7
    Registered User
    Join Date
    05-29-2015
    Location
    Croatia
    MS-Off Ver
    2013
    Posts
    19

    Re: Using Excel equation builder in VBA

    I meant it to look more like this:
    Izrezak.JPG

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. New Report Builder fo Excel workbook
    By Walt Allen in forum Excel General
    Replies: 12
    Last Post: 05-16-2016, 10:27 AM
  2. New Report Builder fo Excel workbook
    By Walt Allen in forum Access Tables & Databases
    Replies: 6
    Last Post: 05-13-2016, 01:59 PM
  3. Displaying objects built in equation builder in a userform
    By Docmartin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-18-2013, 09:29 PM
  4. Replies: 3
    Last Post: 04-24-2012, 02:06 PM
  5. Simple Equation Builder/Solver in Excel for use in Dell Axim X51v
    By Clarence Crow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-21-2006, 04:20 PM
  6. Residential Builder Gantt Chart in Excel
    By R. Drapeau in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-26-2005, 02:05 PM
  7. VBA with Matlab using Excel Link but not Excel Builder
    By James in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-03-2005, 06:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1