如何按照工作表中的格式在 VBA 中检索 Excel 单元格值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6932901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I retrieve an Excel cell value in VBA as formatted in the WorkSheet?
提问by Logan Serman
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):
我需要在数字列表中保留前导零。数字是这样添加的（在循环中，但这只是使用 (1, 1) 的示例：
Set cel = Sheet.worksh.Cells(1, 1)
cel.ColumnWidth = 10
cel.Value = e.Name
cel.NumberFormat = "0000"
Where e.Nameis the number, something like "0720". This displays on the worksheet just fine, but if I do something like this:
数字在哪里e.Name，类似于“0720”。这在工作表上显示得很好，但如果我这样做：
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.
我得到“720”。我需要它是“0720”，我知道我可以检查使用Len()并以这种方式添加零，但我想知道是否有更直接的 Range 对象方法可以为我做到这一点。
采纳答案by Lance Roberts
Use This:
用这个：
Msgbox Format(Sheet.worksh.Cells(1,1).Value, "0000")
回答by GSerg
You are confusing a number with its textual representation.
您将数字与其文本表示混淆了。
You want the .Textproperty, not .Value, but then, you might have problems with it.
您想要该.Text属性，而不是.Value，但是，您可能会遇到它的问题。
回答by Jon Peltier
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)
Msgbox 格式(Sheet.worksh.Cells(1,1).Value, Sheet.worksh.Cells(1,1).NumberFormat)
回答by Kirk
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)
Msgbox Application.WorksheetFunction.Text(Sheet.worksh.Cells(1, 1).Value, Sheet.worksh.Cells(1, 1).NumberFormat)
