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.

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

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.

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.

That's the understanding I got because I do exactly the same thing (in
concept) but go about it as I explained. It just works!

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

Using this procedure...

Sub Run_It()
Debug.Print LOCATION & ":" & GOTIT
End Sub

...gave me the following in the Immediate Window...

LOCATION = "HOME"
HOME:Home

LOCATION = "AWAY"
AWAY: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.

I also use ini files so my apps are portable, thus not using the
Registry for storage. I use the dummy file approach for setting 'flags'
in my apps so they can evaluate startup status or any other things I
want to use them for. In your scenario you could use the dummy file in
your home folder but not at the away location. Your app will then
automaticially know where it's running.

You could also just use your LOCATION constant as described and
initialize a booean to its value...

bHome = (LOCATION = "HOME")

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

VB[A] isn't liking it; the reason I don't know the answer to. I even
tried 'ElseIf...Then and still nogo! I did get the following results
from this procedure, though...

Sub Run_It()
Dim bHome As Boolean
bHome = (LOCATION = "HOME") '//initialize
Debug.Print "bHome=" & bHome
End Sub

LOCATION = "HOME"
bHome=True

LOCATION = "AWAY"
bHome=False

...which I understand to be the result you want. In the case of away,
LOCATION can be any value or even an empty string!

--
Garry

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

"Spiggy Topes" wrote in message

