如何在 VBA 中保留十六进制范围的所有前导零？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3190120/
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 keep all the leading zeros on a Hexadecimal Range in VBA?
提问by JCam
I am trying to expand a range of Hexadecimal numbers. For example I have on column K ... 1880 and column L ...188A my range is 1880-188A When I expand the Range, starting On column M I get 1880 1881 1882 1883 1884 1885 1886 etc etc.
我正在尝试扩展一系列十六进制数字。例如，我在列 K ... 1880 和列 L ...188A 上，我的范围是 1880-188A 当我扩展范围时，从 MI 列开始得到 1880 1881 1882 1883 1884 1885 1886 等。
From one of the posting I copied and changed the VBA script to fit my case... and it works ... but found 2 issues. All my device range are 4 digit and I need to keep all leading zeros.
For example if my range is 0000 - 0005 .... it errors... will not work.
If my range is 0001 - 0005 then I get 1 2 3 4 5.... and I want to be 0001 0002 0003 0004 0005
从我复制并更改了 VBA 脚本以适合我的情况的其中一篇帖子中...并且它有效...但发现了 2 个问题。我所有的设备范围都是 4 位数字，我需要保留所有前导零。
例如，如果我的范围是 0000 - 0005 .... 它错误...将不起作用。
如果我的范围是 0001 - 0005 那么我得到 1 2 3 4 5.... 我想成为 0001 0002 0003 0004 0005
Any help will be much appreciated.. Thanks, JCam Here is the script that I use it ... as long as there are no leading zeros on my range
任何帮助将不胜感激..谢谢，JCam 这是我使用它的脚本......只要我的范围内没有前导零
Sub FillHexNumbers()
Dim cellKValue As Long
Dim cellLValue As Long
Dim diffBetweenKAndL As Long
Dim iCtr As Long
cellKValue = CLng(Format("&h" & Cells(2, 11).Text, "###"))
cellLValue = CLng(Format("&h" & Cells(2, 12).Text, "###"))
diffBetweenKAndL = cellLValue - cellKValue
For iCtr = 0 To diffBetweenKAndL
Cells(2, 13 + iCtr).Value = Hex(cellKValue + iCtr)
Next
End Sub
回答by MikeD
The Analysis Toolpakcontains functions to convert between DEC and HEX - for HEX you can specify the # of digits, e.g. =DEC2HEX(14,4) gives "000E". You may enable this package by "Tools/Add-Ins...". By adding columns containing DEC numbers and displaying the HEX aequivalent you can maybe solve your task without VBA at all ...
该分析工具库包含了这些功能DEC和HEX之间的转换-为十六进制，你可以指定的号码，如= DEC2HEX的＃（14,4）给出“000E”。您可以通过“工具/加载项...”启用此包。通过添加包含 DEC 数字的列并显示等效的十六进制数，您可能根本不需要 VBA 就可以解决您的任务......
Hope that helps
希望有帮助
回答by RickCardoso
Try this:
尝试这个：
Dim i as Integer 'This is the number you want to format
Dim l as Integer 'The length you want your format in (suppose it's six)
Dim h as String
l = 6
i = 47 'Any integer between 0 and 16,777,215
h = Replace(Space(l - len(hex(i))), " ", "0") & hex(i) 'h = "00002F"
The variable hwill return the format text "00002F".
变量h将返回格式文本“00002F”。
Cheers,
干杯，
Rick.
瑞克。
回答by bugtussle
you have to format the data as a string. You can do this with a single quite ie '0045.
您必须将数据格式化为字符串。你可以用一个简单的 ie 来做到这一点'0045。
may be something like this:Cells(2, 13 + iCtr).Value = "'" & Hex(cellKValue + iCtr)
可能是这样的：Cells(2, 13 + iCtr).Value = "'" & Hex(cellKValue + iCtr)
回答by jth
If you format a cell as text, then add your hex value with leading zeroes, they should remain.
如果您将单元格格式化为文本，然后添加带有前导零的十六进制值，它们应该保留。
If you are dealing with hex values that have already lost their leading zeroes, you can manually fix them in a text editor, then format your new cells as text, and paste values.
如果您正在处理已经丢失前导零的十六进制值，您可以在文本编辑器中手动修复它们，然后将新单元格格式化为文本，然后粘贴值。
Alternatively, you can define a custom number format for those cells by right-clicking on them, choosing Format Cells..., choose Custom, then enter a value like "0000" (if, say, you'd like padding to four chars). This will only affect hex values that don't have an alpha character. If you enter 000A, Excel should preserve the zeroes and treat it as a string automatically.
或者，您可以通过右键单击这些单元格，选择“设置单元格格式...”，选择“自定义”，然后输入“0000”之类的值（如果您想填充到四个字符）来为这些单元格定义自定义数字格式）。这只会影响没有字母字符的十六进制值。如果输入 000A，Excel 应保留零并自动将其视为字符串。
回答by Drew Chapin
It's very important that you also correct the cell formatting because Excel will remove the leading zeros if the format is General or Number. Two ways to correct this are:
更正单元格格式非常重要，因为如果格式为“常规”或“数字”，Excel 将删除前导零。纠正这种情况的两种方法是：
Prefix your value with a single quote (
')Cells(2, 13 + iCtr).Value = "'" & ...Enforce a specific format.
Cells(2, 13 + iCtr).NumberFormat = "@" ' Text format
用单引号 (
')前缀您的值Cells(2, 13 + iCtr).Value = "'" & ...强制执行特定格式。
Cells(2, 13 + iCtr).NumberFormat = "@" ' Text format
As for the challenge of adding the leading 0's. I've got three solutions for you.
至于加入领先的挑战0。我为你提供了三个解决方案。
Set the number format such that it displays the leading
0's. Note that this may not be the ideal solution if you really need the stored value to include the leading0's.Cells(2, 13 + iCtr).NumberFormat = "000000"Use a binary
Orwith the value&H10000000and then useMid()orRight()to get rid of the leading1. This has the disadvantage that your number can't be more than 7 digits in hex. However, given that the maximum column number in Excel 2016 is 16,384 (4 digits in hex) and the maximum row is 1,048,576 (6 digits in hex). That's kind of a non-issue.' Using Mid() Cells(2, 13 + iCtr).Value = Mid(Hex(&H10000000 Or (cellKValue + iCtr)), 2) ' Desired # of digits = # of 0's ^^^^^^^ ' Using Right() Cells(2, 13 + iCtr).Value = Right(Hex(&H10000000 Or (cellKValue + iCtr)), 4) ' Desired # of digits ^Use the
String()andLen()functions to pad the value with0's. This has the disadvantage you must convert the number to hex twice, or create another variable to hold the converted string.Cells(2, 13 + iCtr).Value = String(5 - Len(Hex(cellKValue + iCtr)), "0") & Hex(cellKValue + iCtr) ' Desired # of digits ^
设置数字格式，使其显示前导
0。请注意，如果您确实需要存储的值来包含前导0的值，这可能不是理想的解决方案。Cells(2, 13 + iCtr).NumberFormat = "000000"使用
Or带有值的二进制文件，&H10000000然后使用Mid()或Right()去掉前导1. 这样做的缺点是您的号码不能超过 7 位十六进制数字。但是，鉴于 Excel 2016 中的最大列数为 16,384（十六进制 4 位数），最大行数为 1,048,576（十六进制数）。那是一个无关紧要的问题。' Using Mid() Cells(2, 13 + iCtr).Value = Mid(Hex(&H10000000 Or (cellKValue + iCtr)), 2) ' Desired # of digits = # of 0's ^^^^^^^ ' Using Right() Cells(2, 13 + iCtr).Value = Right(Hex(&H10000000 Or (cellKValue + iCtr)), 4) ' Desired # of digits ^使用
String()和Len()函数用0's填充值。这有一个缺点，您必须将数字两次转换为十六进制，或者创建另一个变量来保存转换后的字符串。Cells(2, 13 + iCtr).Value = String(5 - Len(Hex(cellKValue + iCtr)), "0") & Hex(cellKValue + iCtr) ' Desired # of digits ^
Personally, I prefer the Orand Mid()method. It will ensure your raw value includes the leading 0's and it has the least number of functions/operations (thus it will perform the fastest).
就个人而言，我更喜欢Or和Mid()方法。它将确保您的原始值包含领先0的 并且它具有最少数量的功能/操作（因此它将执行最快）。
