optimize vba for i7 procesor and 64 bit

[mountainclimber11]

Hello,

Here is what I have:
1) an i7 (8 core) processor,
2) 64 bit machine,
3) Excel 2007 (32 bit),
4) a massive permutation program written in VBA with lots of seemingly unavoidable loops that take weeks to run,
5) one (of 8) cores is about maxed out constently while this program runs.

Questions:
a) What options do I have to speed this program up?
b) How can I take advanage of the other 7 processors?
c) Is there anything I can do regarding the 64 bit machine / 32 bit Excel situation and would it help with anything (not just speed)?
c) Is there any way to run two VBA scripts at once on one machine?
d) Any links on the subject would be great.

I did some searching online and didn't see any way to take advantage of the multi-core processor using VBA nor did I see a 64 bit version of Excel. (It looked like a 64 bit Excel is either in beta or will be out in 2010.)

Thanks!

 

[mountainclimber11]

Yeah, forgot something again. Above the timer thing I posted (foo()) you will need this:

Public Declare Function QueryPerformanceFrequency _
    Lib "kernel32.dll" ( _
    lpFrequency As Currency) As Long
Public Declare Function QueryPerformanceCounter _
    Lib "kernel32.dll" ( _
    lpPerformanceCount As Currency) As Long

 

[Norie]

There should be no need to use Activate, that like Select is rarely - if ever - needed and could also be slowing down the code.

I don't quite follow the 'VBA guy's' thing - I think in every programming language I've used variables wouldn't be declared in code, never mind within a loop.

As far as I can see every time you loop you'll be, if you like, 're-declaring' the variables.

I've got a feeling that might impact on performance somehow.

Any chance you could answer the question about the code taking a week or weeks to run?

Is that true? If it is then that sounds like a pretty inefficient system/method/badger/way of doing something.

I've no qualifications in finance etc but I've worked for some pretty large financial organizations and in the finance/insurance departments) of other organizations.

I've never heard of code taking that long to complete.

 

[Stray__CAt]

Albeit you can improve your code, there's no chance you'll have Excel use more than one core to run the macro.

What you described appears to be a problem to be solved by, in order of complexity:

1) a bit of database programming
2) a specific mutithreaded application
3) data mining

I'd focus on porting the app on another platform instead of insisting on a clearly unfit platform.

my 2 cents

 

[AlphaFrog]

Could you store the permutations of the first five digits (1,2,3,4,5), then for the next combo, just add a 1 to the stored permutations to get the next power5 permutes of (2,3,4,5,6) ?

 

[mountainclimber11]

AlphaFrog - If I understand you, and I may not, that would not give me all of the combinations. For example I need the combination 12356.

The powerset5 gives me the combos. It uses static variables inside the function to track the last combo and incriments last digit until it is greater than 200 then the digit to the left is incrimented by one and the far right takes on a value on greater than the one on the left. I use simple loops for this with statics so there the last combo is not lost and it only has to add one. Maybe that is something like what you mean....

Stray_Cat - I hope I am data mining. In its basic form, this is a association rule permutation process...data mining. I would add some sql to it but I don't know it and I need to get this thing cranking and can't start over at this point. Basically I have to do the best with what I have at this point....just want to do small fixes and optimizations etc.

Norie -
Initialization: Use the timer I posted to time an intialization, then take out the intialization. There is no time difference. The director of the Computer Science department where I went to school said it is best to intialize on the smallest scope possible...so what is what I do. I would move it out of the loop if it took any more time to Dim again. If I am not mistaken a Dim happens just once and is ignored after that within a loop. My limited testing backs this up. In a big program such as this I don't like having to refer all the way back up to the top of the script to check the initialization etc. But it does make the code look nicer and almost everyone on the forum does it your way. I'm about function...
Timing: I really have no idea how long it will take. "Weeks" is a guess. I started running it about 12 hours ago (I think) to test it and it is on permutation 67 46 12. It has to go to 5 digitis (it is on 3) and the last combination will be 196 197 198 199 200 (the last permutionation will be the reverse of those numbers)...I have a long way to go at this rate. Anyway, I ran it with the statusbar updated every iteration and the permuations go so fast I can't make out the numbers. Seems like a pretty fast pace to me. I am also getting more ganular in my efficiency testing to see if I can improve a few key functions. The Activate part is a huge help. I don't know how to get a value without activating the file first. Can you post how to do that? I will look online too.

