Decimal to Binary functions in Visual Basic

Here are functions to perform decimal to/from binary conversion.

  • CBin – converts a decimal integer to binary string.
  • CDeci – converts a binary string to decimal integer.
  • CBinS16 – converts a decimal signed integer to 16 bit binary string.
  • CdecS16 – converts a 16 bit binary string to decimal signed integer.
'converts an integer to binary string
Function CBin(ByVal n As Double) As String
    If n = 0 Then
        CBin = 0
    ElseIf n > 0 Then
        Dim i As Double
        Dim c As Long
        i = 2 ^ CLng(Log(n) / Log(2) + 0.1)
        Do While i >= 1
            c = Fix(n / i)
            CBin = CBin & c
            n = n - i * c
            i = i / 2
        Loop
    End If
End Function

'converts an integer to binary string, problems for n=64
Function CBinOld(ByVal n As Double) As String
    On Error Resume Next 'caters for 0 values
    Dim i As Double
    i = 2 ^ Int(Log(n) / Log(2))
    Do While i >= 1
        CBinOld = CBinOld & Fix(n / i)
        n = n - i * Fix(n / i)
        i = i / 2
    Loop
End Function

'converts a binary string to integer
Function CDeci(ByRef s As String) As Double
    Dim i As Long
    CDeci = 0
    For i = 0 To Len(s) - 1
        CDeci = CDeci + (Mid$(s, Len(s) - i, 1) * 2 ^ i)
    Next i
End Function

'converts an integer to 16 bit signed binary string
Function CBinS16(ByVal n As Double) As String
    Dim i As Double

    CBinS16 = vbNullString
    If n < -2 ^ 15 Then
        CBinS16 = "0"
        n = n + 2 ^ 16
        i = 2 ^ 14
    ElseIf n < 0 Then
        CBinS16 = "1"
        n = n + 2 ^ 15
        i = 2 ^ 14
    Else 'not negative
        i = 2 ^ 15
    End If

    Do While i >= 1
        CBinS16 = CBinS16 & Fix(n / i)
        n = n - i * Fix(n / i)
        i = i / 2
    Loop
End Function

'converts 16 bit signed binary string to integer
Function CDecS16(ByRef s As String) As Double
    Dim i As Long
    CDecS16 = 0
    For i = 0 To Len(s) - 1
        CDecS16 = CDecS16 + (Mid$(s, Len(s) - i, 1) * 2 ^ i)
    Next i
    If CDecS16 >= 2 ^ 15 Then 'negative number
        CDecS16 = CDecS16 - 2 ^ 16
    End If
End Function

Optimizing/faster String Concatenation in VBA

There are numerous links about Visual Basic string concatenation, one particular is Microsoft’s How To Improve String Concatenation Performance. But the article is overwritten for the point it is trying to make, so I will share a simplified example.

Lets say we want to perform the following concatenation:

Dim i As Long
Dim s As String
For i = 1 To 100000000
	s = "A" & i & "B"
Next i

This takes approximately 6000 ticks. The faster approach, yet more complex functionality would be as follows:

Dim sourceLength As Long
sourceLength = 1
Dim source As String
s = "A B"

Dim i As Long
Dim s As String
For i = 1 To 100000000
	source = CStr(i)
	If Len(source) > sourceLength Then
		sourceLength = Len(source)
		s = "A" & Space$(sourceLength) & "B"
	End If
	Mid$(s, 2, sourceLength) = source
Next i

Which takes approximately 3700 ticks, a saving of nearly 40%.