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:


    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:


    Code
    Private Sub Workbook_Open()
       Application.Run("MacroInSomeModule")
    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:


    Code
    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:



    ThisWorkbook

    Code
    Private Sub Workbook_Open()
          InitializeGlobals
          RemoveOne
          'Additional code
       End Sub


    ModuleOne

    Code
    Public Sub InitializeGlobals()
          'Code
       End Sub


    ModuleTwo

    Code
    Public Sub RemoveOne()
          Application.VBE.ActiveVBProject.VBComponents.Remove _
                Application.VBE.ActiveVBProject.VBComponents.Item("ModuleOne")
       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):


    Module1:

    Code
    Sub Refresh_Update_Code
    '.Remove and .Import Module2
    End Sub


    Module2:

    Code
    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.


    bgano

    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.


    bgano