VBA code for an array formula returns an error

Question

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

Split is an existing function name for starters.Use a different name. And you will want error handling if inputstring does not have two ' — QHarr
– QHarr, Commented Aug 23, 2018 at 18:29
@Ibo it's defined in the VBA.Strings module, it can absolutely be shadowed by user code... which indeed isn't something I'd recommend doing, but it's completely legal. — Mathieu Guindon
– Mathieu Guindon, Commented Aug 23, 2018 at 18:48

QHarr · Accepted Answer · 2018-08-23 19:58:49Z

1

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.

edited Aug 23, 2018 at 19:58

answered Aug 23, 2018 at 18:45

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Mathieu Guindon Over a year ago

I agree with Option Base being frowned upon, so omitting it is good - but declaring explicit upper and lower bounds for statically declared arrays is an even better idea: Dim P(1 To 2) As Variant, or Dim P(0 To 1) As Variant leaves no room for headscratching... or for Option Base to mess anything up.

QHarr Over a year ago

You are absolutely right. I knew I was in for it when I saw you comment. :-)

Comintern Over a year ago

And if you're using statically declared arrays you probably shouldn't be using LBound or UBound either. I'd bound them with constants and use those instead.

QHarr Over a year ago

@Comintern Help me out please. How should I reflect that in the above then? Why would you avoid LBound and UBound? And what would binding with constants look like? I think you guys could answer this a whole lot better than me and bring more benefit overall.

Comintern Over a year ago

Marginally, although it's mainly a readability thing. My personal view is that it makes no sense to call a function to determine what is for all intents and purposes deterministically a constant.

|

Collectives™ on Stack Overflow

VBA code for an array formula returns an error

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related