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 开发者_StackOverflow中文版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.

You are confusing a number with its textual representation.

You want the .Text property, not .Value, but then, you might have problems with it.

Use This:

Msgbox Format(Sheet.worksh.Cells(1,1).Value, "0000")

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)

A little late to the party, but you could always try calling up the value and number format separately, like this:

Msgbox Application.WorksheetFunction.Text(Sheet.worksh.Cells(1, 1).Value, Sheet.worksh.Cells(1, 1).NumberFormat)