Solved

How to check with VBA if an excel formula has multiple terms in it?

Posted on 2016-11-30
2
77 Views
Last Modified: 2016-12-01
Problem
My macro creates formulas to be displayed in word, so I don't want unnecessary brackets. The formula obtained from an excel cell will be multiplied by a factor, so a bracket will be necessary if it has many terms.

eg the factor is A20, I want the program to know that if the formula is A1, the A1*A20 is fine without bracket, however if the formula is A1+B1, then A1+B1*A20 is incorrect. It will have to be (A1+B1)*A20. I'll deal with the brackets the current function should just return if there are multiple terms.

It is fine if the answer just explains how the program should work, in case you don't use VBA. (Ideally an explanation should have enough detail for me to be able to write the correct program based on it)

Example inputs and outputs (true for multiple terms)
I: (A1-B1)/LOG(A1-B1)                               O: False
I: (A1+B1)/(A2-B2)                                      O:False
I: PI()/A1                                                       O: False
I: A1                                                              O:False

I: LOG(A1)-(A1-B1)/C1                                O:True
I: A1+B1                                                       O: True
I: (A1+B1)/(A2-B2)-(C1+D1)/(C2-D2)         O:True

Previous ideas
I was considering some not too elegant solutions before.
For example I could just substitute some numbers into the formula (with the factor) and see if adding a bracket returns a different value. (I think this would not always work)

I was also considering finding each + and - sign and looking for the terms next to them to see if they are enclosed in a bracket together somewhere. I'm not sure how to implement this and I can't prove to myself that it always works.
0
Comment
Question by:Laszlo Benedek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41908250
HI,

you could use a function like this
Sub Macro()
res = CellRegexSimple("(A1+B1)/(A2-B2)-(C1+D1)/(C2-D2)", "[\-\+](?=\()|([\-\+])(?!.*\))")
End Sub

Function CellRegexSimple(Myrange As String, strPattern As String) As Boolean
      Dim regex As Object
      
      Set regex = CreateObject("VBScript.RegExp")
      Dim strInput As String


      If strPattern <> "" Then
          strInput = Myrange

          With regex
              .Global = True
              .MultiLine = True
              .IgnoreCase = False
              .Pattern = strPattern
          End With

          If regex.Test(strInput) Then
              CellRegexSimple = True
          Else
              CellRegexSimple = False
          End If
      End If
      Set regex = Nothing
End Function

Open in new window

Regards
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question