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
    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
    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!