Bin2Dec Function in Excel also available in Access ?

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Richard Schollar

MrExcel MVP
Apr 19, 2005
Hi Nick

Whilst I am not an Access expert by any means, I don't believe there is an equivalent Access function (it's only available in Excel via the Analysis Toolpak add-in anyway). You could write a custom function in VBA to do the same thing though. Could you elaborate on how you want to use it and under what circumstances? Thanks.


New Member
May 29, 2005
Thanks for your help as always Richard

I was trying to get a priority list for our customers. Each client is listed and then has a YES/No for each field. Starting from left to right this determines the priority of the client - so for instance

Client Name, Preferential Client, Large Revenue Provider, Potential for Growth

has the values

Client A, Yes, No, No
Client B No, Yes, No
Client c No,Yes,Yes


Someone suggested I could use Binary to set the priority and although it's new to me I thought about giving it a try e.g. changing Yes = 1, No = 0.
I was going to check using the equivalent to BIN2DEC but as you mentioned I don't think I have this option in Access. I did get a book on Access which has a way of using Excel Functions in Access

It suggested something like

Public Function GAMMALN(number As Integer) As Double

Dim xl As Object

Set xl = CreateObject("Excel.Application")
GAMMALN = xl.WorksheetFunction.GAMMALN(number)

Set xl = Nothing
End Function

could be used but I can't seem to get this to work for BIN2DEC

Anyway if you have any thoughts please let me know but please don't go to much trouble as I realised by simply setting each field after client name to Ascending I can get exactly what I need !

Thanks again for all your help


Andrew Fergus

MrExcel MVP
Sep 9, 2004
Hi Nick

Aren't you over-complicating this by using BIN2DEC? If you assign a value of 1 for a yes and 0 for no, then concatenate the 3 digits and convert into a number between 111 and 000, and sorting this value in descending order you get the highest priority clients at the top of the list. But as you already identified, you can get this by sorting on those three fields anyway.

HTH, Andrew

Richard Schollar

MrExcel MVP
Apr 19, 2005

I think in your specific situation (as you've discovered and Andrew suggested) the sort based on meaningful info (ie Yes/No) is best - it will make more sense to you if you (or a co-worker) comes back to the database after a break. However, in case you wanted to see how you might structure a UDF (which will work in access too) then:

Function BinToDec(str As String) As Long
Dim i As Long, lTemp As Long, j As Long
i = Len(str)
If i > 0 Then
    lTemp = 0
    For j = 1 To i
        If CInt(Mid(str, j, 1)) Then lTemp = lTemp + 2 ^ (i - j)
End If
BinToDec = lTemp
End Function

Note that ths function performs no error checking (so it will actually 'convert' a non-binary string such as 7073 (treating it as 1011)).

Watch MrExcel Video

Forum statistics

Latest member