vba Excel 在公式栏中显示前导零
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/9644789/
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 show leading zero in formula bar
提问by Irene Ling
I have a list of data without 0, so I triedFormat Cells->Custom
to add a leading zero and it works.
我有一个没有 0 的数据列表,所以我尝试Format Cells->Custom
添加一个前导零并且它有效。
But when I click every single cell, the data shown in formula bar still didn't have the leading zero. For example, in my excel file after custom formatting:0112244555
但是当我单击每个单元格时,公式栏中显示的数据仍然没有前导零。例如,在我自定义格式后的 excel 文件中:0112244555
But in the formula bar:112244555
但是在公式栏中:112244555
Is there any way to display data with leading zeros as well when I click each of them?
当我单击每个数据时,有没有办法显示带有前导零的数据?
回答by brettdj
Removing leading zeroes is default behaviour in Excel.
删除前导零是 Excel 中的默认行为。
Your work-around to use a Custom format is standard to show leading zeroes
您使用自定义格式的解决方法是显示前导零的标准
If you want to actually embed them then you will need to add an apostrophe
ie in A1'012
will display
012
如果你想真正嵌入它们,那么你需要添加一个撇号,即在 A1'012
中将显示
012
as text - although you can still perform algebraic manipulation on this cell as if it was entered as a numeric12
作为文本 - 尽管您仍然可以在此单元格上执行代数操作,就像它是作为数字输入的一样12
Code Solution
代码解决方案
This code will:
此代码将:
- run on only numeric constant cells in the current selection (ie ignoring blanks, text, formulae)
- will add two leading zeroes behind an apostrophe
- 仅在当前选择中的数字常量单元格上运行(即忽略空格、文本、公式)
- 将在撇号后面添加两个前导零
So if you ran the code on column A below, the result would be the updated cells shown in column C (for demonstration only, the actual updates occur in A1,A4 and A5)
因此,如果您在下面的 A 列上运行代码,结果将是 C 列中显示的更新单元格(仅用于演示,实际更新发生在 A1、A4 和 A5 中)
Change this linestrRep = "'00"
to change the amount of leading zeroes
更改此行strRep = "'00"
以更改前导零的数量
'Press Alt + F11 to open the Visual Basic Editor (VBE)
'From the Menu, choose Insert-Module.
'Paste the code into the right-hand code window.
'Press Alt + F11 to close the VBE
'In Xl2003 Goto Tools … Macro … Macros and double-click AddLeadingZeros
'按 Alt + F11 打开 Visual Basic 编辑器 (VBE)
'从菜单中选择插入模块。
'将代码粘贴到右侧的代码窗口中。
'按 Alt + F11 关闭 VBE
'在 Xl2003 中转到工具…宏…宏并双击 AddLeadingZeros
Sub AddLeadingZeros()
Dim rng1 As Range
Dim rngArea As Range
Dim strRep As String
Dim lngRow As Long
Dim lngCol As Long
Dim lngCalc As Long
Dim X()
strRep = "'00"
On Error Resume Next
'Set rng1 = Application.InputBox("Select range for the replacement of leading zeros", "User select", Selection.Address, , , , , 8)
Set rng1 = Selection.SpecialCells(xlConstants, xlNumbers)
If rng1 Is Nothing Then Exit Sub
On Error GoTo 0
'Speed up the code by turning off screenupdating and setting calculation to manual
'Disable any code events that may occur when writing to cells
With Application
lngCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
'Test each area in the user selected range
'Non contiguous range areas are common when using SpecialCells to define specific cell types to work on
For Each rngArea In rng1.Areas
'The most common outcome is used for the True outcome to optimise code speed
If rngArea.Cells.Count > 1 Then
'If there is more than once cell then set the variant array to the dimensions of the range area
'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks
X = rngArea.Value2
For lngRow = 1 To rngArea.Rows.Count
For lngCol = 1 To rngArea.Columns.Count
'replace the leading zeroes
X(lngRow, lngCol) = strRep & X(lngRow, lngCol)
Next lngCol
Next lngRow
'Dump the updated array sans leading zeroes back over the initial range
rngArea.Value2 = X
Else
'caters for a single cell range area. No variant array required
rngArea.Value = strRep & rngArea.Value2
End If
Next rngArea
'cleanup the Application settings
With Application
.ScreenUpdating = True
.Calculation = lngCalc
.EnableEvents = True
End With
End Sub