Regex

Alessandro Giraldi · Feb 25, 2020

Hi,
How can i calculate this in Via?
2(2A2B)= 4A 4B
And
2(2A2B) = 8
Tk u

Deleted member 147267 · Feb 25, 2020

First, when you cross post (post the same issue in different forums) it's considered polite to provide a link to each other site in both of your threads.
Second, General Forum isn't really the place for this question given that you asked for a vba solution elsewhere.

You didn't really put forth much effort in either forum, but even less here. Post more than one example of what the data looks like along with the answers for that data, otherwise the answer you get (based on your other thread) might be that it's just 2 to the power of 3 (2 cubed) which = 8.

Deleted member 147267 · Feb 25, 2020

I do love a challenge though. Since you asked for a vba solution (maybe Regex would be better) this expression might do the trick:

Val(str) * Val(Mid(str, InStr(str, "(") + 1)) * Val(Mid(str, InStr(str, "(") + 1 + Len(CStr(Val(Mid(str, InStr(str, "(") + 1)))) + 1))

Alessandro Giraldi · Feb 25, 2020

Alessandro Giraldi said:

Val(str) * Val(Mid(str, InStr(str, "(") + 1)) * Val(Mid(str, InStr(str, "(") + 1 + Len(CStr(Val(Mid(str, InStr(str, "(") + 1)))) + 1))

yes this code is ok for this expression 2(2a2b), but it find only = 8 and not how many a and b ...anyway if i change the expression as 2(2a2b)2c2a2b4(2c2d) the result should be
a = 6
b = 6
c = 10
d = 8
total = 30
i can have a different expression any time so i need a function to calculate the expression.
somebody can help me?
tk u

bob fitz · Feb 25, 2020

Cross-posted: https://www.accessforums.net/showthread.php?t=79772

Please read: https://www.excelguru.ca/content.php?184

ebs17 · Feb 25, 2020

i can have a different expression any time

Codes are implementations of rules, and a rule will only be able to take into account what is known.
For "also different" you need a magician or a crystal ball.

In addition, the first rule in a database would be to work with atomic information from table fields.
This is much easier to calculate than with compound strings.

MajP · Feb 25, 2020

When you say Via calculation do you mean calculating a printed circuit board Via. If so there are javascripts available. See link

The CircuitCalculator.com Blog » PCB Via Calculator

You could build what is shown all the formulas are there

PCB Via for High Speed Design

referencedesigner.com

If you need help on building that, I can help. I do a lot of math focused databases.

Alessandro Giraldi · Feb 25, 2020

MajP said:
When you say Via calculation do you mean calculating a printed circuit board Via. If so there are javascripts available. See link

The CircuitCalculator.com Blog » PCB Via Calculator

You could build what is shown all the formulas are there

PCB Via for High Speed Design

PCB Via for High Speed Design

referencedesigner.com

If you need help on building that, I can help. I do a lot of math focused databases.

Hi,
Tk u for tour answer.
No, i mean vba code ti calculate One string every time could be differenti.
I work in textile Sector.
Some time the expression can be like 2(2a2b) and the result must be 4a and 4b and the Total of the expression Is 8 or some time could be 2(2a2b2c)2a5b6c3(3c3b)
In every time i have a string likes those i have ti calculate the sum of each letter and the Total without letter.
I don' t know of i have explaned well
Tk u

MajP · Feb 25, 2020

My fault. I did not figure out the typo. You put Via and I guess that was meant to be Vba. Can you explain the source of these equations? Where does this data come from? Maybe there is a better way. I think this would be a pretty hard algorithm to code if the possibilities for the letters are limitless and the form of the equations are limitless. Even then, my guess the best you could get would be a 80% solution.

jdraw · Feb 25, 2020

Your explanation is still not clear to me. You have given us some sort of function/algorithm but no context. It appears that some sort of arithmetic/calculaltion is required, but readers would need to know more about the a, b and the values they represent. In plain English what do a,b,c etc represent?

