+ Reply to Thread
Results 1 to 8 of 8

VBA Efficiency Question - better to call a private sub or repeat same code?

  1. #1
    Registered User
    Join Date
    12-09-2011
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2007/2010
    Posts
    20

    VBA Efficiency Question - better to call a private sub or repeat same code?

    Hello,

    I am fairly new to VBA and I have a general question regarding coding efficiency. Is it more efficient to have a set of code in a private sub and then call it when needed or to repeat the set of code over and over again?

    I have a userform with many controls on it. There are multiple checkboxes that I use to effect certain controls (e.g. fonts, visibility). For example, if checkbox1 is True, then the font on Label1 is bold, and visible is True (done to multiple controls). Then, for checkbox2, which is used for other purposes but also requires Label1 to be bold and visible.

    I have multiple controls where this would take place. I know having a private sub to call would be easier for me when writing this code, but is it more efficient than just coding these changes over and over again?

    Thanks.
    Last edited by jellyjen17; 02-09-2012 at 07:24 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: VBA Efficiency Question - better to call a private sub or repeat same code?

    I think the reality is that if you're going to do something ten times, you're going to do something ten times regardless of whether the code is in line or a loop. That's likely to take pretty much the same amount of time whichever approach you take.

    However, think of it in terms of extremes. If you're going to do the same thing twice, why bother thinking about a loop or a subroutine? If it's the same thing ten times, how much effort is involved in copying and pasting the code 9 times? Is that more or less effort than working out the loop? If it's the same thing 100 times or a 1000 times, well that's not really scaleable, is it? But if it's a "similar" thing? That is, basically the same code but with a different range or variables? Copy, past, adjust range, copy, paste, adjust range, ... how much of a pain is that? And then the boss says, add a new cell, column or row ... or any combination. Then all your ranges will need adjusting to reflect the new structure. Unless you set it up using named ranges ... but then, as you're asking the question about "to loop or not to loop", I'm going to guess that you won't have done that.

    Probably clear on which side of the fence I'd come down. It doesn't need to be a subroutine but that's not a bad option if you want to pass parameters.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: VBA Efficiency Question - better to call a private sub or repeat same code?

    Inline code will be faster but probably not noticeably so, so as TMS said, go with what is easier to write and maintain. (also depends on what you mean by efficient)
    Good luck.

  4. #4
    Registered User
    Join Date
    12-09-2011
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2007/2010
    Posts
    20

    Re: VBA Efficiency Question - better to call a private sub or repeat same code?

    Hi TMS,

    Thanks for the input. Good points, and I do agree, especially considering the editing scenario. I would much rather make a change in 1 spot than in however many places throughout the code. With this particular question, the spreadsheet is still in development and I was thinking more in terms of how fast the code would run when a user was working with the final product, but failed to think of things like editing or making future adjustments. And, like you said, doing something ten times is doing something ten times. I am pretty much learning this VBA stuff on my own, and this forum has helped out a lot. Being newer to VBA, I don't want to pick up any more bad habits than I already have!

    Thanks again! jay

  5. #5
    Registered User
    Join Date
    12-09-2011
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2007/2010
    Posts
    20

    Re: VBA Efficiency Question - better to call a private sub or repeat same code?

    Thanks OEG0! I agree & indeed I will!

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: VBA Efficiency Question - better to call a private sub or repeat same code?

    As OEG0 says, in line code "will be faster but probably not noticeably so" because the "compiler" just drops straight through. To loop, it has to do some additional work storing variables. But do you want VBA to do the work, or do you want to do it yourself? There are probably things that you should do or shouldn't do when setting up a loop (for efficiency) but that's a different question and probably needs to be asked about a specific "issue".

    Another example we see a lot is people offsetting and selecting cells to find the next empty row ... when you can get there in one statement. You more often than not don't need to select anything; if you are, ask us how it can be done better ... that will have more of an impact than the overhead of loops and called subroutines.


    Regards, TMS

  7. #7
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: VBA Efficiency Question - better to call a private sub or repeat same code?

    You could always GoSub... Return...

  8. #8
    Registered User
    Join Date
    12-09-2011
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2007/2010
    Posts
    20

    Re: VBA Efficiency Question - better to call a private sub or repeat same code?

    TMS & OEG0, thanks for the extra tips, and I will keep those in mind going forward!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1