Posts by bgano

    Re: Variable Print Area

    The problem was, you were .Selecting Range("A5:Q5") in line 3, then printing out the selection later on in the code. You want to print the sheet, not the selection. I've added some comments to your code to help you out.

    Re: VBcomponent remove before exit

    That procedure will only work if it is the only thing running in the VBA session. Any modules containing procedures you call before hand will not be removed immediately, which may be required.

    For example, I have 75 xls files that all contain the same code modules. When I need to update the code modules, I need a way to import them into all files without doing it manually.

    We use an additional "utility" xls file to sequentially open, update the code, and save each file. To do this, I have to loop through all components, remove the component, then re-import the current code. I can't import, because the .Remove command doesn't execute until all code has stopped running.

    Here's what VBA does:

    • Queue .Remove command, but don't execute it
    • Attempt to import component
    • See that the component already exists (because it hasn't executed the .Remove yet)
    • Append a "1" to the end of the component name
    • Perform any other steps
    • Execute queued .Remove command on original component

    Now we're left with all components that couldn't be .Removed right away having a "1" appended to their name.

    Re: VBcomponent remove before exit

    I found a solution to my problem. Maybe it will help you too.

    It turns out that by simply including a call to a procedure in the Workbook_Open event, Excel will "lock" the code. This happens whether or not the call is actually executed. For example, given this code:

    Private Sub Workbook_Open()
       If False Then
          Call MacroInSomeModule
       End If
    End Sub

    The "SomeModule" module will still be locked even though the code is never executed. To solve the problem, you simply have to call the macro indirectly using the Application.Run procedure:

    Private Sub Workbook_Open()
    End Sub

    Even when the code is executed, the module can still be removed.

    Re: Variable Print Area

    There are many ways to find the row you're looking for. Here's one. Assuming the "5701 Total" label is located in column A:

    Dim lngLastRow As Long
       'Find last row
       lngLastRow = Columns("A:A").Find(What:="5701 Total", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
       'Set print area
       ActiveSheet.PageSetup.PrintArea = "$C$5:$Q$" & lngLastRow

    Note that the print area must be set with an absolute ($) range.

    Re: If then else if VBA

    If you're curious as to why you receive the "Else without If" error, it lies in VBA's limited support for the shorthand If. Specifically, a shorthand If statement (one where the body of the If is contained on the same line as the If itself) cannot contain any ElseIf or Else sections:

    The first line of the second to code groups acts as a shorthand If, then VBA doesn't know what to do with the subsequent Else lines.

    Re: VBcomponent remove before exit

    I'm having the same problem. Excel will not immediately remove any modules where code has recently been executed. For example, given the following modules:


    Private Sub Workbook_Open()
          'Additional code
       End Sub


    Public Sub InitializeGlobals()
       End Sub


    Public Sub RemoveOne()
          Application.VBE.ActiveVBProject.VBComponents.Remove _
       End Sub

    The module is not removed immediately (or in some cases, at all) as you might expect. It seems as though Excel holds onto the code for a while after executing it, delaying the remove command.

    The same is true for a procedure that attempts to remove the module in which it is contained. For example, if a RemoveTwo() procedure was added to the ModuleTwo module, it would not work correctly.

    I would be thrilled if anyone had a workaround for this.

    To anyone interested:

    I've found a solution to this problem. It's not the most elegant, but it does work.

    I have an Excel file (UTILITY) that opens a series of files (PROJECT) one by one. The PROJECT files all contain two macros (each in separate modules):


    Sub Refresh_Update_Code
    '.Remove and .Import Module2
    End Sub


    Sub Refresh_All_Modules
    '.Remove and .Import all modules (except Module2)
    End Sub

    The UTILITY file calls both of these macros in turn, first updating the update module, than updating everything else.

    If anyone knows of a better way, please post.


    I'm trying to automate code changes across a few dozen Excel files, but am having trouble (vbComponents).Remove-ing one of the modules. My only guess as to why this may be happening is because one of the macros in that module is being called on Workbook_Open. Is there any way to:

    1) Disable all macros when opening a project (via VBA) but still allowing access to the code for .Remove/.Import

    2) Force all running/active macros to end

    3) Force a .Remove even on running/active modules

    Thanks in advance.