How do I keep all the leading zeros on a Hexadecimal Range in VBA?

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.

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

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

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

The Analysis Toolpak contains 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 ...

Hope that helps

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 h will return the format text "00002F".

Cheers,

Rick.

you have to format the data as a string. You can do this with a single quite ie '0045.

may be something like this:
Cells(2, 13 + iCtr).Value = "'" & Hex(cellKValue + iCtr)

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.

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:

1. Prefix your value with a single quote ( ' )

   Cells(2, 13 + iCtr).Value = "'" & ...
   

2. Enforce a specific format.

   Cells(2, 13 + iCtr).NumberFormat = "@" ' Text format
   

As for the challenge of adding the leading 0's. I've got three solutions for you.

1. 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 leading 0's.

   Cells(2, 13 + iCtr).NumberFormat = "000000"
   

2. Use a binary Or with the value &H10000000 and then use Mid() or Right() to get rid of the leading 1. 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                                                     ^
   

3. Use the String() and Len() functions to pad the value with 0'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              ^
   

Personally, I prefer the Or and 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).