What does a hash mark (#) in VBA?

[JTBorton]

Excel 2003 Prof.
I have been looking at some old code. A lot of it has a hash marks (#) in front of conditional statements and constant decelerations such as:

#Const Tracing = True
#Const Debugging = True
#Const UseEventLog = False

#If UseEventLog Then
    ...stuff...
#End If

or 

#If [Condition] Then
    ...stuff...
#Else
    ...stuff...
#End If

Some of it comes out of a book and some of it I am seeing in excel. I can't find any documentation explaining what this # is doing. In some places it surrounds an entire functions. What is the # is doing?

-JTBorton
If it isn't broken, it doesn't have enough parts yet.

 

[ettienne]

# = conditional compile.
I'm not sure what use this has in VBA since you do not compile VBA code. In VB it is used to compile constants or sections of code if a condition is true.

 

[JTBorton]

so why would you want to do this?

-JTBorton
If it isn't broken, it doesn't have enough parts yet.

 

[PHV]

why would you want to do this?
To have an application working whichever platform (eg Win16,win32,mac,...)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TonyJollans]

It is something you would normally use in debugging or, perhaps, for running code in different environments - and, yes, VBA is compiled (I think it's referred to as 'just in time compilation' but it is compiled rather than interpreted)

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[JTBorton]

so do you only place it in conditional statements and constant decelerations, or could I place it in front of every line of code? Why do you need to compile in this way, doesnt excel already compile it?

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[combo]

The structure is #If...#Else...#End If. '...' means regular code. All in False condition is not compiled. See 'conditional compilation' in vba help.
You can define conditional compilation arguments after Tools>[VBAProject] Properties..., 'General' tab.
BTW, I've seen that it was used for commenting:

#If False Then
This is an example of red comment.
All lines between # signs are red
- it means syntax error
but you can compile project and execute it.
#End If

combo

 

[TonyJollans]

Suppose you wanted to print some diagnostics when testing, and the code for this was scattered all over the place. When you went live you would probably want to comment out or delete this code but that would be an error-prone manual edit. If you coded something like this:

[blue]#Const Debugging = 1
Sub Whatever()
    [green]' Your code here[/green]
    #If Debugging = 1 then
        Msgbox "I am debugging"
    #End If
    [green]' some more of your code here[/green]
End Sub[/blue]

you would see your msgbox (and any others similarly coded). Now with a single change to:

[blue]#Const Debugging = 0[/blue]

all of your debugging code would still be in place in case you needed it again, but it would not be compiled and your customers would not see your debugging messages.

You could, of course, do this without using conditional compilation, but the resultant code would not be as efficient. Compiled code is always there, even if conditions mean it doesn't actually run. Using conditional compilation, the code is not compiled and not run.

Another use, some years ago, admittedly, was with new VBA statements, for example [blue][tt]Split[/tt][/blue], which came in with VBA6 in Office 2000, was not available in Office 97. If you coded a [tt]Split[/tt] statement on your Office 2000 system, and your code was then used on an Office 97 system, it would give a compile error. You could, though, use:

[blue]#If VBA6 Then
    A = Split(B)
#Else
    [green]' some other code for 97[/green]
#End If[/blue]

and your code would then compile on all systems, although it would be doing somethiung different at execution time.

Or, if you want to do something different on a Mac from a Windows system, then you could use:

[blue]#If Mac then
    [green]' Do whatever on a Mac[/green]
#Else
    [green]' Do whatever on a Windows system[/green]#End if[/blue]

There are two other constants, [blue][tt]Win16[/tt][/blue] and [blue][tt]Win32[/tt][/blue], somewhat dated now. It is possible that there will be a new one for 64-bit systems (I don't know, but I hope so).


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website