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?
--
Norm

To reply, change domain to an adult feline.

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 wrote:

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?
--
Norm

To reply, change domain to an adult feline.

--

Dave Peterson

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.

Dave Peterson wrote:

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 wrote:

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?
--
Norm

To reply, change domain to an adult feline.




--
Norm

To reply, change domain to an adult feline.

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


Norman Bullen wrote:

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.

Dave Peterson wrote:

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 wrote:

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?
--
Norm

To reply, change domain to an adult feline.



--
Norm

To reply, change domain to an adult feline.

--

Dave Peterson

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

Norm

Dave Peterson wrote:
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


Norman Bullen wrote:

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.

Dave Peterson wrote:


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 wrote:


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?
--
Norm

To reply, change domain to an adult feline.


--
Norm

To reply, change domain to an adult feline.




--
Norm

To reply, change domain to an adult feline.

Thank goodness for VBA's Help <vbg.

Norman Bullen wrote:

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

Norm

Dave Peterson wrote:
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


Norman Bullen wrote:

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.

Dave Peterson wrote:


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 wrote:


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?
--
Norm

To reply, change domain to an adult feline.


--
Norm

To reply, change domain to an adult feline.



--
Norm

To reply, change domain to an adult feline.

--

Dave Peterson