Public Constant not visible in worksheet?

[Clif McIrvin]

Office / Excel 2003 SP3
XP Pro SP3

I'm writing some code that I'd like to be available to every worksheet
in the workbook, and for a variety of reasons I'm trying to avoid using
PERSONAL.XLS and/or addins. [The object of the exercise is to allow any
user within the organization to be able to utilize the macros in this
workbook as long as (s)he has a standard Office installation.]

I've written sub procedures in a standard module in the workbook /
project that are called from a Worksheet_Changed event .... the code
works as expected.

My problem is when I attempted to replace "magic numbers" in my code
with public constants: The "Public Const ..." statements are in the
declarations section of my standard module; but the constants are not
visible to the worksheet (event) code.

What am I missing?

 

[David Heaton]

Office / Excel 2003 SP3
XP Pro SP3

I'm writing some code that I'd like to be available to every worksheet
in the workbook, and for a variety of reasons I'm trying to avoid using
PERSONAL.XLS and/or addins. [The object of the exercise is to allow any
user within the organization to be able to utilize the macros in this
workbook as long as (s)he has a standard Office installation.]

I've written sub procedures in a standard module in the workbook /
project that are called from a Worksheet_Changed event .... the code
works as expected.

My problem is when I attempted to replace "magic numbers" in my code
with public constants:  The "Public Const ..." statements are in the
declarations section of my standard module; but the constants are not
visible to the worksheet (event) code.

What am I missing?

Cliff,

what do you mean the constants are 'not visible'

I have ran a test with
Public Const ATest="Test"
in a module,

then in the worksheet activate event.
Range("a1")=ATest

This worked just fine.

It might be something to do with the code in the Worksheet_Change
event,
so maybe you could post that.

Regards

David

 

[Clif McIrvin]

Office / Excel 2003 SP3
XP Pro SP3

I'm writing some code that I'd like to be available to every worksheet
in the workbook, and for a variety of reasons I'm trying to avoid
using
PERSONAL.XLS and/or addins. [The object of the exercise is to allow
any
user within the organization to be able to utilize the macros in this
workbook as long as (s)he has a standard Office installation.]

I've written sub procedures in a standard module in the workbook /
project that are called from a Worksheet_Changed event .... the code
works as expected.

My problem is when I attempted to replace "magic numbers" in my code
with public constants: The "Public Const ..." statements are in the
declarations section of my standard module; but the constants are not
visible to the worksheet (event) code.

What am I missing?

Cliff,

what do you mean the constants are 'not visible'

I have ran a test with
Public Const ATest="Test"
in a module,

then in the worksheet activate event.
Range("a1")=ATest

This worked just fine.

It might be something to do with the code in the Worksheet_Change
event,
so maybe you could post that.

Regards

David


Hmm.
Here's some sample code that follows the same structure as my other
code -- and this works, so I've got more digging to do.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case Addy
MsgBox "Addy is Visible"
Case Else
MsgBox "Addy is not visible"
End Select
End Sub

and in the module:

Option Explicit

Public Const Addy As String = "$A$1"

I may not be able to get back to this today .... but I did discover at
least one typo <he said with red face>

Thanks for the response!

 

[Clif McIrvin]

I may not be able to get back to this today .... but I did discover at
least one typo <he said with red face>

Thanks for the response!

Yup ... I ferreted out all the typos and all is well. Too late at night
and not enough attention to detail, I guess [as well as mising an Option
Explicit that would have thrown compiler errors!]

David, thanks again for your time.

 

[David Heaton]

news:2cec021f-c3b5-4928-81c3-3b0a03168882@r36g2000vbr.googlegroups.com....

I may not be able to get back to this today .... but I did discover at
least one typo <he said with red face>

Thanks for the response!

Yup ... I ferreted out all the typos and all is well. Too late at night
and not enough attention to detail, I guess [as well as mising an Option
Explicit that would have thrown compiler errors!]

David, thanks again for your time.

your welcome Cliff

regards

David