I have a function for splitting a string by it's first space (I know there may be other ways to do this) which seems to work fine:
Public Function SplitBySide(ByVal str As String) As String()
'Left hand number to go into element 1, right into element 2
Dim strArr(1 To 2) As String, i As Integer
str = Trim(str)
For i = 1 To Len(str)
If Mid(str, i, 1) = " " Then Exit For
Next i
strArr(1) = Mid(str, 1, i)
If Not (i >= Len(str)) Then
strArr(2) = Mid(str, i, (Len(str) - i))
End If
For i = 1 To 2
strArr(i) = Trim(strArr(i))
Next i
SplitBySide = strArr
End Function
When I call it from a test sub within the module it's in it works as expected:
Sub kjghkg()
Dim strArr() As String
strArr = SplitBySide(" 1234 567893247")
Debug.Print strArr(1), strArr(2)
End sub
(prints 1234, 567893247 )
However, when I try to call it from a select statement within a UserForm module I get an "Expected Array" Error. The select statement I use to call it is:
Private Function CaseCalls(ByVal a As Integer, ByRef xlRange As Excel.Range)
Dim xlCell As Excel.Range
Dim strArr() As String
Select Case a
...
Case 8
For Each xlCell In xlRange
If xlCell.Value = "" Then
xlCell.Delete (xlUp)
Else
strArr = SplitBySide(xlCell.Value) 'problem line
xlCell.Value = strArr(1)
xlCell.Offset(0, 1).Value = strArr(2)
End If
Next xlCell
...
End Select
End Function
I can only think of three major differences between the test sub and the function with the select case statement - 1. the test sub was in a normal class module, the select case function is in a userform, 2. the passed variable is the contents of an excel cell, rather than a string typed directly into the IDE, 3. the test sub does not call the SplitBySide function from within a select case statement.
Is there something fundamental stopping me from returning an array variable using the CaseCalls function, or have I just made a stupid mistake somewhere? Hopefully some can help me work out what I'm doing wrong!
Edit:
I have managed to bypass the problem by passing the strArr variable from the select case statement into a function that simply calls SplitBySide and fills the passed array with the results but doesn't explicitly return it to CaseCalls (since the array is passed by reference, I can still access these values from CaseCalls):
...
Case 8
For Each xlCell In xlRange
If xlCell.Value = "" Then
xlCell.Delete (xlUp)
Else
Call AvoidIt(strArr, xlCell.Value)
xlCell.Value = strArr(1)
xlCell.Offset(0, 1).Value = strArr(2)
End If
Next xlCell
...
Public Function AvoidIt(ByRef strArr() As String, ByVal str As String)
Dim strGh() As String
strGh = SplitBySide(str)
strArr(1) = strGh(1)
strArr(2) = strGh(2)
End Function
For some reason this works where the other attempt didn't. I think Racil Hilan may be right and I've somehow confused the system and it doesn't know where to find the function (there is only one function with this name, so I don't know why this would happen). I will have to do some tests
xlCell.Value
withCStr(xlCell.Value)
. – Ioannis Mar 8 at 12:45