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

Question

Hi There, 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

Answer 1

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)

Answer 2

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