I have a series of macros stored in one module which I need to make
as common as possible for execution in two locations. File locations
will differ from one to the other, and I'd like to set it up so that
all necessary changes can be made with just one line change.

Naturally, I thought of using compiler directives to bracket the
constants that are used for directory names, and came up with this
code:

Const LOCATION = "AWAY"

Const AT_HOME = "HOME"
Const AT_AWAY = "AWAY"

#If LOCATION = AT_HOME Then
Const GOTIT = "Home"
#Else
Const GOTIT = "Away"
#End If

Sub Run_It()
MsgBox LOCATION
MsgBox GOTIT
End Sub

...but it doesn't work, and I can't see why. Any setting of LOCATION
returns "Home" in GOTTIT. "LOCATION = AT_HOME" always evaluates as
True.

Same behaviour under Windows 7 and Vista, using Excel 2007 or 2010.

Any idea why this happens? I guess I can fix by replacing constants
with global variables, but I'd like to know why it doesn't work this
way.

Because LOCATION is fixed value and so must be manually changed. Either
use just...

Const LOCATION$ = "AWAY" 'OR HOME

...and have your code ref it at runtime, -OR- use...

Const AT_HOME$ = "HOME"
Const AT_AWAY$ = "AWAY"

Public gsLocation$

...and have your startup code initialize the latter to 1 of the 2
constants...

gsLocation = AT_HOME 'or AT_HOME

...and use it for conditional code execution.

Personally, I'd use a Boolean global variable based on if a file exists
in the workbook path, and just initialize it at startup...

Declaration:
Public bHome As Boolean

Initialize at startup:
bHome = Dir(ThisWorkbook.Path & "\dummy.dat") < ""

...so when you're away just rename the file "_dummy.dat" to have the
variable bHome = False. So your code, then, can use it as follows...

If bHome Then DoThis Else DoThat

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion