dcsimg
Results 1 to 6 of 6

Thread: [RESOLVED] [Excel] Please, help to optimize VBA code

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2016
    Posts
    7

    Resolved [RESOLVED] [Excel] Please, help to optimize VBA code

    Dear colleagues,

    Can I ask for advice on optimization of a certain VBA-code?

    The purpose of this subroutine is to generate unique promo-codes for coupon promotion, and to store them in Excel Sheet.

    Program takes all inputs from Excel cells, in particular: required #of variable characters in the code, list of valid characters, required #of codes.

    I run it through a nested loop, and then use RemoveDuplicates functionality to cut any duplicate codes.

    So far it shows decent performance on my PC (about 1800 codes per second). However, I'm a novice coder myself, so I'd appreciate any advice on how to improve performance further or generally improve the coding.

    Excel file with the code is attached. Comments in the code are in Russian, but I believe you wouldn't need those anyway.

    Thank you in advance

    P.S. I had to pack .xlsm into a .zip, because .xlsm is not allowed on the forum (which is a bit strange to me - why .xls, but not .xlsm?)

    Code Generator v2.zip
    Last edited by VSPavlov; Dec 8th, 2016 at 02:18 AM.

  2. #2
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: [Excel] Please, help to optimize VBA code

    One suggestion is to declare and use Range variables instead of using the evaluate shortcut.

    i.e.: the usage of [rSymbols].Value you would do something like this:
    Code:
    Dim rSymbols As Excel.Range
    Set rSymbols = ThisWorkbook.Names("rSymbols").RefersToRange
    and then just use rSymbols.Value. This should be beneficial for those named ranges used inside the For-Next loop.

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2016
    Posts
    7

    Re: [Excel] Please, help to optimize VBA code

    Thank you, TnTinMN. I see how that way is more "by-the-book". Tomorrow I'll try to use it and see if it affected the performance as well.

    By the way, if I may ask, why "Excel.Range" and not just "Range"? And why do you use RefersToRange method for Set - couldn't we just use the evaluate shortcut for this particular case?

  4. #4
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: [Excel] Please, help to optimize VBA code

    Quote Originally Posted by VSPavlov View Post
    ...
    By the way, if I may ask, why "Excel.Range" and not just "Range"?
    I'm glad to see that you picked up on that.

    Many have have no clue that a given type is associated with a particular library (namespace) and it is my subtle way to I try to get people to think and question the code instead of just copying and pasting. It is also avoids confusion if you have code that references both Excel and Word and use objects that have the type name. Both Excel and Word have Range objects.

    I also write Office automation code where knowing which library a given type is defined tends to be more relevant, so doing so in VBA helps me learn this information and keeps it simple for me as I move between languages.


    Quote Originally Posted by VSPavlov View Post
    ...
    And why do you use RefersToRange method for Set - couldn't we just use the evaluate shortcut for this particular case?
    You probably could use the evaluate shortcut. I'm an old fart that learned programming before the stylistic value of so-called elegant code became more important to many than the value of writing sound code that performs well. Hence, I tend to choose my code statements to minimize unnecessary work for the CPU.

  5. #5

    Thread Starter
    New Member
    Join Date
    Dec 2016
    Posts
    7

    Re: [Excel] Please, help to optimize VBA code

    Well, thank you for your suggestion. It got me thinking and, actually, instead of declaring Range variables, I immediately declared them all as Strings and worked only with variables within the loop ever after.

    The effect is extraordinary, honestly: performance has improved from 1 850 codes/second to 16 700 codes/second. The improvement is so big, I'm not even sure if that's connected with the changes I made. Maybe some clean-up was done on PC overnight, increasing overall performance of the OS.

    In any case, thanks a lot for your help.

  6. #6
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: [Excel] Please, help to optimize VBA code

    Quote Originally Posted by VSPavlov View Post
    Well, thank you for your suggestion. It got me thinking and, actually, instead of declaring Range variables, I immediately declared them all as Strings and worked only with variables within the loop ever after.
    That is a great improvement over referring to range variable for those in the loop. It prevents the VBA code from having to interop with Excel each time it needs the value. In a quick test, it is about 25% faster than using the range variables.

    Using range variables directly was about 8 times faster than using the original evaluation to range.

    Good job!

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width