Weird Behaviour of Compiler Directives?

Discussion in 'Excel Programming' started by Spiggy Topes, May 7, 2014.

  1. Spiggy Topes

    Spiggy Topes Guest

    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.
     
    Spiggy Topes, May 7, 2014
    #1
    1. Advertisements

  2. Spiggy Topes

    GS Guest

    > 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
     
    GS, May 7, 2014
    #2
    1. Advertisements

  3. Spiggy Topes

    Spiggy Topes Guest

    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, andcould add another parameter in there, for instance (except that that wouldmean reworking file locations), or I could add a command line argument andcontrol 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.
     
    Spiggy Topes, May 8, 2014
    #3
  4. Spiggy Topes

    GS Guest

    > 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
     
    GS, May 8, 2014
    #4
  5. Spiggy Topes

    Peter T Guest

    "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
     
    Peter T, May 8, 2014
    #5
  6. Spiggy Topes

    GS Guest

    > 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, May 8, 2014
    #6
  7. Spiggy Topes

    Peter T Guest

    "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
     
    Peter T, May 9, 2014
    #7
  8. Spiggy Topes

    GS Guest

    >> (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, May 9, 2014
    #8
  9. Spiggy Topes

    Peter T Guest

    "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
     
    Peter T, May 9, 2014
    #9
  10. Spiggy Topes

    GS Guest

    > "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, May 9, 2014
    #10
  11. Spiggy Topes

    Peter T Guest

    "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
     
    Peter T, May 10, 2014
    #11
  12. Spiggy Topes

    GS Guest

    > "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, May 10, 2014
    #12
  13. Spiggy Topes

    Peter T Guest

    "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
     
    Peter T, May 11, 2014
    #13
  14. Spiggy Topes

    GS Guest

    > "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, May 11, 2014
    #14
  15. Spiggy Topes

    Peter T Guest

    "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
     
    Peter T, May 12, 2014
    #15
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Gareth Thackeray

    Weird range property behaviour

    Gareth Thackeray, Nov 1, 2004, in forum: Excel Programming
    Replies:
    5
    Views:
    74
    Gareth Thackeray
    Nov 2, 2004
  2. Matt

    Weird Cell Behaviour

    Matt, Oct 17, 2005, in forum: Excel Programming
    Replies:
    14
    Views:
    161
  3. Trefor

    Weird AddIn behaviour

    Trefor, Sep 6, 2006, in forum: Excel Programming
    Replies:
    18
    Views:
    101
    Peter T
    Sep 15, 2006
  4. Carlo

    Weird Behaviour of Code

    Carlo, Nov 8, 2006, in forum: Excel Programming
    Replies:
    11
    Views:
    109
    Carlo
    Nov 9, 2006
  5. Mike

    Excel 2003 - Weird behaviour in VBA editor

    Mike, Jul 25, 2007, in forum: Excel Programming
    Replies:
    6
    Views:
    72
  6. medic

    weird behaviour from combo box

    medic, Apr 15, 2008, in forum: Excel Programming
    Replies:
    2
    Views:
    156
    pm0203
    Jun 6, 2008
  7. sharon

    Weird Behaviour Function not invoked

    sharon, Jan 8, 2009, in forum: Excel Programming
    Replies:
    3
    Views:
    73
    sharon
    Jan 9, 2009
  8. JLR-Mart

    msgbox weird behaviour

    JLR-Mart, Feb 2, 2009, in forum: Excel Programming
    Replies:
    5
    Views:
    82
    JLGWhiz
    Feb 3, 2009
Loading...