Hi,

I have the following worksheet formula:

=DEC2BIN(MOD(A1/16777216,256),8) &"."& DEC2BIN(MOD(A1/65536,256),8) &"."& DEC2BIN(MOD(A1/256,256),8) &"."& DEC2BIN(MOD(A1,256),8)

(which I found here: http://ottobelden.blogspot.co.uk/2010/06/ms-excel-dec2bin-32-bit-rotate-no-carry.html).

I have created a UDF equivalent, but it doesn't behave in the same way. Here's the UDF:

Function **DecTo32Bin**(lnDec As Long)

*' Function to return the binary representation of a 32-bit integer as a string
' in the format "00000000.00000000.00000000.00000000"
' Input must be a positive integer between 0 and 4,294,967,295 inclusive.
'*

DecTo32Bin = Application.WorksheetFunction.Dec2Bin(((lnDec / 16777216) Mod 256), 8) _

& "." & Application.WorksheetFunction.Dec2Bin(((lnDec / 65536) Mod 256), 8) _

& "." & Application.WorksheetFunction.Dec2Bin(((lnDec / 256) Mod 256), 8) _

& "." & Application.WorksheetFunction.Dec2Bin((lnDec Mod 256), 8)

End Function

Examples:

253 | <--INPUT |

00000000.00000000.00000000.11111101 | <--Worksheet Formula |

00000000.00000000.00000001.11111101 | <-- DecTo32Bin UDF |

^ | |

254 | <--INPUT |

00000000.00000000.00000000.11111110 | <--Worksheet Formula |

00000000.00000000.00000001.11111110 | <-- DecTo32Bin UDF |

^ | |

255 | <--INPUT |

00000000.00000000.00000000.11111111 | <--Worksheet Formula |

00000000.00000000.00000001.11111111 | <-- DecTo32Bin UDF |

^ | |

256 | <--INPUT |

00000000.00000000.00000001.00000000 | <--Worksheet Formula |

00000000.00000000.00000001.00000000 | <-- DecTo32Bin UDF |

257 | <--INPUT |

00000000.00000000.00000001.00000001 | <--Worksheet Formula |

00000000.00000000.00000001.00000001 | <-- DecTo32Bin UDF |

How come the UDF is giving incorrect results? (I suspect rounding issues... if so, how do we correct this?).

Cheers

Rich