hex() function corrupted excel file

[ajetrumpet]

all,

my coding stopped at the same spot all of a sudden, and I believe I corrupted my entire excel file by having the code break when a hex() function tried to return an unacceptable value. I can't remember what I did, but the input to hex() was using 3 nested cast functions. something like:

---> hex(cdbl(calledFunction return as string)))

now when I type hex() in the immed. window it's messed up. This:

?hex(15)

errors out completely, when it should return 'F'. This:

?hex 15

returns 15. NOT 'F'.

so obviously it's not working properly now. Anyone got any suggestions for me? Tried copying all of my code into a new instance of excel with a new file created - same thing happened. It couldn't be my entire excel program that's screwed now, could it be?

the entire code I was using was:

Dim tempDbl As Double
tempDbl = CDbl(BinaryToDecimal("", "", "11111111"))
debug.print hex(tempDbl) 'ERRORS HERE (type mismatch)

----------------------------------------------------------------------------

Function BinaryToDecimal(InputCell As String, OutputCell As String, _
                         Optional convertVal As String) As Variant
'2 to 10 (quotient/remainder method does not work converting FROM binary!)
On Error GoTo eh

If convertVal Then
   binary = convertVal
Else
   binary = Range(InputCell)
End If

dec = 0
   CharCount = Len(Trim(binary))
   power = CharCount - 1
      For i = 1 To CharCount
         If Mid(binary, i, 1) = "1" Then
            dec = dec + (base2 ^ power)
         End If
            power = power - 1
      Next i
   
   If convertVal Then
      BinaryToDecimal = dec
   Else
      Range(OutputCell) = CStr(dec)
   End If

eh:
End Function

 

[Leith Ross]

Hello ajetrumpet,

Are you aware that Excel has a decimal to hex conversion function? The function is DEC2HEX and is included in the Analysis ToolPak add-in. <LINK rel=stylesheet type=text/css href="mkmsitstore:msohlp10.chm::/html/office10.css">If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

VBA also has a function Hex that converts a number into a string up to eight hexadecimal characters long. Hexadecimal values in VBA can be entered using the prefix &H followed by the hexadecimal value. For Example, &HAD0012F9.

 

[ajetrumpet]

Hello ajetrumpet,

Are you aware that Excel has a decimal to hex conversion function? The function is DEC2HEX and is included in the Analysis ToolPak add-in. <LINK rel=stylesheet type=text/css href="mkmsitstore:msohlp10.chm::/html/office10.css">If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

VBA also has a function Hex that converts a number into a string up to eight hexadecimal characters long. Hexadecimal values in VBA can be entered using the prefix &H followed by the hexadecimal value. For Example, &HAD0012F9.

Leith,

This has nothing to do with what I asked about. Yes, those are alternatives, but I'd like advice on whether my Excel application is now corrupt.

 

[Leith Ross]

Hello ajetrumpet,

These suggestions can help you determine if Excel has become corrupt. However, the quickest method would be to save your work and restart Excel. If it still happens and you are using the Analysis ToolPak add-in, remove the add-in and retest your function. A failure after doing this would seem to indicate Excel is corrupted.