Excel VBA Tools from Andrew
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA Code Location Question - Module vs. Worksheet Level Code

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Dallas, Texas
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone offer any general guidelines regarding code placement (in modules vs. sheets vs. workbooks. vs. class modules)?

    Is vba code stored in a sheet object limited to handling events, buttons, dropdowns, etc?

    If I define a custom function in a sheet object I can see it back in Excel in the Insert Function list but it won't work, even when I include the full path.

    Works fine when stored in a regular VBA module.

    (FWIW - I wanted to put the function in the worksheet object so the code would go along with the worksheet when it was pulled out of a workbook. I've since learned in this forum how to export code from a module & then re-import it into the new workbook, but the other way sure would have been easier.)

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Check out Mr. Pierson's information at:

    http://www.cpearson.com/excel/codemods.htm

    Hope it helps!
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Dallas, Texas
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just what I needed!

    Thanks

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Great!

    Export and Import?
    Cut and paste works well when you're in the same workbook. (Delete any empty modules.) If you're *sharing* your code with someone else--that's when I export. Then send them the *.bas file.
    ~Anne Troy

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    Dallas, Texas
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Export & import because I'm cloning a set of worksheets programatically from hidden template sheets to create a new workbook when a button is clicked & some code modules need to go along with the sheets.

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
  •  

 

DMCA.com