Alessandro Giraldi · Feb 25, 2020

Hi all,
i have tried to put img about my table and what i would like to do in the mask.
my system is access 2016 and the code i need to put under vba access.
the column is "Nota Ordito" and the column "Lettera" rappresent the value i have to give for each letter in the string "Nota Ordito".
i know is not easy, i think about regular expression but probabilly i am wrongin about it.
let me know if is more clear now or you need some more information.
tk u

Alessandro Giraldi · Feb 25, 2020

sorry this is the correct table

jdraw · Feb 25, 2020

"Lettera" in your graphic is alphabetic???
Do you mean that in the Nota Ordito (expression) that you replace/substitute each Lettera with the corresponding value in Ordito Nr. Numeric terms are used to multiply the substituted values.

Update: Just saw your latest jpg. Why are there multiple expressions Nota Ordito in some records of TBL1.

MajP · Feb 25, 2020

After rereading and looking at the data this looks doable. I will take a shot.

ebs17 · Feb 25, 2020

Since RegEx was in demand, here's a little gimmick to break down a expression so that detailed information can be written into a table so that calculations can be carried out on it.

Code:

Private pRegEx As Object

Public Property Get oRegEx(Optional Reset As Boolean) As Object
   If (pRegEx Is Nothing) Then Set pRegEx = CreateObject("Vbscript.Regexp")
   If Reset Then Set pRegEx = Nothing
   Set oRegEx = pRegEx
End Property

