VB (in Excel): Why does "A" + Str(5) result in "A 5" and not "A5"?

Question

Presently, this one works, but there must be an easier way?!

Dim z As Integer
Dim rge As String
Dim rgestr As String
Dim shortened As String
Dim howmany As Integer
'
z = 5
rgestr = Str(z)
howmany = Len(rgestr)
shortened = Right(z, howmany - 1)
rge = "A" + shortened
Range(rge).Select
'
' and so on ........

Clarification: I want to address a cell in Excel using an indirect method because I will use a loop.
The problem is that "A"+str(5) causes a space between the A and the 5. (btw: using & instead of + has the same result.

Answer 1

You don't need to make z into a String. Just put directly into the formula as shown below. In this formula, cell A5 will be set to 30.

Dim z As Integer
z = 5
Range("A" & z).Value = 30
End Sub

Answer 2

People gave solutions, but nobody said why.

It goes back to the days of Megasloth's Quickbasic. They and they alone in their "infinite wisdom" (pffft!) decided to always leave a blank space in front of positive numbers. No other BASIC interpreters or compilers did this.

It was done so that positive and negative numbers would require the same number of characters to display a number and that decimals would line up. The problem is, though, it's harder to remove a blank from a number than it is to add one. They should have left it to programmers to add a space *if* it is needed. Most of the time, you don't want it, and programmers were left to contend with the mess.

Obviously, Mr. Bill decided to carry over his stupidity from QuickBasic to Windows....

Answer 3

cant use + use an & instead

rge= "A" & shortened

i am also a little confuse on what you are trying to do? are you removing spaces or just taking the last character from a string?

Answer 4

Use & with strings and Trim Function to remove leading and ending spaces.

rge="A" & trim(rgestr)

Answer 5

instead of using 'A'+ Str(5) use:

'A' + Trim( Str(5) )