# VB: .pattern problem, Need a UDF to find a MAX Number, within a Formula • ### Question

• 1.  where is a chart? for the meanings behind:  aRegExp.Pattern = "   symbols.  (i was on a regexp forum, but think question is bigger/ other xl)

examples seen are:

string pattern = @"/(\d+?)/rs/"

`Regexp.Pattern="[0-9][0-9]*"finding some info at:  https://en.wikibooks.org/wiki/Visual_Basic/Regular_Expressions  `

2.  i have a formula i need to find the max number within a formula, for numbers after a > or < sign  (and if matters:  followed by a comma)

=IF(A1>7,IF(A1>9,"Y","X"),IF(A1>3.5,"A",IF(A1>2,"B",IF(A1>.50,"C","D"))))  i need to find the:  MAX(7,9,3.5,2,.50)  with end result of:  9

note:  the number will follow either a less than, or greater than sign.

i think a UDF would work fine for this, and i found a couple of examples that are supposed to work on the same string, and i cannot see how to change them (to discern pattern symbols etc) to work with the above example (> or < signs);  the first one does not seem to use a pattern:  (thanks)

Function maxSTR(rng As String) As Double  'prob: command in cell not go to correct case, find reset note / what wrong (make istrue fix note)
Dim splt() As String: Dim i&
splt = split(rng)
For i = LBound(splt) To UBound(splt)
If IsNumeric(splt(i)) Then
If splt(i) > maxSTR Then
maxSTR = splt(i)
End If
End If
Next i
'test YES for STRING below,  need for: > or <
'[[ E:\DATA\SQL\SY0\ , 19198 ],[ E:\ , 18872 ],[ E:\DATA\SQL\ST0\ , 26211 ],[ E:\DATA\SQL\ST1\ , 26211 ],[ E:\DATA\SQL\SD0\ , 9861 ],[ E:\DATA\SQL\SD1\ , 11220 ],[ E:\DATA\SQL\SL0\ , 3377 ],[ E:\DATA\SQL\SL1\ , 1707 ],[ E:\DATA\SQL_Support\SS0\ , 14375 ],[ E:\DATA\SQL_Support\SS1\ , 30711 ]]
End Function

The next example is supposed to work on the above, same string but i could not get it to work at all.  this example has the .pattern item.

Function maxSTR1(S As String) As Double  'max string  NO
Dim RE As Object, MC As Object, M As Object, D As Double
Set RE = CreateObject("vbscript.regexp")

With RE   'eg's:  aRegExp.Pattern = "\.gif[\w+ ?= ?\w+]*>"    at: http://www.programmingexcel.info/Regular_expressions_for_replacements_in_Excel_.html
.Global = True
.pattern = ">b[0-9]*,.?[0-9]+>b"    'pattern prob ?  goog: aRegExp.Pattern = "
'.pattern = "\b[0-9]*\.?[0-9]+\b"    'orig
If RE.test(S) = True Then
For Each M In MC
D = IIf(D > CDbl(M), D, CDbl(M))
Next M
End If
End With
'test NO for below,  need for: > or <
'[[ E:\DATA\SQL\SY0\ , 19198 ],[ E:\ , 18872 ],[ E:\DATA\SQL\ST0\ , 26211 ],[ E:\DATA\SQL\ST1\ , 26211 ],[ E:\DATA\SQL\SD0\ , 9861 ],[ E:\DATA\SQL\SD1\ , 11220 ],[ E:\DATA\SQL\SL0\ , 3377 ],[ E:\DATA\SQL\SL1\ , 1707 ],[ E:\DATA\SQL_Support\SS0\ , 14375 ],[ E:\DATA\SQL_Support\SS1\ , 30711 ]]
End Function

• Edited by Friday, May 26, 2017 3:01 AM
Friday, May 26, 2017 2:29 AM

• Non-Regex Version

```Function maxSTR(rng As Range) As Double

Dim splt As Variant
Dim i As Long
Dim c As Long

splt = Split(rng.Formula, ",")

For i = LBound(splt) To UBound(splt)
c = InStr(1, splt(i), ">")

If c = 0 Then
c = InStr(1, splt(i), "<")
End If

If c > 0 Then
If Mid(splt(i), c + 1, 100) > maxSTR Then
maxSTR = Mid(splt(i), c + 1, 100)
End If
End If
Next i

End Function
```

