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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 15:29:41  来源:igfitidea点击:

Excel show leading zero in formula bar

excelexcel-vbaexcel-formulavba

提问by Irene Ling

I have a list of data without 0, so I tried
Format 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 numeric
12

作为文本 - 尽管您仍然可以在此单元格上执行代数操作,就像它是作为数字输入的一样
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 中)

enter image description here

在此处输入图片说明

Change this line
strRep = "'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