length of 1,000,000 is 8

[yky]

I passed a numeric value of 1,000,000 to a sub and checked the length of it. This is what I got in the Immediate window.

Sub income_RG(num As Double)
Debug.Print num              <-gives 1000000 with a trailing space
num = Trim(num)
Debug.Print num              <-gives 1000000 with a trailing space
Debug.Print Len(num)         <- gives 8
Debug.Print Len(Trim(num))   <- gives 7
Debug.Print Len(num)         <-give 8
......

It seems the effect of TRIM is short-lived. Somehow Excel appends a space after num right away. What's going on here? How do I prevent Excel from adding the trailing space?

Further test shows this:

Debug.Print num&a         <gives 1000000a

There doesn't seem to be a trailing space (though it does have one in the Immediate window). So, why is the length 8, not 7?

 

[DanteAmor]

For numbers, len() returns the storage size of the variable type.

Dante Amor
	G	H
1	Type	Storage
2	Byte	1 byte
3	Integer	2 bytes
4	Long	4 bytes
5	Single	4 bytes
6	Double	8 bytes
Sheet4

Try this

Sub test2()
  Dim numB As Byte
  Dim numI As Integer
  Dim numL As Long
  Dim numS As Single
  Dim numD As Double
  numB = 100
  numI = 100
  numL = 100
  numS = 100
  numD = 100
  Debug.Print Len(numB) 'gives 1
  Debug.Print Len(numI) 'gives 2
  Debug.Print Len(numL) 'gives 4
  Debug.Print Len(numS) 'gives 4
  Debug.Print Len(numD) 'gives 8
  
  Debug.Print Len(Trim(numD)) 'gives 3 Text length "100"
End Sub

----------------------------------

There doesn't seem to be a trailing space (though it does have one in the Immediate window). So, why is the length 8, not 7?

There are no spaces, it is the storage size of the variable type.
If you convert it to text, for example using the Trim() function, then you can see the number of digits of the number.

 

[yky]

Thanks. That explains it.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.