Is this program as efficient as it could be?

Hmm Overbet,

We met before and then you posted a specific question we could help you with.

This time you ask us for a code review for several modules.

Sure the code can be optimized, I even see possible errors, but these kind of questions mainly remain unanswered.

It's better to specify what you want the code to do step by step and even post several threads step by step.

Now you ask us to look, but what are we looking at?

Without making a big deal about how these two ranges overlap, this can be simplified:

Please Login or Register  to view this content.

Without some understanding of what's going, mentioned already above by RW, then I can't see how this syntax is better than this:

Please Login or Register  to view this content.

...since it appears the formatting is the same in the column anyway.

========
Same idea, this can be simplified to remove the unnecessary "selecting":

Please Login or Register  to view this content.

===========
This long macro appears to evaluate the active cell in row 12, if it is greater than zero, then do some actions...it can be simplified...all of this:

Please Login or Register  to view this content.

...down to this I think:

Please Login or Register  to view this content.

What I saw was this

Please Login or Register  to view this content.

This can be replaced with

Please Login or Register  to view this content.

... and this one

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

hi all,

Ricardo,
I suggested to Overbet that he put the code up for comment - so any fault for trying to shortcut the system (specific questions) sits with me. From looking it his previous code (perhaps the same thread you've seen?), I thought there were some things that we could help with in terms of general learning & then possible code overhaul (eg from cell looping to in memory array processing*).

Overbet,
JB & Ricardo have given you some good suggestions so far. Are you able to make these changes, review your code for & incorporate With statements (eg "With Activecell") anywhere where the same object is acted on repeatedly, and then repost a file with the shortened code?
As Ricardo mentioned the intent of the code is important too, so where you can, try including comments within the shortened code that explain the intent. This may help us identify different/quicker approaches such as in memory array processing (see below links for some knowledgeable comments).

*These may be a bit of a leap but they will expand your knowledge!
http://www.avdf.com/apr98/art_ot003.html (perhaps some is outdated now but see concept of Tip 1)
http://www.dailydoseofexcel.com/arch...nge-using-vba/
http://www.dailydoseofexcel.com/arch...ays-explained/

hth
Rob

howdy,
I've had a play with the code (see attached) & I'll respond to your questions in the last post tomorrow - fingers crossed for the testing!

hth
Rob

I asked someone in my office who is more familiar with vba than i am and he is also familiar with the rediplus api that we are interacting with and I was told that he does not think an array would help. He asked if you were suggesting that I pull in 10 stock prices at once and submit orders for them at once? He then said we can not pull in price feed in array so he didnt think it would work. He also said he didnt think that was the bottle neck. Does that make sense? Thanks Jason

Hi Jason,

I re-read the array links as well, there are some things that still trip me up each time I use them!
Using VBA arrays (Variant arrays, in this case to make it easier to copy from the sheet to VBE & vv) should be quicker than processing cell by cell which means the macro will take less time to run when asked (while constantly updating data).
I may get my terminology wrong here, but I'll have a go...

Originally Posted by overbet

I have another program that I was told has an array in it but the problem with that program is that it only can handle reading data from about 50 symbols at once where as the other programs you have seen of mine can handle 600 symbols at once.

What does "at once" mean?
I imagine there are other issues in the file if it is only able to manage 50 symbols. An array processed in memory should be cope, but it does depend what it's doing. In the files in this thread, I have used arrays to "hold" the information from the spreadsheet in memory, so the macro doesn't have to find them from the sheet one by one.
How big is the file?
Is it slow to recalculate?

Originally Posted by overbet

The 2nd attachment I was told uses an array to pull in quotes. Is this the same array you are referring to or something different? Thanks Jason

Ummm... I'm not sure, I think the array in the second attachment must have been inside the "redilink" application...?

I ran out of steam before posting the 2 files which is why there are some asterisked sections in the "redilink" section of the OPG Test file.

Let us know if you have any questions...

A good way to help figure out the problems is to bring up the Locals window in the VBE & step through the code a line at a time using [F8] (maybe put "Stop" as the first line in each macro). You can even use a Watch window to tell you when something changes.

hth
Rob

howdy,

hmmm...
The only other thing I can think of that may play a part in the slowdown & could possibly be overcome is to turn off the calculation etc (lots of others too) during the macro, which is fine as long as the macro doesn't rely on calculated values.
Check out RoyUk's example in post five of http://www.excelforum.com/1867848-post5.html

btw, here's another link for some light reading (accelerated learning if you have things to apply it to). In fact, If you're in the mood, I suggest devouring the whole of Charles' site: http://www.decisionmodels.com/calcsecrets.htm

Rob

hi Jason,

Originally Posted by overbet

...I have been stepping through my code and getting an error right away under module 4 of NY-A-test. It is the 13th line of code ignoring the comments. It is saying compile error variable not defined and it highlights the word "done =" False. Someone told me I need to define the variable but I dont know how to fix this yet so kinda stuck at the moment....

The use of "option explicit" is what is identifying/forcing this compilation error & it's good because it stops any potential errors being hidden/ignored. If "option explicit" is not used, the computer creates a variable when it reaches that line of code. Although harmless in this situation, it can cause problems if a variable is created due to a simple spelling mistake eg

Please Login or Register  to view this content.

This should sort you out for this one

Please Login or Register  to view this content.

hth
Rob