CODEWITHSUNDEEP
vbaexcel
RomnieEE2
RomnieEE2

Reputation: 99

VBA maintaining the program in memory

Sorry I don't know if this is something simple, or even where the problem fits in the greater scheme of programming.

So in my unsophisticated ways, my programs have always been of the scheme: 1. start program, 2. wait while program runs, 3. program is done and gone.

What I am doing now is creating a table from a long list of transactions (10,000s of). The table has several combo boxes for the user to select filters. Right now, every time the user changes a filter, the entire log is re-processed, which takes half a minute or a minute.

What I would rather do is have the trade log held in memory, or somehow latently but more immediately available. But not have the program "spinning" in the background. So the user could go about using Excel unaware that the program is ready in the background in case they want to update the table later, or not.

Does that make sense? If it can't be done in VBA, I'd still be curious how it would be done in another environment, say C#, if it could be. Thanks.

Upvotes: 1

Views: 3016

Answers (3)

Charles Williams
Charles Williams

Reputation: 23530

If the frequency of updates to the options trade is low enough you could separate reading and processing the option trades from the filtering process: Step 1 - Refresh - read the logs and process them, storing the results in global containers (arrays, collections, dictionaries, objects ...) Step 2 - User requests - show form - user chooses filters - show/store results extracted from the global containers.

Upvotes: 2

user688334
user688334

Reputation:

There are several options

  1. Firstly, is the code correctly structured? For example, do you really need to re-process everything or can a re-write be more efficient?
  2. If you cannot avoid resource intensive code, notify the user with a progress bar or message. Also consider the use of DoEvents which frees up the operating system so that Excel can process other events.
  3. DoEvents is slow and dirty. Even better look at this link DoEvents is slow!!! Here are faster methods
  4. Rewrite your code to work asynchronously. Create a class, a handler and deal with each transaction asynchronously.
  5. You could write some VBScript/Javascript and push the task out to run independently of Excel/VBA. Eg there's an example Here
  6. Don't use VBA :)

Edit: How are you filtering? If you're iterating through thousands of items in an array testing for criteria it can be very slow. Excel's Advanced Filter is very quick and could process hundreds of thousands of rows with multiple criteria quickly.

Upvotes: 2

JMax
JMax

Reputation: 26601

When a macro in Excel VBA runs, the user cannot use Excel anymore, running the VBA "stucks" the whole program.

Here are a few tips to find a workaround for your problem :

  • Keep the vba running : load the data a first time when launching the combobox and then display results to the user every time he asks for but keep a combobox loaded so that vba keeps its context and memory
  • Load the data in Excel Worksheet, even hidden and then use it when the user asks for some data
  • Give us more info on what you are doing, from where you are loading the data, how you can cache it, what is your current code, what you tried... so that we can help you more

Regards,

Max

Upvotes: 0

Related Questions