Excel VBA adding Spaces form Str()

Question

I have the following snipet in Excel 2013/VBA. The code is a very simple random cell/row generator which then displays the output in a text box. In the snippet below Excel/Vba is adding a space between A and the random numebr for example:

"A 14"

where the expected out put was

"A14"

I've gotten around this using the Replace() function, however I'm wondering why this behaviour occurs i the first place. Is it to do with the Str() function?

Private Sub btnRetrieve_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("CenRaw")

Dim k As Long
Dim selectedRow As String

k = sh.Range("A2", sh.Range("A2").End(xlDown)).Rows.Count
aRandNum = Int((k - 2) * Rnd + 2)
selectedRow = Replace("A" & Str(aRandNum), " ", "")
tbOne.Text = selectedRow
selectedRow = vbNullString

End Sub

SierraOscar · Accepted Answer · 2015-04-13 23:51:02Z

3

VBA already has a function for casting to string that doesn't include leading spaces:

tbOne.Text = "A" & CStr(aRandNum)

There are similar functions for casting to other data types too:

CBool() -> Boolean
CInt() -> Integer
CLng() -> Long
CDbl() -> Double
CDate() -> Date
CDec() -> Decimal

to name just a few other examples

Although if you're working with a textbox, the value being assigned is implicitly a string and so the numeric value would show as a string anyway.

answered Apr 13, 2015 at 23:51

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kevin · Accepted Answer · 2015-04-13 19:54:57Z

1

Please see this article on MSDN:

vba str leading spaces

The easier way is to use: LTrim$(aRandNum)

answered Apr 13, 2015 at 19:54

Kevin

2,6311 gold badge14 silver badges12 bronze badges

Collectives™ on Stack Overflow

Excel VBA adding Spaces form Str()

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related