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