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

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


"(E-Mail Removed)" 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
>

Thank you for your reply, Joel.

However, I am already aware that I could handle this with a module
level or global variable.
And I don't really understand what you mean with "Static only works
when you use a complier".
I have declared the bBeenHere variable as Static and it retains its
value between calls, just as it should.
So what is the reason why the asSplit() array doesn't remember its
contents? Is this behavious documented?

VBA code is not compied code. So it doesn't assign a varaible until it is
used. You may get the results you want by moving the declaration from
function to the Sub. When you compile code the Linker takes all the Static
variables and puts them into global memory and then checks all the functtions
and subroutines that use the variables to see which one are global and which
are loc. VBA doesn't do all the checking.

"(E-Mail Removed)" wrote:

> Thank you for your reply, Joel.
>
> However, I am already aware that I could handle this with a module
> level or global variable.
> And I don't really understand what you mean with "Static only works
> when you use a complier".
> I have declared the bBeenHere variable as Static and it retains its
> value between calls, just as it should.
> So what is the reason why the asSplit() array doesn't remember its
> contents? Is this behavious documented?
>

If I change it to:
Static asSplit As Variant

It worked fine.

(E-Mail Removed) 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

--

Dave Peterson

ps. I think it's the split function and the "dim asSplit() as String" that's
doing the damage.

I could step through this code with watches on aaa and see that it keeps its
values.

Option Explicit

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 aaa() As String
Static asSplit() As String

If bBeenHere = False Then
ReDim aaa(1 To 5)
aaa(1) = "asdf"
asSplit = Split("apple,orange,banana", ",")
bBeenHere = True
End If

fnFruitArray = asSplit
End Function


Dave Peterson wrote:
>
> If I change it to:
> Static asSplit As Variant
>
> It worked fine.
>
> (E-Mail Removed) 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
>
> --
>
> Dave Peterson

--

Dave Peterson