Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA - Number Sign usage (2003)

    Hi all,

    I was reading through an open source add-in's code when I stumbled upon this block:

    <code> #If FullRecalc Then
    Application.Volatile
    #End If</code>

    I had never seen a number sign preceding "If" or "End if". FullRecalc, in turn, is defined as:

    <code> #Const FullRecalc = False</code>

    Searching a little, I found this.

    However, I cannot figure what "compile" would mean in Excel. Can VBA code be compiled in some way?
    In particular, I was perusing the code to check whether a certain UDF was volatile or not. After reading this block, I still don't know if Application.Volatile is included only in case "the code is compiled" or if the function is already volatile even when FullRecalc = False.

    Thank you
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA - Number Sign usage (2003)

    VBA in Office is a "compile on demand" language. The first time code is run after being created or modified, VBA "compiles" it, i.e. creates a list of the entry points and jumps in the code; this speeds up subsequent execution. With small modules you won't notice the difference, but if you write a substantial piece of code, you may find that the first time you run it is significantly slower than the next time(s).

    You can also force compilation of all code in a project (workbook) by selecting Debug | Compile <project name>. This is useful because you'll be notified if there are syntax errors (preventing compilation) and because all code will already be compiled if there are no syntax errors.

    If you specify the value of the compilation constant FullRecalc as False, the line Application.Volatile will be ignored next time you run the code.
    If you then edit the code so that FullRecalc is defined as True, you force VBA to recompile the code next time you run it, because it has been modified. This time, the line Application.Volatile will be evaluated.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - Number Sign usage (2003)

    Hello Hans,

    Thank you for such a clear and prompt explanation.
    <hr>The first time code is run after being created or modified, VBA "compiles" it, i.e. creates a list of the entry points and jumps in the code; this speeds up subsequent execution.<hr>
    Just out of curiosity: does VBA/Excel store this list somewhere when the workbook is saved, or does it create the list upon every first run of the code after opening the file?
    <hr>You can also force compilation of all code in a project (workbook) by selecting Debug | Compile <project name>.<hr>
    I tried to compile the add-in's project, but got this error prompt: "Compile error: User-defined type not defined"

    As a result, the second line gets highlighted:

    <code> Function SelectedFile() As String
    Dim fd As FileDialog</code>

    Doing a whole project search, I couldn't find a definition for this FileDialog type. In fact, if I use =SelectedFile() in any cell, I get the same error. However, all the other add-in UDFs I tried work just fine. This makes me think the code is compiled and the resulting list is stored in the workbook, because if VBA tried to compile the code on every first run, I'd get the same error message. Right?
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA - Number Sign usage (2003)

    Do you have a reference (in Tools | Options...) to the Microsoft Office 11.0 Object Library? This library is necessary to work with FileDialog.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - Number Sign usage (2003)

    Certainly not. The subject indication of Excel 2003 refers to the version I have at work (where I made the original post). At home (where I tried to compile the project) I'm using Excel 2000, which, as far as I can see, can only add a reference to the Microsoft Office 9.0 Object Library. Couldn't find a checkbox for 11.0.

    Let me try to check at work (won't be there until Wednesday). And thanks for all your help! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA - Number Sign usage (2003)

    You should use the version number of the Office version you have on your PC:
    8.0 = Office 97
    9.0 = Office 2000
    10.0 = Office XP aka 2002
    11.0 = Office 2003
    12.0 = Office 2007

  7. #7
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA - Number Sign usage (2003)

    Hi Hans,

    Finally at work. I could compile the project here (Excel 2003). However, I failed at home (Excel 2000, including a VBA reference to the Microsoft Office 9.0 Object Library). How weird... at least it worked at work.

    BTW, the add-in I'm talking about is RExcel, <img src=/S/free.gif border=0 alt=free width=30 height=15>ware, which allows Excel to interact with R, which I mentioned in this post.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA - Number Sign usage (2003)

    Ah yes, I'm sorry. FileDialog was introduced in Office XP (2002), it is not available in Office 2000.

Posting Permissions

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