ActiveWorkbook

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Whenever I use the property "ActiveWorkbook" in VB whether entered directly or by using the Macro recorder I receive an error message. Has anybody any idea why this should be?

    What do you get if you run this bit of code?


    [vba]Sub testWB()
    Dim wbACT As Workbook
    Dim strWB As String


    Set wbACT = ActiveWorkbook
    strWB = wbACT.Name
    MsgBox "" & strWB
    End Sub[/vba]

    ActiveWorkbook


    Error message reads Compile error: Expected function or variable.


    I have used
    myBook = ActiveWorkbook.Name


    ActiveWorkbook Close


    and each produces the same message

    OK, so MyBook is therefore a String right ?


    So you'd have to use it as


    [vba]Sub myBook()
    Dim myBook As String
    myBook = ActiveWorkbook.Name
    Workbooks(myBook).Close
    End Sub[/vba]


    If you want to refer to the Workbook Object you'd need to Dim myBook as a Workbook Object i.e


    [vba]Sub myBook()
    Dim myBook As Workbook
    Set myBook = ActiveWorkbook
    myBook.Close
    End Sub[/vba]


    Does this help.... It sounds as if you've been mixing & matching your variables...

    My advice would be to stick the words Option Explicit at the top of each of your modules (or in the VBE, go to Tools|Options|Editor & check the box labelled "Require Variable Declaration")


    This will force you to properly reference & dimension your variables & avoid errors such as this - it will also make your code easier to debug

    WillR


    everything you say is true, nonetheless whenever and wherever the code element "ActiveWorkbook" appears the error message follows. This occurs only on my laptop, my PC does not suffer the same problem. Is there a way of re-installing Excel/VB to recover what can only be a problem in the basic program.

    Before doing that, & just to satisfy my curiosity


    paste the following in the VBE Immediate window on you laptop (with any workbook open) & hit enter


    [vba]msgbox ""& ActiveWorkbook.Name[/vba]


    What's the exact error messgae you get?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login