Solved: Length of negative number

[mdmackillop]

In debugging my posted code here, http://vbaexpress.com/forum/showthread.php?t=9663 I've a problem with these lines
[VBA]
Num = CInt(Mid(F, Pos1 + 2, Pos2 - Pos1 - 2))
Mid(F, Pos1 + 2, Len(Num)) = Num + Rw
[/VBA]
When Num has a value of -2, Len(Num) = 4, which is throwing out the text replacement.

[Ken Puls]

Hey Malcolm,

Can you provide a sample of a formula that would cause this issue? I tried it with a simple one, and it seems nice...

[mdmackillop]

Hi Ken
I've resolved the problem, but here's the original code.

[Ken Puls]

Are you submitting this one to the KB, Malcolm? Seems a nice entry to me.

[mdmackillop]

I'll give it a day or two for some ********* to come up with a method solution!
Seriously though, I'll probably do that. I should be able to build in options for absolute row, colum or both.

[Ken Puls]

LOL!

[Bob Phillips]

[vba]

Sub xxConvertAbsolute()
Dim cell As Range

For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula( _
cell.Formula, xlA1, xlA1, xlAbsolute)
End If

Next cell

End Sub
[/vba]

[mdmackillop]

The ********* has posted! "ConvertFormula". I should have guessed!

[Ken Puls]

[Bob Phillips]

I told you, those Scots love us men of Wessex.

For Arthur and country!

[mdmackillop]

I'll steal the solution and KB it anyway!

[Bob Phillips]

You could wrap my name around it, I don't get many KBs :-(

[mdmackillop]

but my original question remains. Why does -2 have a length of 4?

[mvidas]

Malcolm,

I'm guessing 'Num' is declared as type Long. Len() returns different things, depending on what you pass it.

If you pass it a string, it returns the length of that string.
If you pass it a variant, it converts to a string first then returns the length of that string.
If you pass it anything else, it returns how much memory is used by the variable. (LenB)
See it in action:[vba]Sub WatchLen()
Dim lNum As Long, vNum, stNum As String, iNum As Integer, siNum As Single, dNum As Double
lNum = -2
vNum = -2
stNum = -2
iNum = -2
siNum = -2
dNum = -2
Debug.Print "Long: " & Len(lNum)
Debug.Print "Variant: " & Len(vNum)
Debug.Print "String: " & Len(stNum)
Debug.Print "Integer: " & Len(iNum)
Debug.Print "Single: " & Len(siNum)
Debug.Print "Double: " & Len(dNum)
End Sub[/vba]

Long: 4
Variant: 2
String: 2
Integer: 2
Single: 4
Double: 8

Matt

[mdmackillop]

Thanks Matt,
That makes sense.