Help Me - Array Failure

[John T Ingato]

I am new to VB, coming from C. Perhaps had I stated with VB, I would be catching on sooner.

I am trying to fill an array of a user-defined data types, which as you can see, is basic customer info. I am trying to pull this information from another workbook.

I wrote the function to open the said workbook, copy its info into the array display the first five array member ( for Checking) and close the original file.

It all works fine until I return to the calling function, when apparently the array falls out of scope.

I defined the array outside of and procedure, so I thought it would be a global scope item.

Where should I put this declaration? Please I am going bald!!!

Any other suggetion would be welcome also.

Here is the code I have so far:


--------------------------------------------------------------------------------


Option Base 1

Public Type Stores

StoreNum As Integer
StoreName As String
Market As Integer
ServiceRep As String

End Type

Const Max = 60

Dim MyStores(Max) As Stores ' Here is what I thought was a Global Scoped Variable


--------------------------------------------------------------------------------

Sub LoadMyStores()

Dim i As Integer

For i = 1 To 60

If IsNull(Workbooks("replist.xls") _
.Worksheets("stores").Range("A" & i + 1)) Then Exit For


MyStores(i).StoreNum = Workbooks("replist.xls") _
.Worksheets("stores").Range("A" & i + 1)

MyStores(i).StoreName = Workbooks("replist.xls") _
.Worksheets("stores").Range("B" & i + 1)

MyStores(i).Market = Workbooks("replist.xls") _
.Worksheets("stores").Range("C" & i + 1)

MyStores(i).ServiceRep = Workbooks("replist.xls") _
.Worksheets("stores").Range("D" & i + 1)

Next i

For i = 1 To 5


MsgBox (MyStores(i).StoreNum & " " & MyStores(i).StoreName _
& ", in Market " & MyStores(i).Market & " is serviced by " & MyStores(i).ServiceRep)

Next i



End Sub


--------------------------------------------------------------------------------


Sub SetList()
'
'


Workbooks.Open Filename:="C:\Program Files\FieldTracker\replist.xls"
Application.WindowState = xlMinimized
LoadMyStores
Workbooks("replist.xls").Close

End Sub


--------------------------------------------------------------------------------


Sub Main()



Call SetList ' call to another function to initialize storelist array


End Sub

 

[Jean-Paul Viel]

Hi,



It's at the right place for a global variable but instead of Dim use Private, to use it only in that module, Public to make it available outside this module.


--
JP
(e-mail address removed)
http://www.solutionsvba.com


I am new to VB, coming from C. Perhaps had I stated with VB, I would be catching on sooner.

I am trying to fill an array of a user-defined data types, which as you can see, is basic customer info. I am trying to pull this information from another workbook.

I wrote the function to open the said workbook, copy its info into the array display the first five array member ( for Checking) and close the original file.

It all works fine until I return to the calling function, when apparently the array falls out of scope.

I defined the array outside of and procedure, so I thought it would be a global scope item.

Where should I put this declaration? Please I am going bald!!!

Any other suggetion would be welcome also.

Here is the code I have so far:


------------------------------------------------------------------------------


Option Base 1

Public Type Stores

StoreNum As Integer
StoreName As String
Market As Integer
ServiceRep As String

End Type

Const Max = 60

Dim MyStores(Max) As Stores ' Here is what I thought was a Global Scoped Variable


------------------------------------------------------------------------------

Sub LoadMyStores()

Dim i As Integer

For i = 1 To 60

If IsNull(Workbooks("replist.xls") _
.Worksheets("stores").Range("A" & i + 1)) Then Exit For


MyStores(i).StoreNum = Workbooks("replist.xls") _
.Worksheets("stores").Range("A" & i + 1)

MyStores(i).StoreName = Workbooks("replist.xls") _
.Worksheets("stores").Range("B" & i + 1)

MyStores(i).Market = Workbooks("replist.xls") _
.Worksheets("stores").Range("C" & i + 1)

MyStores(i).ServiceRep = Workbooks("replist.xls") _
.Worksheets("stores").Range("D" & i + 1)

Next i

For i = 1 To 5


MsgBox (MyStores(i).StoreNum & " " & MyStores(i).StoreName _
& ", in Market " & MyStores(i).Market & " is serviced by " & MyStores(i).ServiceRep)

Next i



End Sub


------------------------------------------------------------------------------


Sub SetList()
'
'


Workbooks.Open Filename:="C:\Program Files\FieldTracker\replist.xls"
Application.WindowState = xlMinimized
LoadMyStores
Workbooks("replist.xls").Close

End Sub


------------------------------------------------------------------------------


Sub Main()



Call SetList ' call to another function to initialize storelist array


End Sub

 

[Rob Bovey]

Hi John,

My next guess then is that your array is fine and it's your watch that's
set wrong. When you add the array as a watch expression, make sure that (All
Procedures) is selected in the Procedure dropdown of the Context section. If
you add a watch expression from a specific procedure, then by default, the
watch will only be defined while code executing within that procedure, even
if the expression you're watching is global.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *