VBA – Number Sign usage (2003)

Topic

New Reply

Hi all,

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

#If FullRecalc Then
Application.Volatile
#End If

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

#Const FullRecalc = False

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

Reply | Quote

Replies

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

Reply | Quote

Hello Hans,

Thank you for such a clear and prompt explanation.[indent]

---

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.

---

[/indent]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?[indent]

---

You can also force compilation of all code in a project (workbook) by selecting Debug | Compile .

---

[/indent]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:

Function SelectedFile() As String
Dim fd As FileDialog

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?

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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, ware, which allows Excel to interact with R, which I mentioned in this post.

Reply | Quote

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

Reply | Quote