Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array declared with the Static keyword clears itself between calls
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array declared with the Static keyword clears itself between calls
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array declared with the Static keyword clears itself betweencalls
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array declared with the Static keyword clears itself between c
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. " 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array declared with the Static keyword clears itself between calls
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array declared with the Static keyword clears itself between calls
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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
static array | Excel Programming | |||
Load a static array at procedure level | Excel Programming | |||
Lookup from Static List/Array | Excel Worksheet Functions | |||
keeping a sum array static | Excel Worksheet Functions | |||
How to reference a public array (declared in module) from a proced | Excel Programming |