Help Me - Array Failure

  • Thread starter Thread starter John T Ingato
  • Start date Start date
J

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
 
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
 
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 *
 
Back
Top