Excel VBA 不会保留前导零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24686732/
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
Excel VBA Won't Keep Leading Zeroes
提问by programming_user1221
I can't seem to find a way to save leading zeros in my VBA code. The zeros are necessary since they correspond to unique IDs.
我似乎找不到在 VBA 代码中保存前导零的方法。零是必需的，因为它们对应于唯一的 ID。
I've tried changing the number format to text and 0000.... in excel and the same approach in my actual code: ActiveSheet.Cells(i, j).NumberFormat = "00000"
我已经尝试将数字格式更改为文本和 0000 .... 在 excel 中，在我的实际代码中使用相同的方法： ActiveSheet.Cells(i, j).NumberFormat = "00000"
Any other suggestions? (If I manually type these numbers into VBE it strips the leading zeros too). EDIT:
还有其他建议吗？（如果我手动将这些数字输入 VBE，它也会去除前导零）。编辑：
Sheets.Add.Name = "Temp"
Sheets("Sheet1").Select
ActiveSheet.Cells(2, 2).NumberFormat = "000"
cid = Cells(2, 2)
MsgBox cid
Sheets("Sheet2").Select
ActiveSheet.Cells(6, 1).NumberFormat = "00000"
sid = Cells(6, 1)
Sheets("Temp").Select
Url = _
"URL;" & _
"http......asp?" & _
"X1=" & cid & "&" & _
"X2=" & sid & "&"
This is inside a loop ultimately but I'm debugging as individual iterations.
这最终是在一个循环内，但我正在作为单独的迭代进行调试。
采纳答案by KekuSemau
All below deliberations were about writingto a cell, not reading from it. Changing the Numberformat doesn't work that way. See if you can figure out how this code works:
以下所有讨论都是关于写入单元格，而不是读取单元格。更改 Numberformat 不会那样工作。看看你是否能弄清楚这段代码是如何工作的：
dim v as integer
v = val(Sheets("Sheet1").Cells(2, 2))
dim cid as string
cid = format(v, "000")
You should never use Selectand ActiveSheetthat way, it is not necessary; and never use Cellswithout the sheet it should be referring to.
(Valcan be omitted if the cell is really already numeric and not text).
你永远不应该使用Select和ActiveSheet这种方式，它是没有必要的; 并且永远Cells不要在没有它应该引用的工作表的情况下使用。
（Val如果单元格真的已经是数字而不是文本，则可以省略）。
Earlier answer... :
较早的回答...：
This code works perfectly fine for me
这段代码对我来说非常好
Worksheets(1).Columns("A").NumberFormat = "@"
Worksheets(1).Cells(1, "A").Value = "00023"
And also does
还有
Worksheets(1).Columns("A").NumberFormat = "000000"
Worksheets(1).Cells(1, "A").Value = "0023"
(Here the string is converted to a number and displayed with 6 digits)
（这里将字符串转换为数字并以6位数字显示）
EDIT:
If that fails - although I could not explain that - I'd still bet that the 'should reallywork:
(No numberformat required.)
编辑：
如果失败 - 虽然我无法解释 - 我仍然打赌它'应该真的有效：（
不需要数字格式。）
Dim s As String
s = "0002"
Worksheets(1).Cells(1, "A").Value = "'" & s
回答by gtwebb
Setting the format to text or "00000" should work. The small snippet below gives the results expected. With the first storing a value of 0001 and the second storing a value of 1 that is formatted to look like 0001.
将格式设置为文本或“00000”应该可以工作。下面的小片段给出了预期的结果。第一个存储值 0001，第二个存储值 1，格式为 0001。
This also works if the cell value was stored in a string variable.
如果单元格值存储在字符串变量中，这也适用。
Sub set_value()
Range("A1").NumberFormat = "@"
Range("A1").Value2 = "0001"
Range("B1").NumberFormat = "0000"
Range("B1").Value2 = "1"
End Sub
