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

Posted on 2012-09-10
Last Modified: 2012-09-12
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


There exists two workbooks namely, wbStaticData and wbPresentation.


wbStaticData contains historical information.


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


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


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.


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


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,
Question by:RiCzN
    LVL 13

    Expert Comment

    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.

    Author Comment

    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?
    LVL 13

    Expert Comment

    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.

    Author Comment

    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.
    LVL 13

    Accepted Solution

    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".

    Author Closing Comment

    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.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now