CODEWITHSUNDEEP
vbaexcel
Brian
Brian

Reputation: 8357

A better Excel Bin2Dec function (signed)?

I was wondering if anyone had written a better Bin2Dec VBA macro for Excel in than the one included. I'm looking for a function that can take arbitrarily large binary numbers (within ~16 bits), and uses the signed bit. Excel's BIN2DEC function overflows after 12 bits, and it generally very ugly (Excel isn't too great since numbers overflow quickly as well). Any help?

Upvotes: 0

Views: 1189

Answers (1)

Brian
Brian

Reputation: 8357

Wrote one:

Function Bin2SignedDec(sMyBin As String) As Long
    Dim x As Integer
    Dim iLen As Integer
    Dim sign As Boolean
    Dim tmp As String

    sign = Mid(sMyBin, 1, 1) = "1"


    iLen = Len(sMyBin) - 1
    For x = 0 To iLen
        tmp = Mid(sMyBin, iLen - x + 1, 1)
        If sign Then
            If tmp = "1" Then
                tmp = "0"
            Else
                tmp = "1"
            End If
        End If
        Bin2SignedDec = Bin2SignedDec + _
          tmp * 2 ^ x
    Next
    If sign Then
        Bin2SignedDec = (Bin2SignedDec * -1) - 1
    End If

End Function

This link got me started

Upvotes: 0

Related Questions