Arrays in VB6 specifically for Excel

T

TANSTAAFL

I could do this in C or C++ but unfortunately I am working on a time
constrained project and I am crying uncle early.

In C I would created a array and accessed it with pointer. The array is
48 rows by 4 columns.

In VB I have written a function that I will be using about 45,000 times
in a large spreadsheet. Currently I read the values from a spreadsheet
one time each time the function is called, yuch!

I would like to read that once when the spreadsheet is loaded, the data
NEVER changes, and access it from the function, which I think will
speed up the recalculation. Any help would be appreciated.

Thanks Dave
 
S

Steve Rindsberg

Tanstaafl said:
I could do this in C or C++ but unfortunately I am working on a time
constrained project and I am crying uncle early.

In C I would created a array and accessed it with pointer. The array is
48 rows by 4 columns.

In VB I have written a function that I will be using about 45,000 times
in a large spreadsheet. Currently I read the values from a spreadsheet
one time each time the function is called, yuch!

I would like to read that once when the spreadsheet is loaded, the data
NEVER changes, and access it from the function, which I think will
speed up the recalculation. Any help would be appreciated.
Click to expand...

What exactly do you want to do?

Is there some reason you can't read the Excel data into an array just as you
would in C/C++?
 
T

TANSTAAFL

I really don't know how to use VB. I spent a several hours yesterday
reading the documents and decided that setting the data into 48 rows
and 4 columns in a spread sheet and then reading that would work why I
tried to figure out how to create a data structure I could use.
Unfortunately the spreadsheet is much larger than I first anticipated
and recalcs now take 10 minutes on a high end machine.

Below is a snippet of the table

1 1/27/1999 1/27/1999 0.1200
2 1/28/1999 3/21/1999 0.1270
3 3/22/1999 4/19/1999 0.1480
4 4/20/1999 4/20/1999 0.1630
5 4/21/1999 4/25/1999 0.1770
6 4/26/1999 5/16/1999 0.1900
7 5/17/1999 8/10/1999 0.2020
8 8/11/1999 10/20/1999 0.2150
9 10/21/1999 1/2/2000 0.2210
10 1/3/2000 1/18/2000 0.2290


It would to me that there is an easy why to create a structure that is
loaded when the spreadsheet is that I can look up the value I need from
VB in Excel.

Dave
 
S

Steve Rindsberg

Tanstaafl said:
I really don't know how to use VB.
Click to expand...

OK, fair enough. I'd read up on Arrays in the help file or whatever other
reference you're using.

It's possible to declare arrays that can be re-sized on the fly if you don't
know how much data you're going to deal with but since your data will be
constant, you could pick an arbitrary value a bit higher than what you really
need then in your code test for "" entries, so:

Dim aExcelData(1 to 4000,1 to 3) as String
Dim x as lRowCounter

' You already have it pulling data from Excel
' Modify the routine you're using to look more like so:
For x = 1 to [howevermanyrows]
aExcelData(x,1) = [Row x, col A data ... your date]
aExcelData(x,2) = [Row x, col B data ... the second date]
aExcelData(x,3) = [Row x, col C data ... the final value in your set]
Next

To get the 8th row value:

' make sure it's not blank
If len(aExcelData(8,1)) > 0 then
Debug.Print aExcelData(8,3)
' and use Cdbl(aExcelData(8,3)) to use it as a double/numeric
' rather than as a string
End if

Smarter folks than I will no doubt have better ways of doing this; it strikes
me that two arrays (one for the two strings, another for the numeric) might be
more efficient, for example.

I spent a several hours yesterday
 
G

George Nicholson

Well, if the data is in a contiguous range of cells in a spreadsheet, it is
already in an array. Why not leave it there?
'****************
Dim rng as Range

' Assuming Row 1 is column headers
Set rng = ThisWorkbook.Worksheets("SomeName").Range("A2:D49")

' Cells usage: Range.Cells(Row, Column)
With rng
w = .Cells(1,1)
x = .Cells(1,2)
y = .Cells(1,3)
z = .Cells(1,4)
End With
'**********************
Would read your first row of data (A2:D2).

As long as you can declare/create a single Range object to access, I'm not
sure there would be that much of a performance benefit over reading the
contents of that range into an array. (Creating a class and storing elements
in a Collection might be a different story, depending on how you are
accessing the stored data.)


HTH,
 
R

Raymond Cruz

Maybe I'm wrong but I think Dave and the people who responded aren't
communicating about the same problem. I'm keying in on the idea that Dave
wants to read data into a global, internal array once and access it many
times, like a cache. I also can very much identify with the frustration a C
programmer faces bootstrapping himself into VBA.

Dave - if I'm close this is what you need to do.

Open the VB Editor and in the Project tree at the upper left find
ThisWorkbook and right click it. Select View Code from the popup. The
empty code window that appears will have two drop down lists. Select
Workbook from the left list and an empty Workbook_Open() sub will appear.
Select SheetActivate from the right list and an empty
Workbook_SheetActivate() sub will appear. I suggest you create a 3rd sub
named "worker" for example. Then I suggest you fill in code something like
this:

Public globalData(48, 4) As Integer
Private Sub Workbook_Open()
worker ActiveSheet
End Sub
Private Sub Workbook_SheetActivate(ByVal sh As Object)
worker sh
End Sub
Private Sub worker(ByVal sh As Object)
Rem Populate globalData here from worksheet info, e.g
Rem statements like globalData(x, y) = sh.Cells(u, v)
End Sub

Afterwards you can write as much code as you wish that can access the data
in the globalData array without having to read from the worksheet again.
The worker sub will run at startup and any time thereafter that you switch
to a new sheet. This may be even more than you need if your workbook has
only one worksheet.

RC
 
S

Steve Rindsberg

Maybe I'm wrong but I think Dave and the people who responded aren't
communicating about the same problem. I'm keying in on the idea that Dave
wants to read data into a global, internal array once and access it many
times, like a cache.
Click to expand...

That's what I was suggesting as well, but note that Dave mentioned coding in
VB6; your suggestions and some of the others seem to assume that he's working
inside Excel.

We'll have to let Dave sort out the bits that apply, I guess.



I also can very much identify with the frustration a C
 
Top