Len function not giving correct value

Question

I am having difficulty getting the Len() function to give a correct length of the number.

The section of code that Is having Difficulty is here:

Spec = Len(Range("J8")) - x
intermit = Range("I8") - Range("J8")
Low = Len(intermit)

MsgBox "Lower Spec: " & intermit & " Length: " & Low

intermit = Range("I8") + Range("J8")
High = Len(WorksheetFunction.Text(intermit, 0))
MsgBox "Upper Spec: " & intermit & " Length: " & High

Range("I8") = 0.0140

Range("J8") = 0.0002

The message boxes are supposed to display the following:

Lower Spec: 0.0138 Length: 6

Upper Spec: 0.0142 Length: 6

However they are displaying:

Lower Spec: 0.0138 Length: 8

Upper Spec: 0.0142 Length: 8

I tried converting the intermit variable to text but then it would return as 0. I do not know where the extra 2 characters that the function is counting is comming from.

Answer 1

Don't use WorksheetFunction.Text to convert a Variant/Double to a String; the correct format string for it would have been "@", not 0 - but you don't need to involve worksheet functions, the VBA standard library already provides a VBA.Conversion module that gives you a bunch of conversion functions, including CStr.

But, the Len function lives in the VBA.Strings module, which suggests that the string conversion would be implicit anyway - doesn't hurt to make that conversion explicit using an appropriate conversion function, especially given we're dealing with what appears to be undeclared variables, that would be Variant/Double at run-time, assuming the cell values are actually numeric values.

I cannot reproduce the problem with the provided code and values. This works for me:

Dim intermitLow As Double
intermitLow = Sheet2.Range("I8").Value - Sheet2.Range("J8").Value
Debug.Print intermitLow, Len(CStr(intermitLow))

Dim intermitHigh As Double
intermitHigh = Sheet2.Range("I8").Value + Sheet2.Range("J8").Value
Debug.Print intermitHigh, Len(CStr(intermitHigh))

Output with I8=0.014 (that non-significant trailing zero in the OP is worrying) and J8=0.0002:

 0.0138        6 
 0.0142        6 

Note that I'm explicitly qualifying the Range member calls with a proper Worksheet object, here Sheet2 (see CodeName: Sheet1 for everything you need to know about worksheet code names). If your code is written in a standard module, then unqualified Range calls are implicit ActiveSheet references, i.e. it's referring to whatever worksheet happens to be active at the time, which makes the code more frail than it needs to be - although, it doesn't appear to be the problem here, since we're working with the expected values.

Consider only reading the cell values once - no need to hit the worksheet again every time:

Dim value1 As Double 'todo use a meaningful name
'note: possible type mismatch on assignment depending on the data type of .Value
value1 = Sheet2.Range("I8").Value

Dim value2 As Double 'todo use a meaningful name
'note: possible type mismatch on assignment depending on the data type of .Value
value2 = Sheet2.Range("J8").Value

Dim intermitLow As Double
intermitLow = value1 - value2
Debug.Print intermitLow, Len(CStr(intermitLow))

Dim intermitHigh As Double
intermitHigh = value1 + value2
Debug.Print intermitHigh, Len(CStr(intermitHigh))

Avoid reusing variables/identifiers for different purposes within a procedure scope, and always declare all variables (have Option Explicit at the top of every module forcing you to do it).