Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: optimize vba for i7 procesor and 64 bit

  1. #1
    Board Regular
    Join Date
    Dec 2009
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default optimize vba for i7 procesor and 64 bit

    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!
    Last edited by mountainclimber11; Apr 4th, 2010 at 12:35 PM.

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,953
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    6 Thread(s)

    Default Re: optimize vba for i7 procesor and 64 bit

    I don't know about optimizing VBA for 64-bit, but have you asked around about your VBA code itself to see if there are better ways to code it and avoid the endless loops. Often there are alternate ways to code that could significantly improve performance by orders of magnitude.

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,856
    Post Thanks / Like
    Mentioned
    55 Post(s)
    Tagged
    5 Thread(s)

    Default Re: optimize vba for i7 procesor and 64 bit

    You have code that takes weeks to run?

    Are you sure Excel/VBA is the right tool for the job?

    The longest I've seen code running was for about 7 hours, ie a working day.

    Admittedly it didn't involve permutations/whatever but as soon as I imported the data involved to Access I was able to write a query in minutes that gave the same results as the code in seconds.

    If you must use VBA then I agree with AlphaFrog - it's probably the code you should be looking at rather than trying to 'optimize' the hardware.
    Last edited by Norie; Apr 4th, 2010 at 01:47 PM.
    If posting code please use code tags.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,300
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: optimize vba for i7 procesor and 64 bit

    I've been using Excel 2010 beta for a few months. You may like to give it a whirl. I'm not sure if it will be a big help or not but it might make better use of your system's resources.

    You may find some interesting blogs on this topic:
    http://blogs.msdn.com/excel/archive/...computing.aspx
    http://blogs.msdn.com/excel/archive/...03/488822.aspx

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Dec 2009
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: optimize vba for i7 procesor and 64 bit

    Thanks. Well, I didn't post my code last time because its liternally 4000 lines long (with a ton of comments/notes). However, below is simplified version of what I am doing. The code below is within 3 other loops, but they are not as time consuming as these. The time consumer in my program is the massive amount of iterations that have to be done because I'm looking at as many as 274,124,700,000 permutations if I run the program once.

    Here are the detail on the permutation value if you want them:
    You can find this value via this equation:
    n!/(r!(n-r)!)
    ...where r is the number chosen and n is the number that can be chosen from. n can be any integer from 1 to 200 and r can be any integer from 1 to 5....and that entire process is repeated as many as 30,000 times. Now I know this is not realistic unless I used a super computer or something, but I can pair things down to a managable number of iterations by reducing r and n that I discussed above. "Managable" to me is about a week per run. But of course I would like to maximize what I can do during that week...hence my post.

    Anyway, here is the simplied version of the part of the program that is repeated many times over (there is a ton more code that is not used as much):

    Code:
     
    Do While iUpperBoundIndexComboArray <> iIndyCount And iUpperBoundIndexComboArray <> 6 'will repeat combos over and over again b/c statics are reset for next loop
     
                                    Dim iaIndexPerm() As Variant 
                                    vaIndexPerm = PowerSet5(iIndyCount, bResetStatics) 'return the next combination of the indices needed
                                    bResetStatics = False 'so that statics in PowerSet5 won't reset during loop
                                    iUpperBoundIndexComboArray = UBound(vaIndexPerm)
                                    If iUpperBoundIndexComboArray >= 6 Then
                                        Exit Do 'skip backtest if it is last combo (PowerSet5 sends 6 element array to signal last combo has already been given
                                    End If
                                    Dim bStopPermuting As Boolean
                                    bStopPermuting = False
    'PERMUTATION LOOP..........................................................................................................................................
                                    Do While bStopPermuting = False 'will run through all permutations of each combination then ask for new combo to permute
                                                                    'each permuation is a input for test method
                                        Dim greater_c As Double
                                        greater_c = Empty
                                        Dim greater_s As Double
                                        greater_s = Empty
                                        ReDim isgreaternowArray(3) As Variant
                                        Dim keepdoinggreaternow As Boolean
                                        keepdoinggreaternow = True
     
                                        Dim i As Integer
                                        For i = 1 To iUpperBoundIndexComboArray
     
                                            Workbooks(straIndyArray(vaIndexPerm(i))).Activate 'get the file name for indy, including file extension
     
                            'greaternow...........................................................................................................start
     
                                            If do_greaternow And keepdoinggreaternow Then  'keepdoing_greaternow is used and isgreater starts false so single bar test will be false
     
                                                ReDim isgreaternowArray(3) 'erase the last values
                                                isgreaternowArray = greaternow(i, greater_c, greater_s, comptoprange, seriestoprange)
                                                greater_c = isgreaternowArray(2) 'store the last values for comparison to the next
                                                greater_s = isgreaternowArray(3) 'same as above but for series
     
                                                If isgreaternowArray(1) = False And i <> 1 Then ' And new indy <> 1 b/c comparison is always false for one indy
                                                        keepdoinggreaternow = False 'part of the comparison was false so stop doing this test method for this permutation
                                                End If
                                            End If
                            'greaternow.............................................................................................................end
     
                            'other test methods here...............................................<<<<<<<<<<<<<<<<<<<
     
                                            Next i 'goes through indys of permutation one by one, loop exits when permuation is finished
     
        'Record test LABELS that were true in permuation loop>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Record test LABELS>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Record test LABELS>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.............................................................................................................................................
                                            'duplicates would be made if RepCheck was not used, b/c comp is compared to each series
                                            Dim strTestLabel As String
                                            If isgreaternowArray(1) = True Then
     
                                                strTestLabel = BuildTestLabel(straIndyArray, iaIndexPerm, "GtrNw", bb, compmoverow)
                                                result = RepCheck_RecordTestLabelToPriceFile(strTestLabel)
     
                                                If result = True And bResultFound = False Then 'false = rep found
                                                    bResultFound = True 'must be done in every criteria recording section
                                                End If
     
                                            End If
     
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                                    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                                        bStopPermuting = PermuteIndices(iaIndexPerm)                   'permutes the given combination of indices and returns bool used in Do While
                                    Loop 'Do While bStopPermuting = False ...go through ea permutation '<-keep together
                                    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                                    If tLastTime + TimeValue("00:10:00") < Now Then
                                        Application.StatusBar = "Perm: " & Join(vaIndexPerm, "_") & ",  bb: " & bb & ",   Series: " & seriesmoverow & ", Comp: " & compmoverow & "  (This status will update again at: " & Now + TimeValue("00:10:00")
                                        tLastTime = Now
                                    End If
                                Loop 'Do While iUpperBoundIndexComboArray >=6 .....go through each combination and let next look cycle through the permutation of that
                                    'combination
    Last edited by mountainclimber11; Apr 4th, 2010 at 02:56 PM.

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,856
    Post Thanks / Like
    Mentioned
    55 Post(s)
    Tagged
    5 Thread(s)

    Default Re: optimize vba for i7 procesor and 64 bit

    What, in words, is the code meant to do?

    There's a couple of things I can think of that might be changed, though I couldn't tell you if they'll have any impact on performance.

    The first thing is to use Long, not Integer.

    The second is don't declare variables in the middle of the code, especially within loops.

    Oh, and I hope you don't mind I indented the code to make it a little easier to understand, ish.

    And you don't need to initialize your boolean variables - there default value is False as far as I know.
    Code:
    Dim iaIndexPerm() As Variant    'join only works with string and variant
    Dim greater_c As Double
    Dim greater_s As Double
    Dim keepdoinggreaternow As Boolean
    Dim strTestLabel As String
    Dim I As Integer
    Dim bStopPermuting As Boolean
    
    Do While iUpperBoundIndexComboArray <> iIndyCount And iUpperBoundIndexComboArray <> 6    'will repeat combos over and over again b/c statics are reset for next loop
     
        iaIndexPerm = PowerSet5(iIndyCount, bResetStatics)    'return the next combination of the indices needed
    
        bResetStatics = False    'so that statics in PowerSet5 won't reset during loop
        
            iUpperBoundIndexComboArray = UBound(iaIndexPerm)
    
            If iUpperBoundIndexComboArray >= 6 Then
                Exit Do    'skip backtest if it is last combo (PowerSet5 sends 6 element array to signal last combo has already been given
            End If
    
            bStopPermuting = False
            'PERMUTATION LOOP..........................................................................................................................................
            Do While bStopPermuting = False    'will run through all permutations of each combination then ask for new combo to permute
                'each permuation is a input for test method
                greater_c = Empty
                greater_s = Empty
                ReDim isgreaternowArray(3) As Variant
                keepdoinggreaternow = True
    
                For I = 1 To iUpperBoundIndexComboArray
                    Workbooks(straIndyArray(iaIndexPerm(I))).Activate    'get the file name for indy, including file extension
                    'greaternow...........................................................................................................start
                    If do_greaternow And keepdoinggreaternow Then  'keepdoing_greaternow is used and isgreater starts false so single bar test will be false
                        ReDim isgreaternowArray(3)    'erase the last values
                        isgreaternowArray = greaternow(I, greater_c, greater_s, comptoprange, seriestoprange)
                        greater_c = isgreaternowArray(2)    'store the last values for comparison to the next
                        greater_s = isgreaternowArray(3)    'same as above but for series
                        If isgreaternowArray(1) = False And I <> 1 Then    ' And new indy <> 1 b/c comparison is always false for one indy
                            keepdoinggreaternow = False    'part of the comparison was false so stop doing this test method for this permutation
                        End If
                    End If
                    'greaternow.............................................................................................................end
                    'other test methods here...............................................<<<<<<<<<<<<<<<<<<<
                Next I    'goes through indys of permutation one by one, loop exits when permuation is finished
                'Record test LABELS that were true in permuation loop>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Record test LABELS>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Record test LABELS>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.............................................................................................................................................
                'duplicates would be made if RepCheck was not used, b/c comp is compared to each series
                If isgreaternowArray(1) = True Then
                    strTestLabel = BuildTestLabel(straIndyArray, iaIndexPerm, "GtrNw", bb, compmoverow)
                    result = RepCheck_RecordTestLabelToPriceFile(strTestLabel)
                    If result = True And bResultFound = False Then    'false = rep found
                        bResultFound = True    'must be done in every criteria recording section
                    End If
                End If
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                bStopPermuting = PermuteIndices(iaIndexPerm)                   'permutes the given combination of indices and returns bool used in Do While
            Loop    'Do While bStopPermuting = False ...go through ea permutation '<-keep together
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            If tLastTime + TimeValue("00:10:00") < Now Then
                Application.StatusBar = "Perm: " & Join(iaIndexPerm, "_") & ",  bb: " & bb & ",   Series: " & seriesmoverow & ", Comp: " & compmoverow & "  (This status will update again at: " & Now + TimeValue("00:10:00")
                tLastTime = Now
            End If
        Loop    'Do While iUpperBoundIndexComboArray >=6 .....go through each combination and let next look cycle through the permutation of that
        'combination
    If posting code please use code tags.

  7. #7
    Board Regular
    Join Date
    Dec 2009
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: optimize vba for i7 procesor and 64 bit

    Correction: max permutations-> 2,741,248,500,000
    And "iaIndexPerm" should be "vaIndexPerm" throughout. It is an array of variants. I missed a few in my last post.
    Last edited by mountainclimber11; Apr 4th, 2010 at 03:08 PM.

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,856
    Post Thanks / Like
    Mentioned
    55 Post(s)
    Tagged
    5 Thread(s)

    Default Re: optimize vba for i7 procesor and 64 bit

    Could you explain in words what you are trying to do?

    I honestly don't think Excel/VBA is the tool to deal with that many permutations.

    Is this code really taking a week to run on a 64-bit machine with 8 processors?

    If it is there must be something not quite right somewhere.
    If posting code please use code tags.

  9. #9
    Board Regular
    Join Date
    Dec 2009
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: optimize vba for i7 procesor and 64 bit

    >>Thanks. My notes are embedded below:

    What, in words, is the code meant to do?

    >>Summary:
    Its a trading analytics tools. Basically I find pattners in the market and build trading machines based on it. (I have an MS in Finance/Investments and I code as necessary.)
    Here are the basic steps:
    1)Find all combinations
    2)Find all permuations of all combinations
    3)Activate correct file with desired values using permuations as indices
    4)Do a comparison of the values
    5)Record the observation if it was intersting
    6)Repeat

    Here are the details ...For the code I posted (the entire program description would take a book to explain):

    The first Do While Loop goes through every combination of a set of 200 values 1 through 200 choosing 1 then 2 values and so on to 5 values (my PowerSet5 function). So:
    One digit:
    1
    2
    3... to 200
    then two digits:
    1 2
    1 3
    1 4...to 199 200
    then 3 digits
    1 2 3
    1 2 4
    1 2 5....to 198 199 200
    ...on up to five digits where the first 5 digit combination would be 1 2 3 4 5 and the last combination would be 196 197 198 199 200

    The the next loop does permuations of each of these combinations:
    The first permutation would just be 1 and the last permutation would be 200 199 198 197 196.
    For example a 3 digit combination of 1 2 3 would have these permutations:
    1 2 3
    1 2 4
    1 2 5...to 3 2 1
    There permutations are all done at the bottom of the second loop show above using:
    Code:
    bStopPermuting = PermuteIndices(vaIndexPerm)
    The "For i = 1 To iUpperBoundIndexComboArray" loop:
    The permutations of combinations are actually idices for referencing different string file names in a array of strings called straIndyArray. I then use those file names to open files with the values I want. The use of the permuted indices to find the file name and open up a file with the proper values on it is done on this line:
    Code:
    Workbooks(straIndyArray(vaIndexPerm(i))).Activate
    With the proper file active (leave the 200 files open to save time), I reference certain cell values for comparison on these individual files. I only show one comparison function in my code (to keep it simple):
    Code:
    isgreaternowArray = greaternow(i, greater_c, greater_s, comptoprange, seriestoprange)
    Then after the "For i = 1 To iUpperBoundIndexComboArray" loop, if I found something of interest, I build a label (for reference in a different part of the program not discussed here) and check to see if I already found this 'thing of interest' and record the label if I did not already observe it and record it before. This is label building, checking and recording is done in:
    Code:
    strTestLabel = BuildTestLabel(straIndyArray, vaIndexPerm, "GtrNw", bb, compmoverow)
                                                result = RepCheck_RecordTestLabelToPriceFile(strTestLabel)
    Then, as mentioned above in the "second loop" I find the next permuation of the combination of Indy's via:
    Code:
    bStopPermuting = PermuteIndices(vaIndexPerm)
    And at the end of the outter most loop, the "first do while" discussed in the beginning of this post, I find the next permutation of the indices of the combinations that were found:
    Code:
    bStopPermuting = PermuteIndices(vaIndexPerm)
    Oh and at the bottom of the outter most loop I update the status bar every 10 minutes...just to be sure its all working still....may take out once I am all finished.

    The first thing is to use Long, not Integer.
    >>Everything that is a Long must be a Long (unless I missed one or two). I've checked this already.

    The second is don't declare variables in the middle of the code, especially within loops.
    >>I've put a timer on the Dim's and it makes zero time difference. Personally I like the Dim's as close to where I use the variable as possible for reference etc. I know you VBA guys like your at the top...sorry that would drive me crazy. Here is an example how I timed it (I found this online somewhere):
    Code:
    Sub foo()
    Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
    Dim Overhead As Currency
    Dim i As Long
    
    QueryPerformanceFrequency Freq
    QueryPerformanceCounter Ctr1
    QueryPerformanceCounter Ctr2
    Overhead = Ctr2 - Ctr1 ' determine API overhead
    QueryPerformanceCounter Ctr1 ' time loop
    For i = 1 To 100000
    
    Dim my_something as Long 'time it with and without this line
    
    Next
    QueryPerformanceCounter Ctr2
    Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
    End Sub
    Oh, and I hope you don't mind I indented the code to make it a little easier to understand, ish.
    >>No problem at all, thanks...

    And you don't need to initialize your boolean variables - there default value is False as far as I know.
    >>I think every place I do this it needs to be done since I need to reset them given some contraints. But I'll take another look at that. Thanks!

    Any and all suggests are welcome!

  10. #10
    Board Regular
    Join Date
    Dec 2009
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: optimize vba for i7 procesor and 64 bit

    I agree that VBA is not the best, but I don't know SQL or something like that, that would work better.

    I think the issue is the number iterations, which is something I can deal with, but I need to maximize/optimize things.

    Note: I WANT to use all 8 processors, but VBA will only use one.


    Quote Originally Posted by Norie View Post
    Could you explain in words what you are trying to do?

    I honestly don't think Excel/VBA is the tool to deal with that many permutations.

    Is this code really taking a week to run on a 64-bit machine with 8 processors?

    If it is there must be something not quite right somewhere.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •