You need to make asSplit a public variable. Static only works when you use a
complier.

Public asSplit() As String
Sub Test()
Dim a As Variant

'The first call works OK
a = fnFruitArray
MsgBox a(0)

'But the second call doesn't return an initialised array
a = fnFruitArray
MsgBox a(0)
End Sub

Function fnFruitArray() As String()
Static bBeenHere As Boolean

If bBeenHere = False Then
asSplit = Split("apple,orange,banana", ",")
bBeenHere = True
End If

fnFruitArray = asSplit
End Function


" wrote:

I have come across an unexpected problem. I thought that the array
asSplit() in fnFruitArray would remember its contents between calls
but it does not. I must have missed something elementary here - is
this really how VBA is supposed to work?

Thanks,
Ture Magnusson

Sub Test()
Dim a As Variant

'The first call works OK
a = fnFruitArray
MsgBox a(0)

'But the second call doesn't return an initialised array
a = fnFruitArray
MsgBox a(0)
End Sub

Function fnFruitArray() As String()
Static bBeenHere As Boolean
Static asSplit() As String

If bBeenHere = False Then
asSplit = Split("apple,orange,banana", ",")
bBeenHere = True
End If

fnFruitArray = asSplit
End Function