Public Function RegExMatchCollection(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As Object

   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .MultiLine = bMultiLine
      Set RegExMatchCollection = .Execute(SourceText)
   End With
End Function

Public Function RegExReplace(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      ByVal ReplaceText As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As String
 
   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .MultiLine = bMultiLine
      RegExReplace = .Replace(SourceText, ReplaceText)
   End With
End Function

Calling routine

Code:

Sub test_textil()
    Const cT1 = "2(2a2b2c)2a16b6c3(3c3b)"
    'Const cT2 = "2(2A2B)"
    Dim oMC As Object, oMC2 As Object
    Dim oM As Object, oM2 As Object
    Dim sF As String, lF As Long

    Set oMC = RegExMatchCollection(cT1, "((\d+\((\d+[a-z])+\))|(\d+[a-z])+)")
    For Each oM In oMC
        'Debug.Print oM.Value, oM.FirstIndex + 1, oM.Length
        sF = oM.Value
        sF = RegExReplace(sF, "(\d+)\(.+\)", "$1")
        'Debug.Print sF
        If InStr(oM.Value, "(") > 0 Then
            lF = Val(sF)
        Else
            lF = 1
        End If
        'Debug.Print lF
        Set oMC2 = RegExMatchCollection(oM.Value, "\d+[a-z]")
        For Each oM2 In oMC2
            Debug.Print lF, Val(oM2.Value), Replace(oM2.Value, Val(oM2.Value), "")
        Next
    Next
End Sub

Eberhard

Alessandro Giraldi · Feb 25, 2020

Hi,
How can i pass the result ti panel?

Alessandro Giraldi · Feb 25, 2020

Sorry

ebs17 said:

Since RegEx was in demand, here's a little gimmick to break down a expression so that detailed information can be written into a table so that calculations can be carried out on it.

Code:

Private pRegEx As Object

Public Property Get oRegEx(Optional Reset As Boolean) As Object
   If (pRegEx Is Nothing) Then Set pRegEx = CreateObject("Vbscript.Regexp")
   If Reset Then Set pRegEx = Nothing
   Set oRegEx = pRegEx
End Property

Public Function RegExMatchCollection(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As Object

   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .MultiLine = bMultiLine
      Set RegExMatchCollection = .Execute(SourceText)
   End With
End Function

Public Function RegExReplace(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      ByVal ReplaceText As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As String

   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .MultiLine = bMultiLine
      RegExReplace = .Replace(SourceText, ReplaceText)
   End With
End Function

Calling routine

Code:

Sub test_textil()
    Const cT1 = "2(2a2b2c)2a16b6c3(3c3b)"
    'Const cT2 = "2(2A2B)"
    Dim oMC As Object, oMC2 As Object
    Dim oM As Object, oM2 As Object
    Dim sF As String, lF As Long

    Set oMC = RegExMatchCollection(cT1, "((\d+\((\d+[a-z])+\))|(\d+[a-z])+)")
    For Each oM In oMC
        'Debug.Print oM.Value, oM.FirstIndex + 1, oM.Length
        sF = oM.Value
        sF = RegExReplace(sF, "(\d+)\(.+\)", "$1")
        'Debug.Print sF
        If InStr(oM.Value, "(") > 0 Then
            lF = Val(sF)
        Else
            lF = 1
        End If
        'Debug.Print lF
        Set oMC2 = RegExMatchCollection(oM.Value, "\d+[a-z]")
        For Each oM2 In oMC2
            Debug.Print lF, Val(oM2.Value), Replace(oM2.Value, Val(oM2.Value), "")
        Next
    Next
End Sub

Eberhard

Hi and tk u very much, can i ask how can help pass the result to label

ebs17 · Feb 25, 2020

I can't do anything with your fragmentary requests. So I say: RegEx cannot write in labels.

I had only tried to extract individual information from such data conglomerates in such a way that you could write it into a table and then continue to count on it.

You should request ready-made solutions from someone who is adequately rewarded and who knows the entire task. The information chunk by chunk is unbearable (for me).

Eberhard

MajP · Feb 25, 2020

Solution provided

Code:

Public Type t_Ordito
 A As Integer
 B As Integer
 C As Integer
 D As Integer
 E As Integer
 Total As Integer
End Type

Public Function GetOrdito(strOrdito As String) As t_Ordito
  Dim Ordito As t_Ordito
  Dim character As String
  Dim strnumberBefore As String
  Dim numberBefore As Integer
  Dim distributeNumber As Integer
  Dim distribute As Boolean
  Dim i As Integer
  distributeNumber = 1
  For i = 1 To Len(strOrdito)
    character = Mid(strOrdito, i, 1)
    Debug.Print "Char in beginning" & character
    If IsNumeric(character) Then
      strnumberBefore = strnumberBefore & character
      numberBefore = CInt(strnumberBefore)
      
    Else
    If distribute = False Then
     distributeNumber = 1
    Else
     distributeNumber = numberBefore
    End If
   ' Debug.Print "Char " & character & "Distribute " & distribute & " number " & distributeNumber & " Number before " & numberBefore
    Select Case character
    
     Case "A"
      Ordito.A = Ordito.A + numberBefore * distributeNumber
      Debug.Print Ordito.A & " A"
      strnumberBefore = ""
     Case "B"
      Ordito.B = Ordito.B + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "C"
      Ordito.C = Ordito.C + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "D"
      Ordito.D = Ordito.D + numberBefore * distributeNumber
       strnumberBefore = ""
     Case "E"
      Ordito.E = Ordito.E + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "("
      distribute = True
      distribute = numberBefore
      strnumberBefore = ""
     Case ")"
       distribute = False
       distributeNumber = 1
       strnumberBefore = ""
      End Select
    End If
  Next i
  Ordito.Total = Ordito.A + Ordito.B + Ordito.C + Ordito.D + Ordito.E
  GetOrdito = Ordito
 
 
End Function

Code:

Public Sub TestOrditio()
  Dim Ordito As t_Ordito
  Ordito = GetOrdito("2(2a2b2c)2a5b6c3(3c3b)")
  Debug.Print "A = " & Ordito.A
  Debug.Print "B = " & Ordito.B
  Debug.Print "C = " & Ordito.C
  Debug.Print "D = " & Ordito.D
  Debug.Print "E = " & Ordito.E
  Debug.Print "Totol = "; Ordito.Total
End Sub

Output

Code:

A = 6
B = 18
C = 19
D = 0
E = 0
Total =  43

It was actually easier than I thought. You would then have to either do the insert query or you could wrap in individual functions for sql. like

Code:

Public Function GetOrditoA(strOrdito As String) As Integer
  Dim ordito As t_Ordito
  ordito = GetOrdito(strOrdito)
  GetOrditoA = ordito.A
End Function

Alessandro Giraldi · Feb 26, 2020

MajP said:

Solution provided

Code:

Public Type t_Ordito
A As Integer
B As Integer
C As Integer
D As Integer
E As Integer
Total As Integer
End Type

Public Function GetOrdito(strOrdito As String) As t_Ordito
  Dim Ordito As t_Ordito
  Dim character As String
  Dim strnumberBefore As String
  Dim numberBefore As Integer
  Dim distributeNumber As Integer
  Dim distribute As Boolean
  Dim i As Integer
  distributeNumber = 1
  For i = 1 To Len(strOrdito)
    character = Mid(strOrdito, i, 1)
    Debug.Print "Char in beginning" & character
    If IsNumeric(character) Then
      strnumberBefore = strnumberBefore & character
      numberBefore = CInt(strnumberBefore)
     
    Else
    If distribute = False Then
     distributeNumber = 1
    Else
     distributeNumber = numberBefore
    End If
   ' Debug.Print "Char " & character & "Distribute " & distribute & " number " & distributeNumber & " Number before " & numberBefore
    Select Case character
   
     Case "A"
      Ordito.A = Ordito.A + numberBefore * distributeNumber
      Debug.Print Ordito.A & " A"
      strnumberBefore = ""
     Case "B"
      Ordito.B = Ordito.B + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "C"
      Ordito.C = Ordito.C + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "D"
      Ordito.D = Ordito.D + numberBefore * distributeNumber
       strnumberBefore = ""
     Case "E"
      Ordito.E = Ordito.E + numberBefore * distributeNumber
      strnumberBefore = ""
     Case "("
      distribute = True
      distribute = numberBefore
      strnumberBefore = ""
     Case ")"
       distribute = False
       distributeNumber = 1
       strnumberBefore = ""
      End Select
    End If
  Next i
  Ordito.Total = Ordito.A + Ordito.B + Ordito.C + Ordito.D + Ordito.E
  GetOrdito = Ordito


End Function

Code:

Public Sub TestOrditio()
  Dim Ordito As t_Ordito
  Ordito = GetOrdito("2(2a2b2c)2a5b6c3(3c3b)")
  Debug.Print "A = " & Ordito.A
  Debug.Print "B = " & Ordito.B
  Debug.Print "C = " & Ordito.C
  Debug.Print "D = " & Ordito.D
  Debug.Print "E = " & Ordito.E
  Debug.Print "Totol = "; Ordito.Total
End Sub

Output

Code:

A = 6
B = 18
C = 19
D = 0
E = 0
Total =  43

It was actually easier than I thought. You would then have to either do the insert query or you could wrap in individual functions for sql. like

Code:

Public Function GetOrditoA(strOrdito As String) As Integer
  Dim ordito As t_Ordito
  ordito = GetOrdito(strOrdito)
  GetOrditoA = ordito.A
End Function

Tk u very much,

this code is ok but if i have this case (ex. 3(2a2b) doesn' t work, the result is A=4 and B=4.
do you know why?

tk u

Regex

New member

Deleted member 147267

Guest

Deleted member 147267

Guest

New member

AWF VIP

Well-known member

You've got your good things, and you've got mine.

New member

You've got your good things, and you've got mine.

Super Moderator

New member

Attachments

New member

Attachments

Super Moderator

Attachments

You've got your good things, and you've got mine.

Well-known member

New member

New member

Well-known member

You've got your good things, and you've got mine.

New member

Similar threads

Users who are viewing this thread