Everything Gary said but just to add, conditional constants (which you use
within a #If) should be defined in "Conditional compiler constants" which
you'll see if you right click the project name and click properties.

Where I said paste LOCATION = 1, and adapt your #If to simply

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

If LOCATION=1 GOTIT will return Home, if =0 or it doesn't exist "AWAY". For
your purposes the only reason to use this approach is it will always work if
the project is reset for some reason and any global variables get destroyed,
typically for use when debugging projects. Otherwise easier to use one of
the approaches Gary suggested.

Regards,
Peter T

conditional constants (which you use within a #If) should be defined
in "Conditional compiler constants" which you'll see if you right
click the project name and click properties.

Where I said paste LOCATION = 1, and adapt your #If to simply

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

If LOCATION=1 GOTIT will return Home, if =0 or it doesn't exist
"AWAY". For your purposes the only reason to use this approach is it
will always work if the project is reset for some reason and any
global variables get destroyed, typically for use when debugging
projects.

Peter,
Thanks for clarifying this. I don't use this much and so the problem
wasn't as apparent to me as it should have been since I've never
seen...

#If CONST_NAME =...

...in any I have used. Clearly this construct implies a Boolean but it
just didn't 'register' about specifying a value. Just goes to show once
again how unfamiliarity can be more of a problem than code syntax!
That's probably why I adopted a more-easy-to-manage methodology. IMO,
the 'dummy file' approach works best because it doesn't require
user-edit of code (which isn't possible since all my apps block access
to the VBE + there projects are "Not viewable" anyway).

--
Garry

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

"GS" wrote in message

Peter,
Thanks for clarifying this. I don't use this much and so the problem
wasn't as apparent to me as it should have been since I've never seen...

#If CONST_NAME =...

..in any I have used. Clearly this construct implies a Boolean but it just
didn't 'register' about specifying a value. Just goes to show once again
how unfamiliarity can be more of a problem than code syntax! That's
probably why I adopted a more-easy-to-manage methodology. IMO, the 'dummy
file' approach works best because it doesn't require user-edit of code

I guess there are many different ways depending on what's most suitable, eg
username, click a button on the sheet to say who/where you are and set
internal flags accordingly, etc. The conditional constant might also be
easiest in some scenarios.

(which isn't possible since all my apps block access to the VBE + there
projects are "Not viewable" anyway).

It'd be nice if that could really be made secure!

In passing these built-in conditional constants work in the same way under
#If
Mac, Win32, VBA6, VBA7, Win64

Regards,
Peter T

(which isn't possible since all my apps block access to the VBE +
there projects are "Not viewable" anyway).

It'd be nice if that could really be made secure!

It is secure if you do it right! (Not talking about passwords here!)

--
Garry

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

"GS" wrote in message
(which isn't possible since all my apps block access to the VBE + there
projects are "Not viewable" anyway).

It'd be nice if that could really be made secure!

It is secure if you do it right! (Not talking about passwords here!)

I'd be pleased to be wrong but doubt it!

Regards,
Peter T

"GS" wrote in message
(which isn't possible since all my apps block access to the VBE +
there projects are "Not viewable" anyway).

It'd be nice if that could really be made secure!

It is secure if you do it right! (Not talking about passwords
here!)

I'd be pleased to be wrong but doubt it!

Regards,
Peter T

Check this out...

https://app.box.com/s/23yqum8auvzx17h04u4f

...for filename "ComboBoxHelp_VBA_Locked.xls".

--
Garry

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

"GS" wrote in message
"GS" wrote in message
(which isn't possible since all my apps block access to the VBE +
there projects are "Not viewable" anyway).

It'd be nice if that could really be made secure!

It is secure if you do it right! (Not talking about passwords here!)

I'd be pleased to be wrong but doubt it!

Regards,
Peter T

Check this out...

https://app.box.com/s/23yqum8auvzx17h04u4f

..for filename "ComboBoxHelp_VBA_Locked.xls".

OK I'm pleased to be wrong :-)

When you said not the pw I guessed you meant JKP's which similarly won't
even let you enter a pw, but that's easy to circumvent.

?

Regards,
Peter T

"GS" wrote in message
"GS" wrote in message
(which isn't possible since all my apps block access to the VBE
+ there projects are "Not viewable" anyway).

It'd be nice if that could really be made secure!

It is secure if you do it right! (Not talking about passwords
here!)

I'd be pleased to be wrong but doubt it!

Regards,
Peter T

Check this out...

https://app.box.com/s/23yqum8auvzx17h04u4f

..for filename "ComboBoxHelp_VBA_Locked.xls".

OK I'm pleased to be wrong :-)

When you said not the pw I guessed you meant JKP's which similarly
won't even let you enter a pw, but that's easy to circumvent.

?

Regards,
Peter T

Thanks for confirming I've finally found a good resolve for this
long-standing issue! Not sure what 'tools' you used to attempt
'breaking in' but none of mine could do it either.<g

--
Garry

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

"GS" wrote in message

Thanks for confirming I've finally found a good resolve for this
long-standing issue! Not sure what 'tools' you used to attempt 'breaking
in' but none of mine could do it either.<g
Garry

What tools - only a little macro of Karl P's and what I was half expecting
would be a one liner to fix what I thought you had done. So don't take my
feedback as a conclusive! Willing to share offline :-)

Regards,
Peter T

"GS" wrote in message

Thanks for confirming I've finally found a good resolve for this
long-standing issue! Not sure what 'tools' you used to attempt
'breaking in' but none of mine could do it either.<g
Garry

What tools - only a little macro of Karl P's and what I was half
expecting would be a one liner to fix what I thought you had done. So
don't take my feedback as a conclusive! Willing to share offline :-)


Must be from 'private stock' since it's not listed/mentioned on his
website. Not sure how to proceed with offline sharing, though, since
last time I posted a hint to my email that got 'vandalized' shortly
thereafter. Is there any chance we can get Rob Bovey to forward emails?

--
Garry

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

"GS" wrote in message

Must be from 'private stock' since it's not listed/mentioned on his
website. Not sure how to proceed with offline sharing, though, since last
time I posted a hint to my email that got 'vandalized' shortly thereafter.
Is there any chance we can get Rob Bovey to forward emails?
Garry

It's a long time since I was last in contact with Rob, but you remind me as
a sort of tribute I adapted (and credited) one of his old routines in this
treeview demo

http://www.jkp-ads.com/Articles/treeview02.asp

"Download The Extended Project Explorer". My address is in most of the
headers.

If you prefer not to download the file I used to occasionally post my email
in this group, like you I hesitate to do so again but could probably find
and refer you to an old post which includes it!

Regards,
Peter T