Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2016-11-30
Medium Priority
89 Views
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
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

LVL 52

Accepted Solution

Rgonzo1971 earned 2000 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
``````
Regards
0

## Featured Post

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month11 days, 11 hours left to enroll