Thanks,
George

I don't think so. Why not just declare it in your code?
--ron

There is no built in constant for that character (constants exist only
for chars 1-127 with a few exceptions). You can declare a Public
variable to hold the character.

' In Module1
Public NBSP As String

' In ThisWorkbook
Private Sub Workbook_Open()
NBPS=Chr(160)
End Sub

Another way, which is in fact better, is to use Property in Module1
(yes, normal code modules can have properties).

Public Property Get NBSP() As String
NBSP = Chr(160)
End Property

Then, in another procedu

Dim S As String
S = "abc" & NBSP & "def"
Dim N As Long
N = InStr(1, S, NBSP)
Debug.Print N 'Displays 4

The advantage of using a property rather than a public variable is
that if your code terminates with an error or an End statement is
encountered, or VBA just decides to recompile the project, Public
variables are dumped, so the public variable would revert to an empty
string, no longer containing Chr(160). With a property as shown above,
it will always return Chr(160) regardless of what VBA decides to do.




Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Mon, 15 Feb 2010 21:21:51 -0700, "G Lykos"
wrote:

Thanks,
George

Another way, or as I think Ron was suggesting

Public Const NBSP As String = " " ' chr(160)

Sub test()
Debug.Print Asc(NBSP) ' 160
End Sub

I got the space like character between the quotes from this in the
Intermediate window
?chr(160)

Regards,
Peter T



"G Lykos" wrote in message
...
Thanks,
George

Interesting, I definitely posted the constant as a chr(160), but it appears
as a normal space chr(32). So don't simply copy/paste the example, make your
own along the lines suggested.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Another way, or as I think Ron was suggesting

Public Const NBSP As String = " " ' chr(160)

Sub test()
Debug.Print Asc(NBSP) ' 160
End Sub

I got the space like character between the quotes from this in the
Intermediate window
?chr(160)

Regards,
Peter T



"G Lykos" wrote in message
...
Thanks,
George

Thanks for the guidance!


"Chip Pearson" wrote in message
...
There is no built in constant for that character (constants exist only
for chars 1-127 with a few exceptions). You can declare a Public
variable to hold the character.

' In Module1
Public NBSP As String

' In ThisWorkbook
Private Sub Workbook_Open()
NBPS=Chr(160)
End Sub

Another way, which is in fact better, is to use Property in Module1
(yes, normal code modules can have properties).

Public Property Get NBSP() As String
NBSP = Chr(160)
End Property

Then, in another procedu

Dim S As String
S = "abc" & NBSP & "def"
Dim N As Long
N = InStr(1, S, NBSP)
Debug.Print N 'Displays 4

The advantage of using a property rather than a public variable is
that if your code terminates with an error or an End statement is
encountered, or VBA just decides to recompile the project, Public
variables are dumped, so the public variable would revert to an empty
string, no longer containing Chr(160). With a property as shown above,
it will always return Chr(160) regardless of what VBA decides to do.




Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Mon, 15 Feb 2010 21:21:51 -0700, "G Lykos"
wrote:

Thanks,
George

Thanks!


"Peter T" <peter_t@discussions wrote in message
...
Interesting, I definitely posted the constant as a chr(160), but it
appears as a normal space chr(32). So don't simply copy/paste the example,
make your own along the lines suggested.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Another way, or as I think Ron was suggesting

Public Const NBSP As String = " " ' chr(160)

Sub test()
Debug.Print Asc(NBSP) ' 160
End Sub

I got the space like character between the quotes from this in the
Intermediate window
?chr(160)

Regards,
Peter T



"G Lykos" wrote in message
...
Thanks,
George

Right.


"Ron Rosenfeld" wrote in message
...
I don't think so. Why not just declare it in your code?
--ron