MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA: Wait with calculation of array function?


Posted by Chris Hieronymus on June 28, 2001 11:15 AM

Array functions called from excel apparently start
executing before the input array is fully specified.
If I have a function

private function stupid(a)
dim cols as integer
cols = a.columns.count
if cols < 10 then msgbox("Input array too small")
stupid="whatever"
end function

I will always get the error message "Input array too
small" while entering the array, no matter how I enter
that array. If I enter it manually on the function
bar, as soon as I type "=stupid(a1" the function
starts working without waiting for the closing
parenthesis or the return key. Using the "Function
Wizard" and highlighting the input array, I get the
same problem.

So how can I make the function wait until the input
is complete before it goes and starts executing?

chris


Posted by Peter@Osgood.com on June 30, 2001 7:56 PM


Your function worked for me.
Are you entering a colon (:) after A1 before selecting another cell?