Regex Version

```Function maxSTRr(rng As Range) As Double
Dim RE As Object, MC As Variant, M As Object, D As Double
Set RE = CreateObject("vbscript.regexp")

S = rng.Formula

With RE
.Global = True
.Pattern = "(>|<)([0-9]+),"

If RE.test(S) = True Then
Set MC = RE.Execute(S)
For Each M In MC
D = IIf(D > CDbl(M.SubMatches(1)), D, CDbl(M.SubMatches(1)))
Next M

End If
End With

maxSTRr = D

End Function
```

Best Regards,

• Marked as answer by Saturday, May 27, 2017 3:23 PM
Saturday, May 27, 2017 8:55 AM

### All replies

• Non-Regex Version

```Function maxSTR(rng As Range) As Double

Dim splt As Variant
Dim i As Long
Dim c As Long

splt = Split(rng.Formula, ",")

For i = LBound(splt) To UBound(splt)
c = InStr(1, splt(i), ">")

If c = 0 Then
c = InStr(1, splt(i), "<")
End If

If c > 0 Then
If Mid(splt(i), c + 1, 100) > maxSTR Then
maxSTR = Mid(splt(i), c + 1, 100)
End If
End If
Next i

End Function
```

Regex Version

```Function maxSTRr(rng As Range) As Double
Dim RE As Object, MC As Variant, M As Object, D As Double
Set RE = CreateObject("vbscript.regexp")

S = rng.Formula

With RE
.Global = True
.Pattern = "(>|<)([0-9]+),"

If RE.test(S) = True Then
Set MC = RE.Execute(S)
For Each M In MC
D = IIf(D > CDbl(M.SubMatches(1)), D, CDbl(M.SubMatches(1)))
Next M

End If
End With

maxSTRr = D

End Function
```

Best Regards,

• Marked as answer by Saturday, May 27, 2017 3:23 PM
Saturday, May 27, 2017 8:55 AM
• update:  seems i may have finally found a ms site describing regex. with goog:  excel version vbscript.regexp site:microsoft.com

hard to find ?!!  not sure if can see an excel version required..

https://msdn.microsoft.com/en-us/library/ms974570.aspx

==========

Thank you !!  that worked great for the non regex version.  have to ask if there is a minumum excel version required for the 2nd / regex version ?  (i have not been able to get any sample of regex to work so far).

(For the REGEX example, a compile is asking to define:  S)  i am trying to make those changes, but not sure i will get that.

FOR THIS ITEM it looks like the problem might be: having both signs in 1 formula for the maxSTR udf, for both:  > and <

i have an example for adding an Array choices to a VB.  it will take me awhile to check that out though. (am slow with vb).

for the first example (non regex) on at least 1 line i got an value error in the cell.  exact example of formula testing has:

=IF(A9>60,"9/1/2000",IF(A9>27,"1/15/2001",IF(OR(A9<12,A9>24),"2/1/2006",IF(A9<14,"6/1/2012",IF(A9<11.5,"8/1/2008",IF(OR(A9<16,1),"6/1/2013","1/1/2014"))))))

maxSTR = Mid(splt(i), c + 1, 100)   'max 1900 worked,  error:
'in sheet 1 line: value error,  calc'd line got error here this line: TYPE MISMATCH

==========

you wouldn't believe the amount of hours i spend on trying to research this stuff..  spent 10 or 12 hours on this for just yesterday.  i saw a site explaining regex, looked like its own program language - had to stop there.

thanks.

==========

for any who have a problem with the correct case setting showing up within a cell, for a udf, the following seems to work:

'FIX / RESET FUNCTION CASE:  capsxx() casexx() CAPSXX() CASEXX()
'rename function name eg: sameNAMEx to add "x", COMPILE VB (debug, compile; ignore error), REM x, COMPILE, SHEET: in one cell rename udf function with correct case & hit enter, done. (might think there could be some script for that).

==========

for others:  having a formula that depends on a main number or price in another column..  if that number changes by a factor of eg:  2, 5, 10 etc other columns become obsolete / the whole line will be in error.  this test will allow you to see that.  example test:

=IF((maxSTR(A9)>0)*(maxSTR(A9)*1.5<M9),"Error",0)

==========

