I'm using Excel as a flat database for my website data file, and a VBA
routine to output the html pages for me. So far, so good.

I came across some strange behaviour last night, which I had to use an
ugly hack to get around - the weirdness is demonstrated by:

Sub printError()
Dim MyNumber As Integer
MyNumber = 3*7
Debug.Print "This thing is "; MyNumber; "mm long"
End Sub

This will print "This thing is 21 mm long". In the code I'm outputting,
the space between the number and the following letters is invalid.

I've since discovered that declaring MyNumber as a string will still
allow me to set it to the value of a calculation(?!), and that the space
is then omitted.

Any ideas why this would be?

TIA

Nik

Yes. A space is placed on each end of a number as part of its inherent
formatting.

If you look closely, if you just debug.print a number, it starts in the
second column.

Debug.Print 3 ' starts in second column.

--
Regards,
Tom Ogilvy



"Nik" wrote in message
...
I'm using Excel as a flat database for my website data file, and a VBA
routine to output the html pages for me. So far, so good.

I came across some strange behaviour last night, which I had to use an
ugly hack to get around - the weirdness is demonstrated by:

Sub printError()
Dim MyNumber As Integer
MyNumber = 3*7
Debug.Print "This thing is "; MyNumber; "mm long"
End Sub

This will print "This thing is 21 mm long". In the code I'm outputting,
the space between the number and the following letters is invalid.

I've since discovered that declaring MyNumber as a string will still allow
me to set it to the value of a calculation(?!), and that the space is then
omitted.

Any ideas why this would be?

TIA

Nik

Tom Ogilvy wrote:
Yes. A space is placed on each end of a number as part of its inherent
formatting.

If you look closely, if you just debug.print a number, it starts in the
second column.

Debug.Print 3 ' starts in second column.

So it does - I hadn't noticed the double space before the number in my
example.

I don't suppose that there is any way to over-ride this that is more
elegant than declaring what should be an integer as a string?

Nik

Sub printError()
Dim MyNumber As Long
MyNumber = 3*7
Debug.Print "This thing is " & format(MyNumber,"#") & "mm long"
End Sub


You save nothing using Integer instead of Long - in fact, I believe
internally, VBA converts it to long to work with it.

--
Regards,
Tom Ogilvy



"Nik" wrote in message
...
Tom Ogilvy wrote:
Yes. A space is placed on each end of a number as part of its inherent
formatting.

If you look closely, if you just debug.print a number, it starts in the
second column.

Debug.Print 3 ' starts in second column.

So it does - I hadn't noticed the double space before the number in my
example.

I don't suppose that there is any way to over-ride this that is more
elegant than declaring what should be an integer as a string?

Nik