Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Location
    San Francisco Bay Area, California, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code on Worksheet or Module? (VBA/Office 2000)

    Within a VBA project is a list of modules and a list of worksheets. If I double-click on a worksheet icon under a VBA project, it opens a page in the editor that one can write code on. What is the difference between writing a VBA procedure on a worksheet page vs. a module page?

    Thanks.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Code on Worksheet or Module? (VBA/Office 2000)

    Procedures coded to a Worksheet are private to that Worksheet Object; they can't be called from anywhere else, and User Defined Functions coded at the Worksheet Object level cannot be used anywhere in the Workbook, they must be in a Module.

    Generally speaking the Worksheet Object Code windows are useful primarily for Worksheet Object Events and possibly procedures that are called only by those Events for only that Worksheet.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Code on Worksheet or Module? (VBA/Office 2000)

    Also, when writing code for a worksheet, sloppy coding will get you in more trouble, than in a module. In a worksheet an unqualified Cells(1,1) will give you A1 on the sheet containing the code, NOT the active sheet as in a module. When I started writing code for a worksheet, it was like learning to code all over again. Now, I just always qualify an object with a worksheet and/or a workbook: makes for a lot less debugging. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code on Worksheet or Module? (VBA/Office 2000)

    <hr>they can't be called from anywhere else<hr>

    Not true.

    Sheet1.Macro1

    calls Macro1 on sheet1's code module.

    Another good reason NOT to put other code than event code in the module of a sheet is because the code cannot be "cleaned". Editing is known to cause pile-up of rubbish behind the scenes. This rubbish can normnally be removed from normal modules by exprting, deleting and reimporting them. Deleting a sheet's module is impossible and if the sheet's code starts to show corruptions, one has to create the sheet from scratch.

    It is for that reason that I tend to only put calls to subs in normal modules (which do the actual processing) in my sheet modules.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code on Worksheet or Module? (VBA/Office 2000)

    I've recently adopted the practice of putting ALL code in a class in Excel.
    I've coded the class so that it works unchanged in either VB 6 or Excel VBA.

    The only code I include in a regular Excel VBA module is:

    1. Auto_Open to instantiate the class.
    2. Auto_Close to destroy the class.
    3. A wrapper function for each function in the class that I wish to use as a worksheet function.

Posting Permissions

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