having a hyperlink in header, to jump to the first instance of this example should work:  (pending,  trying to clear up errrors),  cntrl-shift-enter array formula, in cell:  X3  (row where this resides, column you want cursor to go), adjust columns & rows.

MATCH(TRUE,EXACT(\$AK\$861:\$AK\$2112,"Error"),0))),0,0))),SUMPRODUCT(--(EXACT(\$AK\$861:\$AK\$2112,"Error"))))

• Edited by Saturday, May 27, 2017 5:19 PM
Saturday, May 27, 2017 3:33 PM
• hi,  i had a chance to look the non-regex version over more.  i have limited vb skills & was not able to get the following to work for a couple of small problems.  thanks.

please pardon i did not think to include all versions of formula using (i do a different formula for each line).  with all the work doing on researching this i overlooked that /  wo thinking about it, i assumed "OR" was enough.

an example of a formula with both ">"  OR  "<".  i was not able to get all versions of the formula included, pardon - each line a different formula is entered.  (note:  max numbers seen might be 300, for the regex version, to cause a .pattern problem?  i see on some web pages how involved that can be;  is there an excel version requirement?).

=IF(A9>10,IF(A9>12,"Z","Y"),IF(A9<2,"A",IF(A9<4,"B","C")))    'MAX 12 for both > or < signs,  gets a Value Error

'1.  is there a way to inlcude a:  "Greater than zero" number of > or < in udf? so i do not need this full expression:

