Is there a VBE constant for NBSP (Chr 160)?
 

Thanks,
George

Is there a VBE constant for NBSP (Chr 160)?
 

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

Is there a VBE constant for NBSP (Chr 160)?
 

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

Is there a VBE constant for NBSP (Chr 160)?
 

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

Is there a VBE constant for NBSP (Chr 160)?
 

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

Is there a VBE constant for NBSP (Chr 160)?
 

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

Is there a VBE constant for NBSP (Chr 160)?
 

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

Is there a VBE constant for NBSP (Chr 160)?
 

Right.


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