J.E. McGimpsey said:
Yes I did - thanks! ....
Actually, the problem was in Log(nIn + 1). nIn+1 implicitly coerces
the 1 to a Long which produces an overflow in the long result. I
corrected it using Long(nIn + 1#). Thanks!
nIn = -nIn - 1 is an error when nIn = -(2^31), but it only affects that one
value.
I suppose it would be churlish to point out that your function
chokes on the "boundary condition" of zero, when it would be
trivially easy to fix...<g>
Not churlish. I didn't test mine thoroughly. I was lazy and tried to make
the fewest possible modifications from your udf. That turned out to be
unwise.
I wholly agree. I'll have to think about whether I like your method
better.
All I'll claim for my approach is that I believe it deals more efficiently
with VBA strings by (1) initializing the return value using a String() call,
and then modifying default (all 0s for nonnegatives, all 1s for negatives)
bits as needed using Mid() _statement_ calls. That is, it avoids multiple
memory allocation calls that could arise from building up the return value
one digit at a time using string concatenation.
value.
This I don't like better, but I suspect it's a matter of taste.
Seems preferable to me to test with ISNA(), for example, than by a
string comparison. Again, however, trivial to implement.
....
This is a deeper point, not just subjective. After thinking about it,
returning "" when the specified width is less than the required width would
allow simple string comparison to check for errors, e.g., hgd2b(n,p)="". As
long as Excel maintains a ridiculously small number of allowed nested
function call levels, I'll try to avoid returning error values except for
functions that could return any real number or text value when there were no
errors. Only those require error values to indicate errors. Avoiding error
return values means one fewer level of nested function calls - often a very
good thing.
here's where n=0 fails. I think Abs is superfluous as well.
Correct on both points.
I've probably missed it, but is the overhead of the IIf() call
preferable to the implicit coercion of
k = INT(x) - (Int(x) <> x) - neg
?? I have an unreasonable dislike of IIf(), I suppose.
This is an interesting question. Personally, I dislike using implicit
coersion of booleans to integers in VBA because TRUE is -1 rather than +1.
Makes the code harder to understand. Nevertheless, implicit coersion is
almost certainly much faster than IIF calls. I use IIF because I'm addicted
to C/C++/awk ternary operator expressions. This is as much a matter of taste
as it is a matter of readability vs speed.
Note that for even powers of 2, this gives one two few digits.
Yup.
Personal preference: Exit Function seems inherently inelegant to me,
except just before an error handler. It seems tolerable if it's in
the first couple of lines, but any deeper in the procedure and I
prefer to use a If...Else...EndIf.
OK, this is a Pascal vs C argument in the making. There are arguments pro
and con, but I'm deep in the C camp with respect to the value of immediate
returns vs elegance. This particular problem may not require convoluted If
structures to replace the immediate return, but too many Ifs spoil the code.
There's a reason applications written in C and its offspring outnumber those
written in Pascal by a thousand-fold or more.
So time for my own rewrite.
Function hgd2b(ByVal n As Long, Optional p As Long) As String
Dim k As Long, neg As Boolean, sb As String * 1
If n < 0 Then
neg = True
n = -(n + 1) 'note: -1 becomes 0
k = 32 'to eliminate ambiguity
ElseIf n > 0 Then
k = Int(Log(n) / Log(2)) + 1
Else 'n = 0
k = 1
End If
If p <= 0 Then
p = k
ElseIf p < k Then
Exit Function 'returns "" when p too narrow
End If
If neg Then
hgd2b = String(p, "1") 'init as -1
sb = "0"
Else
hgd2b = String(p, "0") 'init as 0
sb = "1"
End If
If n = 0 Then Exit Function 'returns both 0 and -1 quickly
For k = p To p - k Step -1
If n - 2 * Int(n \ 2) > 0 Then Mid(hgd2b, k, 1) = sb
n = n \ 2
Next k
End Function
No IIF calls, no Excel Application method calls, many immediate returns (I
like 'em), one loop with no post processing. This time I tested it using
Sub foo()
Dim k As Long, n As Long, fd As Variant
fd = FreeFile
Open ENVIRON("TEMP") & "\exceld2b.txt" For Output As #fd
For k = -31 To 30
If k < 0 Then
n = -(2 ^ Abs(k))
Print #fd, n, hgd2b(n)
Print #fd, n + 1, hgd2b(n + 1)
Print #fd, n \ 2 - 1, hgd2b(n \ 2 - 1)
ElseIf k = 0 Then
Print #fd,
Print #fd, 0, hgd2b(0)
Print #fd, 1, hgd2b(1)
Print #fd,
Else
n = 2 ^ k
Print #fd, n, hgd2b(n)
Print #fd, n + 1, hgd2b(n + 1)
Print #fd, 2# * n - 1#, hgd2b(2# * n - 1#)
End If
Next k
Close #fd
End Sub
Everything looks good.
Note that negative values always show 32 binary digits. This is necessary to
eliminate ambiguity. IOW, IMO it's OK to assume implicit leading zeros, so
11B and 1111B are three and fifteen, respectively, rather than -1 to 2 and 4
bits, respectively. The alternative would be always to display a leading
zero for positive values and a leading 1 for negative values. But that leads
to certain absurdities such as one needing to be 01 while negative one could
be just 1.
