Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Option Explicit Off or 'Require Variable Declaration' does not work under excel 2007

  1. #1
    Join Date
    16th November 2010
    Posts
    3

    Option Explicit Off or 'Require Variable Declaration' does not work under excel 2007

    Hello all,

    I recently upgraded from Excel 2003 to 2007 (Basic - English).

    Now I have a lot of excel forms/files with macro's written in VBA.

    Of course I should have declared the variables from the start but a lot of marco's have been written without it. As long as I uncheck 'Require Variable Declaration', I could run these macro's without an flows or fails.

    Running these marco's under 2007 immediately started the debugger telling me that => Compile error: Can't find project or library.
    To see if it was a declarartion fail I started declaring some variables => seem to be the issue.

    For now I thought: Unchecking 'Require Variable Declaration' in 2007 and I can go on.
    Unfortunatelly .... this does not do ANYTHING!!!!!! (same fail message at start of macro)

    Adding the line 'Option Explicit Off' at the beginning of my module does also nothing!!!!
    Strange thing: if I type 'Option Explicit' it accepts the line but if I add 'On' or 'Off' it starts complaining with the following message => Compile Error: Expected end of statement

    What could be wrong?

    For an exercise I removed a lot of the macro's and declared all variables who were left over => new error message => it seems to be complaining about the functions like Right, Left etc. The standard excel functions.
    Question 2: Should these build-in excel functions be addressed with ... Application.Right(...) ??
    IS this new in 2007 or can I disable the need for this direct addresssing?
    (I ask this because 2003 does not complain)

    Any info would be appreciated.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st September 2010
    Posts
    7,625

    Re: Option Explicit Off or 'Require Variable Declaration' does not work under excel

    This is nothing to do with Option Explicit or variables. You are missing a reference to an external library - Probably 'Visual Basic For Applications'.

    I don't have 2007 to hand, but in 2003, you'd access the list of referenced libraries using the 'Tools/References' menu. One, or more, of them will be labelled as 'Missing' ...

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    16th November 2010
    Posts
    3

    Re: Option Explicit Off or 'Require Variable Declaration' does not work under excel

    Quote Originally Posted by cytop View Post
    ....I don't have 2007 to hand, but in 2003, you'd access the list of referenced libraries using the 'Tools/References' menu. One, or more, of them will be labelled as 'Missing' ...
    I have the following references:
    Visual Basic For Applications, Microsoft Excel 12.0 Object Library, OLE Atomation, Microsoft Office 12.0 Object Library, atpvbaen.xls, Microsoft Forms 2.0 Object Library.
    Adding VBAProject is not possible => pops up with the message: Name conflicts with existing module, or object library.

    What am I missing?

    Edit 17-11: Meanwhile I checked the program at another place under Excel 2007 => to my suprise ... it works.
    What could still be wrong with my setup or installation?
    (Both systems have 2007 next to 2003 installed => seams to be the same starting point)
    (also checked on a system where I did a fresh installation of only 2007 => Same fails as before)
    Last edited by Kimball; November 17th, 2010 at 16:27.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th November 2010
    Posts
    3

    Re: Option Explicit Off or 'Require Variable Declaration' does not work under excel

    Solved.

    I was looking at the reference on the working Excel 2007 system.
    I was indeed missing atvbaen.xla => cleared it and it worked.
    (reference to atvbaen.xls (atvbaen.xla) still present which is correct)

    Close

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Option Explicit
    By robert-007 in forum EXCEL HELP
    Replies: 4
    Last Post: May 3rd, 2007, 15:34
  2. Option Explicit
    By Tiffany in forum EXCEL HELP
    Replies: 12
    Last Post: February 20th, 2007, 10:36
  3. Option Explicit
    By abbeville in forum EXCEL HELP
    Replies: 1
    Last Post: June 11th, 2005, 20:10
  4. Option Explicit
    By dacat1997 in forum EXCEL HELP
    Replies: 5
    Last Post: June 1st, 2005, 10:16
  5. Option Explicit
    By Sahak in forum EXCEL HELP
    Replies: 2
    Last Post: April 9th, 2005, 15:41

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno