CODEWITHSUNDEEP
excelvba
OAltyn
OAltyn

Reputation: 21

VBA code for an array formula returns an error

I wrote a code to split a text into two pieces. Values are made up letters, special signs, and number. I checked every line but function does not work, excel returns "That function is invalid" Would you please tell me where my mistake is?

Option Explicit
Option Base 1
Function Split(s As String) As Variant
Dim firstquote As String, secondquote As String
Dim P(2) As Variant

firstquote = InStr(1, s, "'")
secondquote = InStr(firstquote + 1, s, "'")

P(1) = Trim(InStr(1, s, "="))
P(2) = Mid(s, firstquote + 1, secondquote - firstquote - 1)
Split = P

End Function

Upvotes: 1

Views: 127

Answers (1)

QHarr
QHarr

Reputation: 84465

Bearing in mind my two points above:

1) Split is an existing function so name your function differently;

2) You need a test that there are two "'" present

Also, some people frown on unneccesary use of Option Base 1.

The below, whilst not ideal (I think you could probably just use the existing Split function and better naming is required) does show you the existing Split function in action, and also the use of typed functions for working with Strings (e.g. Mid$). These are more efficient I believe. If you have more than 2 ' present you will only be returning the first two segements.

As per @MathieuGuindon (thanks)- declaring lower and ubound for static arrays is also good practice.

Here is a slight re-write:

Option Explicit
Public Sub test()
    Dim tests(), i As Long
    tests = Array("ng", "ng'ng2'ng2")
    For i = LBound(tests) To UBound(tests)
        If IsError(myCustomSplit(tests(i))) Then
            Debug.Print "Not enough ' present"
        Else
            Debug.Print myCustomSplit(tests(i))(1)
        End If
    Next i
End Sub
Public Function myCustomSplit(ByVal s As String) As Variant
    Dim firstquote As String, secondquote As String
    Dim P(0 To 1) As Variant

    If UBound(Split(s, "'")) >= 2 Then
        firstquote = InStr(1, s, "'")
        secondquote = InStr(firstquote + 1, s, "'")

        P(0) = Trim$(InStr(1, s, "="))
        P(1) = Mid$(s, firstquote + 1, secondquote - firstquote - 1)
        myCustomSplit = P
    Else
        myCustomSplit = CVErr(xlErrNA)
    End If
End Function

As comments may disappear I will paraphrase the other helpful comments that were given here. Thanks to all:

@ComIntern: If you're using statically declared arrays you probably shouldn't be using LBound or UBound either. Bind them with constants and use those instead. Something like Const TOP_ELEMENT As Long = 1, then Dim foo(0 To TOP_ELEMENT) As String and For idx = 0 To TOP_ELEMENT. More about readability but marginally faster. No need for a function call to determine what is a known constant.

@JohnnyL: To ensure that your array is always has 0-th lower bound, use VBA.Array instead of just Array. Even if you have Option Base 1, the array's lower bound will be still 0.

Upvotes: 1

Related Questions