none
VB: .pattern problem, Need a UDF to find a MAX Number, within a Formula RRS feed

  • 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 Davexx Friday, May 26, 2017 3:01 AM
    Friday, May 26, 2017 2:29 AM

Answers

  • 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,
    Asadulla Javed,
    Jadavpore & Asansol

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

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,
    Asadulla Javed,
    Jadavpore & Asansol

    • Marked as answer by Davexx Saturday, May 27, 2017 3:23 PM
    Saturday, May 27, 2017 8:55 AM
    Answerer
  • 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.

    and links:  https://en.wikibooks.org/wiki/Visual_Basic/Regular_Expressions

    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.

    =HYPERLINK(IF(AND(ISNA(INDEX(ROW($AK$861:$AK$2112)-ROW($AK$861),
    MATCH(TRUE,EXACT($AK$861:$AK$2112,"Error"),0)))),"","#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$X3),"$",""),ROW(),"")&INDEX(ROW($AK$861:$AK$2112),
    MATCH(TRUE,EXACT($AK$861:$AK$2112,"Error"),0))),0,0))),SUMPRODUCT(--(EXACT($AK$861:$AK$2112,"Error"))))








    • Edited by Davexx 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 Davexx Sunday, May 28, 2017 3:44 AM
    Sunday, May 28, 2017 12:18 AM
  • You can use below.

    Visit http://www.regular-expressions.info/

    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,
    Asadulla Javed,
    Jadavpore & Asansol

    Monday, May 29, 2017 11:14 AM
    Answerer
  • 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 Davexx 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 Davexx 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.

    And Unmark the thread.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Tuesday, May 30, 2017 7:52 AM
    Answerer
  • 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 Davexx Friday, June 2, 2017 10:36 PM
    Friday, June 2, 2017 6:49 PM