Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Developing an Application from a group of Macros

    Hi Everyone:
    Can someone refer me to a good discussion (or give some tips) on how to approach design that addresses how to approach organizing a group of Macros (all in the same
    macros are either linked to CmndClick buttons on different WSs, in the "Private Sub Worksheet_Activate()" event for the WS, or code snippets that are in different modules that are called from other Macros. I have general questions like: is it better to try to combine as much of the code as possible (from all the separate pieces) into one or two macros or is better to have a single Macro (attached to CmndClick button in one of the WS) with Calls to all the other pieces. I mean, which approach would be most efficient and give me the least problems. I have been trying the latter approach and experiencing problems that I think may be associated with variable domains. I also am not sure when I need to insert Worksheets("sheetname").Activate (before the call or as the 1st statement in the called macro) and whether or not a DIM statement always needs to come before the Activate statement. I have tried several "Calls" in a row (with no code in between the calls and the Activate statement in the macro being called) but I suspect that after some of the operations are carried out in the called macro that the active property may revert to what it was when the macro was called.
    If all of the above does not make enough sense to help generate some meaningful comments, I could attach a file with the code but it might take me a day or two to get the time to do it with comments to make it make sense.
    Any response other than "give it up" or the like would be appreciated.
    Stephen <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Developing an Application from a group of Macros

    So, you're calling procedures from a main procedure right? I guess I'd need to know a little more about the mechanics of your design, what is it's purpose?

    If you're calling procedures from other procedures, you may well end up with problems in your variable designations. Your DIM statement must always come before the command that uses it. For example, if I have a button to advance a row with each click, I might do the following:

    Public i as integer <font color=448800>'makes the variable i a public variable, accessible by all code in the macro </font color=448800>

    Sub Worksheet_Initialize()
    i = 1<font color=448800>'at this point, I'm declaring that i = 1.</font color=448800>
    End Sub

    Sub btnNextRow_Click()
    i = i + 1<font color=448800> 'now i = 2 </font color=448800>
    Range("A" & i).Activate <font color=448800> 'A & i = A2, the next time I click the button, "A & i" will equal A3, and so on.</font color=448800>
    End Sub

    Perhaps you can provide a little more information? <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Developing an Application from a group of Macros

    Having all of your code in one or a few Subroutines and/or Functions normally makes it much harder to follow and understand, particularly after you have not looked at it for some time. It is normally better to divide the code into the smallest Subroutines or Functions that make logical sense. I try to keep my code in routines that perform a single logical function. On the other hand, it usually does not make sense to divide code into multiple routines just to make it smaller. You do have to be careful about the scope of variables when you have multiple routines.

    There are only a few things in Excel VBA that require that a sheet be activated or a cell to be selected. If it is not required by what you are doing, it is always faster to not do the activate or select.
    Legare Coleman

Posting Permissions

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