In the end, I think I'll optimize it and run it for a few days and back off on the 200 number and bring the digits of the premutations down to 3 to cut down the iterations....I think. Anyway, a week is my max target run time and once I'm done speeding up the code I will figure a permutations/hour rate to target a total run time.

Thanks everyone for your help so far! Please post anything you can think of. Thank you! Your posts have helped a lot so far...

 

[mountainclimber11]

Just read that variants take longer processing time. Did not know that:
http://www.cpearson.com/excel/DeclaringVariables.aspx

I'd like to do that. This is what is making a lot of my variables Variants:

Option Base 1
Sub tester()
Dim iArray(2) As Integer
iArray(1) = 1
iArray(2) = 2
MsgBox Join(iArray, "_")

End Sub

The above code won't work unless I do this:

Option Base 1
Sub tester_String()
Dim iArray(2) As String
iArray(1) = "1"
iArray(2) = "2"
MsgBox Join(iArray, "_")

End Sub

How do I get Join (or something like it) to work on a Array of integers (other than making them variants)?

 

[mountainclimber11]

Nevermind on the no activating the workbook issue:
Workbooks(strName).Sheets(1).Cells(1, 1)
I feel a little silly now.
...But Thanks!

 

[mountainclimber11]

wow...taking out the Activate stuff cuts my run time by 1/2!

Thanks Norie!

 

[James_B]

Hi MountainClimber,

I sometimes write high-iteration stuff in C++, which is much quicker than VBA. Alternatively, if you have access to Matlab, that might well be worth a go. It has a 'parfor' command specifically designed to take advantage of parallel processing capabilities, as well as built-in permutation functions--e.g. perms, nchoosek, etc.

Failing that, if you're sticking with VBA for whatever reason, I wonder if you could save some time with your workbook access. Are you grabbing data from workbooks within a loop? If so, how are you doing this? Are you opening and shutting it each time? Could you put all the data in the same workbook? Could you make them .csv files and use querytables, etc.?

 

[mountainclimber11]

James B,

I sometimes write high-iteration stuff in C++, which is much quicker than VBA. Alternatively, if you have access to Matlab, that might well be worth a go. It has a 'parfor' command specifically designed to take advantage of parallel processing capabilities, as well as built-in permutation functions--e.g. perms, nchoosek, etc.

>>I'm at the end of the development now, so I need to just make it as good as it can be in vba then run it. Then I'll venture into access, sql, or whatever... I think MatLab might be a good idea too... But unfortunately I only know how to use excel and vba...right now.

Failing that, if you're sticking with VBA for whatever reason, I wonder if you could save some time with your workbook access. Are you grabbing data from workbooks within a loop?
>>I just open all the files I need (up to 200) and do something like this:

Do While x > y
 
'other stuff here....
 
 
with Workbooks(wb).Sheets(sht)
       something = .Cells(r,c).Value
end with
 
'other stuff here....
 
Loop

...and never mess with Activating anything. (Thanks to Norie's suggestion...)
Is there a faster way to do it (within VBA)? I don't think I could suffer through another full rewrite unless I was translating it into sql or something, but its work know for future reference.

If so, how are you doing this? Are you opening and shutting it each time? Could you put all the data in the same workbook?

>>see above. The files get too big if I put everything in one file.

Could you make them .csv files and use querytables, etc.?

>>I know what a .csv file is. After that I don't understand. Can you give me a link to a sample or something? Is that faster?

Thanks!