VBA: Getting value/string between/in square brackets

Question

so I have found a great answer on here showing how to get the values or strings between brackets (Get the value between the brackets) The code works perfectly, but when square brackets are involved, as in the following code, it gives me an error which I have been trying to fix for this whole day. I cannot seem to figure out what my problem is. The error notification I get is: "Run-time error '5': Invalid procedure call or argument"

For i = 6 To Worksheets(FileName).UsedRange.Columns.Count 

    kursName = Worksheets(FileName).Cells(1, i).Value

    klammerAuf = InStr(kursName, "[")
    klammerZu = InStr(kursName, "]")
    Cells(3, i) = Mid(kursName, klammerAuf + 1, klammerZu - klammerAuf - 1)
Next i

`Cells(1, i)` must have `]` otherwise `klammerZu` is zero and `klammerZu - klammerAuf - 1` will be negative. Wrap in `If klammerZu > klammerAuf ` Then/End If` block — CDP1802, Feb 20 '22 at 17:30
`If InStr(kursName, "[") = 0 Then MsgBox "no []"` you don't need error handling, you need to test the result. — KL-1, Feb 20 '22 at 17:33

score 0 · Answer 1 · answered Feb 20 '22 at 17:30

Okay, I might've found the solution. The selected cell didn't have a value with square brackets. The problem was that I didn't have any handling for errors. I'll leave this question for others who might run into the same error in the future. Man, I feel dumb. I'll take any propositions for error handling.

score 0 · Answer 2 · answered Feb 20 '22 at 17:33

Try this:

Function GetTextWithinBrackets(kursName As String, left As String, right As String)
    klammerAuf = InStr(kursName, left)
    klammerZu = InStr(klammerAuf + 1, kursName, right) ' start searching for the closing bracket after the opening one
    If klammerAuf * klammerZu Then ' check if both brackets were found
        GetTextWithinBrackets = Mid(kursName, klammerAuf + 1, klammerZu - klammerAuf - 1)
    Else
        GetTextWithinBrackets = "No " & left & " or " & right & " in the text"
    End If
End Function

Sub test()
    Debug.Print GetTextWithinBrackets("jkdsf]]]klsjc[=the text within brackets=]asd[[aw]]sd", "[", "]")
    Debug.Print GetTextWithinBrackets("872639281(=the text within brackets=)slsx(sladmlsa)", "(", ")")
    Debug.Print GetTextWithinBrackets("872639281=the text within brackets=]slsx(sladmlsa)", "[", "]")
End Sub

Outputs:

=the text within brackets=
=the text within brackets=
No [ or ] in the text

Thanks a lot for your input! I wonder if there is a way to get the last string within brackets? As I have Strings like this a (b) (c). The only condition I could think of is reading from right to left or always getting the last. — Bilal Abdou-Karim, Feb 21 '22 at 15:21

VBA: Getting value/string between/in square brackets

2 Answers2