• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

Excel VBA - Storing Data in Class Object's Array Variable

Hi Experts,

Can I please request for some guidance on template design. I've simplify the below scenario in order to capture the essence of my question.

Case Study

1.

There exists two workbooks namely, wbStaticData and wbPresentation.

2.

wbStaticData contains historical information.

3.

wbPresentation relies on wbStaticData for data and uses defined functions to do calculations as per business requirements.

4.

wbPresentation has a userform to present the final results.
My Approach

1.

With wbPresentation_Open event, I open the wbStaticData. Dump the entire data table into an array and store it in wbPresentation userform's array variable.

2.

Any required data  for calculation will call upon the userform's array.

3.

By doing it like this, I avoided using globals.
Anyone see anything wrong with this approach? Or should I create a class object to store the data and then hook it on to the userform (I didn't do this because I read somewhere that the userform is just the same as a class object)?

Any feedback is greatly appreciated.

Many thanks,
Rick
0
Ronniel Allan Castanito
Asked:
Ronniel Allan Castanito
  • 3
  • 3
1 Solution
 
RyanProject Engineer, ElectricalCommented:
General rules are that you want to store as minimal of data as short term as possible.  So you  define your variables as low level as you can, and have the scope as local as you can.  That's very general, and many patterns will override that for various reasons.

But certainly this array you're storing could be a large memory chunk, and you probably don't want to have multiple copied.  I'm not accustomed to user forms, but whether the values are stored there or in a global doesn't make any difference, except in whether you reference them directly or through the user form.
0
 
Ronniel Allan CastanitoIT ManagerAuthor Commented:
Thanks for your comment MrBullwinkle. I've seen codes that stores a recordset in a variable and from there they navigate through  the recordset. Whats the difference between that approach and the one I  just suggested?
0
 
RyanProject Engineer, ElectricalCommented:
Oh thats what you meant.

A recordset is kind of 2 parts. It has the functionality to read data from a source. It also can store that data in a cache, so that it doesn't have network traffic (possibly leading to slow program response=lag) every time you read or write data.  The cache can be a single record, a defined page (say 100records or 1MB worth), or get the whole thing. Usually that's all defined in the connection string, but lock type will have some effects.

I've never had much use for forcing it to do whatever was automatic, except when I used disconnected recordsets when my data was held open for hours at a time and my network stability wasn't very good. This was in Access, not Excel, but same reasons apply.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ronniel Allan CastanitoIT ManagerAuthor Commented:
Hmm... So say if I open up the wbStaticData, dump the data table into the array variable and then close the wbStaticData, wouldn't it be similar to creating a cache?

Another approach I was thinking was creating a temp workbook or worksheet to store a copy of the data table. Use that temp object to navigate through data table and if updating is required, just open up the Read/Write version and make the changes there. Synchronise the temp object once updated. Would this be better?

I just find working with arrays is a lot more efficient that's all. I'm not really sure about memory issues as I'm not very experience in that area. I never really had to build a template big enough to worry about it in the past.

To put things into perspective, my current template contains 3 data table around 600 rows long and 30 columns wide.
0
 
RyanProject Engineer, ElectricalCommented:
Your verb dump is kind of backwards. It's more of a load, but yes, if you load all the data from the WB into an array then you've cached it.  If you've got enough RAM, then its fine.You can manipulate the data the quickest that way, but you used up some time loading, when maybe you only wanted 1 record?  (just pointing out the pros/cons to the method).

Your 2nd method sounds awful, unless there was a special need for it, like you didn't want to currupt anything in your own workbook...which is pretty far fetched idea.  The only real reason I can think would be that this middle workbook had functions in itself that you were using, and this workbook was used by other workbooks, which is why those functions weren't just in your calling wb.

3x600x30 isn't all that big. It's big enough to notice speed issues if you have to cycle every cell, but storing that in memory isn't that much.  Eg, if they're all strings of length 5, then each letter is probably 2bytes, so 2*5*(3*600*30)=540kB ~ 1/2MB of RAM.  

Alternative methods would be to use use an actual query to read from the other table, to read/write your data, but again, your size isn't really so big you need to complicate things from what you have.

Also, ArrayLists are FAR more efficient when adding/removing data than Arrays, but less efficient for static data that's being randomly accessed.

There's a lot of different things at play to understand in order to make the absolute best choice, so unless you want to learn a lot of things, your biggest factor may be, "Whatever you're comfortable with and can manage".
0
 
Ronniel Allan CastanitoIT ManagerAuthor Commented:
Thanks very much for the guidance MrBullwinkle. I actually did think about using queries but like youself reached the same conclusion. Just want to keep it as simple as possible seeing its only meant to be a short term solution.

The long term goal is to move the entire thing onto access.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now