How do I retrieve an Excel cell value in VBA as formatted in the WorkSheet?
I need to keep leading zeros on a list of numbers. The numbers are added like this (in a loop, but this is just an example using the (1, 1):
Set cel = Sheet.worksh.Cells(1, 1) cel.ColumnWidth = 10 cel.Value = e.Name cel.NumberFormat = "0000"
Where e.Name is the number, something like "0720". This displays on the worksheet just fine, but if I do something like this:
Msgbox Sheet.worksh.Cells(1, 1).Value
I get "720". I need it to be "0720", I know I could check using Len() and add the zeros that way, but I was wondering if there was a more direct approach with the Range object that would do this for me.
Msgbox Format(Sheet.worksh.Cells(1,1).Value, "0000")
You are confusing a number with its textual representation.
You want the .Text property, not .Value, but then, you might have problems with it.
Even better, if you're not sure what the format is:
Msgbox Format(Sheet.worksh.Cells(1,1).Value, Sheet.worksh.Cells(1,1).NumberFormat)