On Wednesday, May 7, 2014 3:52:42 PM UTC-7, GS wrote:
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

Think you misunderstood slightly. The plan was to use the code as originally quoted and to have one user change the value of Const LOCATION - which would mean that all differences between the two sites could be accommodated within the source, and switching for one location or the other would be a simple matter of changing
Const LOCATION = "HOME"
to
Const LOCATION = "AWAY"
or vice versa.

Try it. Paste the code into a module, run it with each of these variations and see what you get. If you get the same as me, both will result in "HOME" and "Home".

I have other options - I already use an .ini file to control execution, and could add another parameter in there, for instance (except that that would mean reworking file locations), or I could add a command line argument and control it that way (except it gets ugly because I'd have to pass it from task scheduler to outer macro workbook to inner macro workbook to inner-inner workbook - crazy restrictions on where macros can be run from). Or, as you say, a simple file in the same directory as the workbook that could be checked for existence.

But I'd just like to know why the code I started from doesn't work.