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.

Answers


Use This:

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)


Need Your Help

modal window to open when icon is clicked

html5 css3 modal-dialog

I have a couple of form definitions in my main HTML file.