> Look up three papers which have been previously published in the
PCPRo,
> all by the same author, I guess.
>
> http://www.woodleyside.co.uk/stats.htm
>
Dr Iain Buchan is a lousy programmer. He is doing stuff in VBA, that's
asking for trouble.
First of all you never do computations in VBA on undeclared variables
in a message box. The computations are done on declared variables
(dim). If you use numbers, you need to specify what type they are. For
example 2% indicates an integer. 2# indicates a double precision
number. 2! is a single precision number. 2& is a long integer. 2$ is
the text version. In VBA in the declarations area you assign constants
to variable names, and declare what type they are. This is at the VBA
level, not at the EXCEL cell level. I haven't checked it as to what
happens when you use these symbols along with a number in an EXCEL
cell.
If the numbers have no declaration they become variant type. In EXCEL
they become "General". This allows cells to accept anything entered,
text, numbers, symbols, letters and combinations. If I am going to do
computations, I set the cell types to number. If I put N/A in a number
cell, or leave it empty, it will be accepted as a variant (number or
string). An error will be encountered if I attempt to do a formula
calculation on it. I overcome the problem by doing all my computations
involving a column in a macro (i.e. sub ........ ()), and do an if
test (i.e. the Boolean function "IsNumber()" or "IsEmpty()") on each
cell. If it is not a number, it gets skipped. Result no errors.
Actually my macro's are more complicated than this because of all the
other possibilities that can occur. (All the possibilities are
converted to double precision if a number occurs, else a zero is
assigned, and a flag is set to false.)
VBA came from VB which came from QB. In QB the basic precursor, the
MOD() function only applied to integers. Therefore if decimal numbers
are used, they will be converted to integers first. This was all known
in 1990. Also in QB, the exponentiation operates by first converting
the number to a log, multiplying it by the exponent, and then doing
exponentiation. Consequently a negative number would give an error in
QB. I don't know what the defaults are in VBA for EXCEL 97.
A good programmer never relies on default behavior of the compiler.
=================================================================
Instructions for joining and leaving this list and remarks about
the problem of INAPPROPRIATE MESSAGES are available at
http://jse.stat.ncsu.edu/
=================================================================
