VBA: The returned length (len) of variable is not proper

[syzer]

Hello guys,

I would like to determine the length of the cell to create special "ID" based on the length of number value. Example: Number 1 : Length = 1 / Number 10 : Length = 2 / Number 122 : Length = 3

Unfortunately, when I trying to determine with LEN function the count of characters of the variable (in my case characters of the numbers), then excel determine wrong number. (Number 9: Length = 2)

Could somebody support me, what can be the root-cause and how I can repair it?

Dim datasheet As Worksheet 'where is the data copied from
Dim reportsheet As Worksheet 'where is the data pasted to
Dim LastRow As Integer 'the last row of the data set
Dim maxValue As Integer 'Create variable to store max value

Set datasheet = Sheet7

datasheet.Select
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Calculate max value in range
maxValue = Application.WorksheetFunction.Max(Range("A1:A" & LastRow))

maxValue = maxValue + 1
LastRow = LastRow + 1

ActiveSheet.Range("A" & LastRow).Select
ActiveSheet.Range("A" & LastRow).value = maxValue

MsgBox ("MaxValue:" & maxValue & vbCrLf & "Len of MaxValue:" & Len(maxValue))

Thank you in advance for any idea!

 

[StephenCrump]

The VBA Len() function returns the number of characters in a string or the number of bytes required to store a variable.

Hence, for example:

Sub Demo()

    Dim x As Integer
    Dim y As Long
    x = 123
    y = 123
    
    MsgBox Len(x)   'returns 2
    MsgBox Len(y)   'returns 4

End Sub

 

[Ezguy4u]

Here is my solution to your problem. This usually generates some discussion from the A+ students.

Sub Prog1()

Dim datasheet As Worksheet 'where is the data copied from
Dim reportsheet As Worksheet 'where is the data pasted to
Dim LastRow As Integer 'the last row of the data set
Dim maxValue As Integer 'Create variable to store max value

Set datasheet = Sheets("Sheet7")

datasheet.Select
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Calculate max value in range
maxValue = Application.WorksheetFunction.Max(Range("A1:A" & LastRow))

maxValue = maxValue + 1
LastRow = LastRow + 1

ActiveSheet.Range("A" & LastRow).Select
ActiveSheet.Range("A" & LastRow).Value = maxValue

MsgBox ("MaxValue:" & maxValue & vbCrLf & "Len of MaxValue:" & Len(CStr(maxValue)))

End Sub

 

[StephenCrump]

Another way ....

With Sheet7
    MsgBox Evaluate("Max(Len('" & .Name & "'!A1:A" & .Range("A" & Rows.Count).End(xlUp).Row & "))")
End With

 

[syzer]

Thank you guys for everyone, the second solution solved my issue! Thanks

 

[Ezguy4u]

syzer thank you for your reply. Glad to be of help