Hexadecimal Numbers

[Norman Bullen]

I'm writing a VBA "macro" which reads several text files and combines
data from them into a single spreadsheet.

One column must contain a value which is normally expressed in
hexadecimal. The value is represented in one of the input files as a
four-character string in which the characters are all members of the set
"0123456789ABCDEF". Leading zeroes are possible. I need to put that
character string in the cells of one column in the spreadsheet.

Initially, I tried setting NumberFormat property of the cells to "0000".
This worked except for those values which contain a single "E" preceded
and followed by digits. In this case Excel interprets the string as a
number with an exponent and displays the wrong value.

Now I've changed it to set the NumberFormat property to "@". Now all of
the values are displayed correctly but those which contain only digits
(i.e. those which Excel thinks are numbers) are flagged with a little
triangle in the upper left corner and, when clicked, display an warning
message that the cell contains a "number formatted as text."

I can get rid of that by manually selecting "Ignore Error" from the menu
but I haven't found a way to automate that.

Can anyone suggest a way to store (in VBA) a string of hexadecimal
digits in a cell and have the result appear exactly as stored with
leading zeroes if present, non-numeric characters "ABCEDF" if present,
and no warning flag?

 

[Dave Peterson]

First, it's best to store your 16 character data as text. Excel only keeps
track of 15 significant digits. So if you format your cell as a number (or
general), then the lowest order digit will be a 0.

You may want to record a macro when you change the error checking options to
ignore this check.

Application.ErrorCheckingOptions.NumberAsText = False

Tools|Options|Error checking tab
in xl2003 menus

ps. You may want to keep track of the setting so you can change it back to what
the user had when your program closes.

 

[Norman Bullen]

As I said, I'm only saving four digits in each of these cells so the 16
digit limit is not a problem.

I tried recording a macro when I clear the error on a single cell and
got nothing.

Turning off the "NumberAsText" error checking option causes the
indicator in the cells to disappear, but only until such time as the
option is turned back on. I was hoping to find something more permanent
and more local to the cell.

First, it's best to store your 16 character data as text. Excel only keeps
track of 15 significant digits. So if you format your cell as a number (or
general), then the lowest order digit will be a 0.

You may want to record a macro when you change the error checking options to
ignore this check.

Application.ErrorCheckingOptions.NumberAsText = False

Tools|Options|Error checking tab
in xl2003 menus

ps. You may want to keep track of the setting so you can change it back to what
the user had when your program closes.

 

[Dave Peterson]

I misunderstood the 4 characters from that set of 16. But that really wasn't
the point of your question anyway.

Maybe you can use something like:


Option Explicit
Sub testme01()
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
For Each myCell In myRng.Cells
With myCell.Errors(xlNumberAsText)
If .Value = True Then
.Ignore = True
End If
End With
Next myCell
End If
End With
Next wks

End Sub

As I said, I'm only saving four digits in each of these cells so the 16
digit limit is not a problem.

I tried recording a macro when I clear the error on a single cell and
got nothing.

Turning off the "NumberAsText" error checking option causes the
indicator in the cells to disappear, but only until such time as the
option is turned back on. I was hoping to find something more permanent
and more local to the cell.

 

[Norman Bullen]

Thanks. I think
Errors(xlNumberAsText).Ignore = True
will do war I need. Too bad the macro recorder couldn't come up with that.

Norm

I misunderstood the 4 characters from that set of 16. But that really wasn't
the point of your question anyway.

Maybe you can use something like:


Option Explicit
Sub testme01()
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
For Each myCell In myRng.Cells
With myCell.Errors(xlNumberAsText)
If .Value = True Then
.Ignore = True
End If
End With
Next myCell
End If
End With
Next wks

End Sub

 

[Dave Peterson]

Thank goodness for VBA's Help <vbg>.

Thanks. I think
Errors(xlNumberAsText).Ignore = True
will do war I need. Too bad the macro recorder couldn't come up with that.

Norm