Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Reduce Workbook Size

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What is the best way to reduce workbook size? The workbook has 16 modules and approximately 20 ActiveX drop-down-boxes.

    Also, is it possible to rename the modules. I hate calling them Module 1, etc. Plus, if you get over 10, they are out of order.


    Thanks in advance,
    Patrick

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Figured out how to rename the modules. Duh. I am an idiot. LOL.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please tell us other idiots.

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In the VBA editor, open properties [either F4 or from the view menu. Click on the module in the project explorer. In the properties window, you can change the name of the module [just like a text box or combo box]. Just be careful not to name it with the same name as a sub in the module.
    Otherwise, you will receive an error message.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    A module with about 500 lines of code adds about 20 - 25 kb.
    Not really too significant...
    If you really are concerned about size, you can load the vba modules dynamically as you need them...
    Just learned how to do it yesterday.
    Not too sticky
    Also cell formatting instructions...
    From what I understand, formatting a whole column of 65536 rows, by selecting the column and not the rows, uses one instruction.
    Formatting an individual cell uses one instruction as well.
    Tom

    [ This Message was edited by: TsTom on 2002-04-23 20:35 ]

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How do you load modules dynamically? I might not do it, but I would still like to know how. Please share.

    Thanks again!
    Patrick

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Give me a couple of minutes to drum up a decent example Patrick...

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay - thanks again Tom.

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Create a folder:
    "C:/DynaLoad"

    Open a new workbook, add a module,
    rename the module "DynaMod"

    Place the following procedure into
    the module:

    Sub LoadDynaTest()
    Sheet1.Range("A1") = "Tis' Loaded!"
    End Sub
    Export the module into folder
    "C:/DynaLoad"

    Now remove the module from your
    workbook all together.

    Insert another module into this
    workbook(name does not matter)

    Place this sub in the module:

    Sub CallDynaLoadedProcedure()
    LoadDynaTest
    End Sub
    In whatever way you want to call it, call it:
    The example here loads the module when the workbook opens.

    Private Sub Workbook_Open()
    Application.VBE.ActiveVBProject.VBComponents.Import "C:/DynaLoad/DynaMod.bas"
    CallDynaLoadedProcedure
    End Sub

    You cannot call the procedure directly
    until the bas file is loaded or you will get a compile error. That is why
    we have the middleman procedure.
    Save the workbook. Close it.
    Open it. Check sheet1.Range("A1")
    Your module is loaded into the project if the range has the data...

    Tom

    [ This Message was edited by: TsTom on 2002-04-23 21:13 ]

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Pittsburgh, PA
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Wow - interesting. Tom, thanks for taking the time to post the code. I'll try to use it in the future.

    Thanks!

Some videos you may like

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
  •