1

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.

Improve this question
2
  • I guess I could use a modeless userform, titled "Please ignore this box" (a la my favorite sign "Keep off this sign"!). But does anyone have a quick answer as to how having the userform showing will interact with my (multifarious) events for combobox _Change's? Commented Jul 7, 2011 at 18:55
  • I guess I could move the comboboxes to a userform... . That's a bit more work, but if need be... Commented Jul 7, 2011 at 19:02

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

What I was trying to figure out was how to keep the global containers alive, in the background, once the first version of output has been created--without tying up the system or having any noticeable effects on the use of Excel otherwise, aside from this program.
the global containers will stay alive for the duration of the excel session or until you kill them. They won't have any effect on Excel unless they consume vast amounts of memory.
Sorry, Charles, I feel like I'm missing something obvious. Do you mean that there is a declaration that persists after the final End Sub statement? Say I read data into some large arrays. I then create an output table from those arrays. Then by my usual practice at the final End Sub, the arrays go away. Are you saying I could declare the arrays in such a way that they still persist? Or is there a command I could use to hold the program static, in a kind of stasis, latent but ready to operate on those arrays, like a holding pattern (but not consuming processor cycles)?
Just declare your arrays/containers as Public in the header of a module before the first Sub and they will persist as global objects until the Excel session ends or you specifically destroy them.
Sorry I forgot to mark this as accepted at the time. I had no idea globals had this handy characteristic. I got so excited, I ran off to implement the idea. Thanks.
|
2

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.

Improve this answer

5 Comments

Thanks, osknows. My first reactions are to your suggestions 1 and 6. First a little background: the transactions are option trades. Reading them takes a fair bit of processing, because they come from different sources with eg different sign conventions and units. And because some features have to be computed. I read each trade into an Option-Deal object. I then compile the deals into several different (overlapping) portfolios--trading desk, trading book (under a desk), underlying type, etc. The user selects a particular portfolio from all the combinations. A portfolio is much smaller than...
the set of trades underlying it, in memory consumption. Oh and Option-Portfolio is another object. The first thing that occurs to me is that maybe I could output the portfolio data to a sheet, and just read that instead of re-reading all the trades. The difficulty with that is creating a lot of code to export and import as text. (Would this be "serializing" an object in C#? Which presumably VBA can't do?) Aside from exporting and importing text (and maybe serializing), I don't know what else suggestion 4 would mean for this problem--?
Oh and as far as the filtering and reading go--I iterate through rows of the trade log one at a time, reading text, and then doing various processing in the code. The "filtering" right now is done primarily just by picking a key from a Dictionary that contains all the portfolio objects (where the key matches the fields the user chooses in the combo boxes). I mean to developing more filtering at the level where the Trade objects are added to the Portfolio objects. This would entail, while For/Each looping through the Dictionary of Deal objects, applying conditionals to the deal properties...
before adding to the Portfolio object. PS, in the first comment, I meant to say "reactions to suggestions 1 and 4" (not 6). Re #6--if it were up to me...
@RomnieEE2 - it's difficult to comment as we don't know the processing involved. However, over 1 minute seems a long time to wait. If things really take this long to calculate I would look at running an overnight batch job on all/most requested data, store in a database and have instantaneous look up through an interface.
0

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

Improve this answer

4 Comments

Keeping the VBA running is what I imagined doing. The suggestion of keeping something loaded is the kind of idea I thought I might try. Would there be a perceptible impact on usage of Excel, with the code "running" just by having something loaded but nothing else being processed?
AFAIK, let the code "running" consists in keeping over the screen the userform the user sees. That's why i suggested to store the data in the workbook itself to "free" the user from the macro
I see, thanks. As currently designed, the combo boxes that the user selects are on a worksheet. And so what I imagined was the user being able to use the worksheets normally, but then update the table with the data still in memory, at will.
sorry it does not work the way you expected. i hope you will find a way to make it work anyway

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.