IF((maxSTR(AO647)>0)*(maxSTR(AO647)*3<FU647),  (else a false positive without IF > 0 portion)

'2.  how to modify if i have both ">" & "<" in the same formula?  i could not get an array to work.

Function maxSTR(rng As RANGE) As Double           'YES  Non-Regex Version  (Asadulla Javed 170527)
Dim splt As Variant, i As Long, c As Long, v As Variant
splt = split(rng.Formula, ",")

v = Array(">", "<")    ', "=")    'eg:  Set c = r.find(what:=v(i)

For i = LBound(splt) To UBound(splt)
'c = InStr(1, splt(i), v)                   'no
c = InStr(1, splt(i), ">")                  'orig

'If c = 0 Then c = InStr(1, splt(i), v)     'no
If c = 0 Then c = InStr(1, splt(i), "<")    'yes separate "=" not help, try array

If c > 0 Then
If Mid(splt(i), c + 1, 100) > maxSTR Then
maxSTR = Mid(splt(i), c + 1, 100)       'err: type mismatch: have eg's with both: > & <
End If
End If
Next i
End Function

• Edited by Sunday, May 28, 2017 3:44 AM
Sunday, May 28, 2017 12:18 AM
• You can use below.

Above contains good reference.

```Function maxSTRr(rng As Range) As Double

Dim RE As Object, MC As Variant, M As Object, D As Double
Dim S As String
Set RE = CreateObject("vbscript.regexp")

S = rng.Formula

With RE
.Global = True
.Pattern = "(>|<)([0-9]+)."

If RE.test(S) = True Then
Set MC = RE.Execute(S)
For Each M In MC
D = IIf(D > CDbl(M.SubMatches(1)), D, CDbl(M.SubMatches(1)))
Next M

End If
End With

maxSTRr = D

End Function
```

Best Regards,

Monday, May 29, 2017 11:14 AM
• hi,  thanks for the link, and the reply.  i still have not been able to the regex version to work (maybe an excel version problem), but have had some success with the NON regex version,  that i need some help with.

can i get help with changing Non Regex version, to include something more like an array? for testing both items simultaneously, in a formula?

i have a couple of other items / tricks i used to get  Non Rex to work.  part of it is using another udf called ISTRUE (udf for isnumber & greater than 0), and using 2 columns.  (temporary get past error in main column), with 2 items:

=IF(ISERROR(maxSTR(A9)),0,maxSTR(A9))    '2 columns to turn an error into 0.

IF((ISTRUE(A9))*((A9)<m9),"maxERR","")    'i can include a copy of istrue in my top post later, just a number>0 test

problem 1.  original formula i need to work with.  would like to reduce formula, to remove >0 1st half.. if can include that test right in the udf.

=IF((maxSTR(A9)>0)*(maxSTR(A9)*3<M9),"maxERR","")

is there a way to use (or change the udf) so i can get rid of the first variable for > 0, and incude that test inside the udf itself?  want to remove from formula:  (maxSTR(A9)>0)

i think i saw an example for adding a boolean TRUE FALSE to a UDF.  (i will put that in next post).

problem 2.  THE Preferred version using is the Non Regex Version.  can you help change this udf do include both ">" &  "<" in the same test formula eg:

=IF(M9>17,IF(M9>20,"Z","X"),IF(M9<9,"A",IF(M9<10,"B",IF(M9>13,"C","D"))))

==========

what working with so far,  need to fix:  1.  make udf not have an output if result is zero.  2  add array?  for both ">" & "<" items to be tested simultaneously.

Function maxSTRB(rng As RANGE) As Double           'almost,  Non-Regex Version  (Asadulla Javed 170527)
Dim splt As Variant, i As Long, c As Long, v As Variant
splt = split(rng.Formula, ",")

v = Array(">", "<")       ''<<  array not working yet,  is there a way to include something like this ??

For i = LBound(splt) To UBound(splt)
'c = InStr(1, splt(i), v)                   '<<  no,  array version not working
c = InStr(1, splt(i), ">")                  'orig

'If c = 0 Then c = InStr(1, splt(i), v)     '<<  no,  array version not working
If c = 0 Then c = InStr(1, splt(i), "<")    'yes, but need both > <
'If c = 0 Then GoTo GETOUT:    'see: yneg,  does not help get rid of extra variable in formula?

If c > 0 Then
If Mid(splt(i), c + 1, 100) > maxSTR Then 'somehow got max out of these 2 lines?
maxSTR = Mid(splt(i), c + 1, 100)
End If
End If
Next i

'GETOUT:
End Function

• Edited by Monday, May 29, 2017 7:27 PM
Monday, May 29, 2017 7:06 PM
• the TRUE FALSE example i have happens to be with the "ISTRUE" udf i mentioned above.  am only guessing that this may help get rid of false positive results.

Function isTRUE(ByRef X As RANGE) As Boolean   'as:  IF(ISTRUE(A1),1,0)
On Error Resume Next                          'for combine:  AND(if(isnumber(a9),a9>0)
isTRUE = False
If IsNumeric(X.Value) And X.Value <> vbNullString And X.Value > 0 Then isTRUE = True
If Not IsNumeric(X.Value) Then isTRUE = False
End Function    'istrue() ISTRUE()

• Edited by Monday, May 29, 2017 7:24 PM
Monday, May 29, 2017 7:22 PM
• Hi,

Can you mention what is requirement. Pls put them in order of your priority.

Best Regards,

Tuesday, May 30, 2017 7:52 AM
• update note:  to summarize, the udf seems to be working now, and if there is somethin i need to do to close this thread, let me know.  i added the explanations below to try to show how it might be working now/ i have been working on many areas for my workbook.  (not meant to continue the question).    thanks much for your help.

pardon:  somehow hit post key while i am writing this:  wait till finished please,  working now.  (Rcommend 1st tab key not be the SUBMIT button.

hi Mr Javed,  pardon late reply,  sometimes up nights - down for a period of time.  i think i have problem resolved.  i added some things for correct errors (as below).  but am not sure of what exactly makes both > and < characters work now.  (i don't see how that is accomplished, since had an error, i was giving my isTRUE udf eg thought aspects for:  TRUE - FALSE would help).

one thing i was trying to do is shorten up my formula, to get rid of the first variable for:  > 0.  i am not sure if that is possible, and / or for using true false eg above.  maybe not possible? but i wanted to change the formula (from / to):

=IF((maxSTR(A9)>0)*(maxSTR(A9)*3<M9),"maxERR","zzz")    'double variable test

=IF(maxSTR(A9)*3<M9,"maxERR","zzz")    'single variable test, if can put > 0 aspect inside the udf.  if not already part of that ?? :)  checking now.

thought there was a problem if i was using both variables:  ">"  and  "<" in the same formula,  to get an error.  this does not seem to be happening now.  Answer for that is:  NO, it is still a problem.  the following gets a false positive / "maxERR",  for:  ="C".  i am slow at vb,  but i can look at that  more.

i use a work around for name errors i receive for udf's (name resolution problem).  a copy of that is below.   not sure why it works now, but if i made some correction :)  please pardon taking up so much time. i had worked many hours on this though.

my "work around"?  for some name convention errors i have been receiving, where the name for a UDF would not resolve back the sheet & I would get a NAME ERROR  (if i am assessing it correctly).

the correction for that is (for a call & supporting vb):

selection.EntireRow.Calculate: call fixROW: fixROW: fixROW    'example of a call,  just calc or part of cut copy..

Sub xfixROWout() 'clear name errors with .formula = .formula
Dim rngErrors As RANGE, cellError As RANGE, S As RANGE, c As RANGE
Set S = selection.EntireRow.Cells
On Error Resume Next    'If no error cells then next code line errors
Set rngErrors = ActiveSheet.Rows(selection.row).SpecialCells(xlCellTypeFormulas, xlErrors).Cells   'works for rows
On Error GoTo 0         'Resume error trapping ASAP.

If Not rngErrors Is Nothing Then              'yes works here
For Each c In S    'gd
If IsError(c) And c.HasArray = False Then   'And Len(c.Formula) <= 256 Then     'LEN not seem to work here
c.Formula = c.Formula   'yes
End If
Next
Else
'MsgBox "yep1" & Space(10), vbQuestion
End If

application.EnableEvents = True     'EVENTS   (DON'T FORGET TO TURN A1 BACK ON: a1="." safety, all macs off)
End Sub

==========

i was not getting a name error for this (i forget error type),  but i had to use a separate column to isolate because i could not realize the IF(NOT(ISERROR() for syntax, to put ahead of this vb udf to use it in the same cell / column.  i was using / have removed this work column:

IF(ISERROR(maxSTR(A9)),0,maxSTR(A9))    'now use in main column:

=IF((maxSTR(A9)>0)*(maxSTR(A9)*3<M9),"maxERR","zzz")

if i did anything in your vb i don't realize..  it is:

Function maxSTR(rng As RANGE) As Double           'YES  Non-Regex Version  (Asadulla Javed 170527)
On Error GoTo 0: On Error GoTo GETOUT:        'clears error trapping, exit

Dim splt As Variant, i As Long, c As Long     ', v As Variant
splt = split(rng.Formula, ",")

'v = Array(">", "<")       ', "=")    'array not working yet,  eg:  Set c = r.find(what:=v(i)

For i = LBound(splt) To UBound(splt)
'c = InStr(1, splt(i), v)                   'no
c = InStr(1, splt(i), ">")                  'orig

'If c = 0 Then c = InStr(1, splt(i), v)     'no
If c = 0 Then c = InStr(1, splt(i), "<")    'yes, but need both > <
'If c = 0 Then c = InStr(1, splt(i), "=")    '(no effect on 1 variable eq with "=" sign)  'If c = 0 Then Exit Sub   'not allowed in a function or property
'> If c = 0 Then GoTo GETOUT:    'does not help get rid of extra variable in formula? for: =IF(AND(ISNUMBER(AG9),AG9>0,AG9*3<FT9),"maxERR","")

If c > 0 Then   '
If Mid(splt(i), c + 1, 100) > maxSTR Then 'gets max out of these 2 lines?
maxSTR = Mid(splt(i), c + 1, 100)       'max 1900 worked,  err: type mismatch: have eg's with both: > & <, turned goto off on (compile gd), off err during DL1x
End If
End If
Next i

'think now working for this:
'=IF((maxSTR(A9)>0)*(maxSTR(A9)*3<M9),"maxERR","zz")

GETOUT:
End Function

the proof i just performed is below.  Z9 value is:  14.  formula is:  =maxSTR(A9),  A9 egs are:   (for results):

=IF(Z9>13,IF(Z9>45,"Y","X"),IF(OR(Z9<7.5,0),"A",IF(OR(Z9<9,1),"B","C")))    X  45  for:  >

=IF(Z9>13,IF(Z9<45,"Y","X"),IF(OR(Z9<7.5,0),"A",IF(OR(Z9<9,1),"B","C")))    Y  45  for:  <

=IF(Z9>15,IF(Z9<45,"Y","X"),IF(OR(Z9<7.5,0),"A",IF(OR(Z9>60,1),"B","C")))    B  60  for:  >    OR(x,1) involved

=IF(Z9>15,IF(Z9<45,"Y","X"),IF(OR(Z9<7.5,0),"A",IF(OR(Z9<60,1),"B","C")))    B  60  for:  <

• Edited by Friday, June 2, 2017 10:36 PM
Friday, June 2, 2017 6:49 PM