VBA problem: Resistor Value ranges

[benstreek]

Hi, I've written a short VBA function to give a Boolean true when a number value is referenced which is in the E96 resistor range. The code looks like this:

Please Login or Register  to view this content.

It works fine up until the equality test, where i have sporadic success. For example if the value passed to the function is 1, the equality test evaluates as true and proceeds accordingly. However if the value passed is 107, the equality test evaluates as false despite normRes and prefRes being the same value and datatype. I am new to VBA, though i do have experience in c++, it is possible i am missing something fundamental. Any help would be greatly appreciated.

Ben

[MrShorty]

You're probably missing the most fundamental aspect of computer arithmetic. It seems a lot of people miss it. Computer's have to do arithmetic on binary numbers. While decimal integers can be exactly represented in binary, the vast majority of decimal fractions can't. You've constrained prefres to 2 decimal places, but I don't see where you've done the same for normres. What is probably happening at If (normres=prefres) is you are getting something like If (1.0699999999=1.07) which returns FALSE.

Experienced programmers (in any language) I know almost never test for "exactly equal" like you're doing. They will almost always use a statement like If (ABS(normres-prefres)<=10^-6) when they want to test for equality. See if something like that helps.

Another observation, but I don't know how much of an improvement it will be. An alternative approach to your normalize function would be to use the INT(logbase10(x)) to figure out how many 10's to multiply (or divide) by. Something like
function normalize(x)
log10=log(x)/log(10)
expon=int(log10)
normalize=x/10^expon
end function
code hasn't been debugged, but something like that will do it.

[benstreek]

cheers mrshorty, that sorts that problem out. The reason the normRes wasn't constrained to 2 dp was the assumption that the value passed to the function would at least be a valid value of any resistor range - probably a stupid assumption to make. Guess that teaches me not to take the value shown in the debugger watch window as gospel. thanks again