Page 1 of 1
[Solved] Passing a range to a user defined function
Posted: Wed Nov 12, 2014 5:39 am
by cleanman2
I am writing a couple of macros in Basic, I wish I could use Python, but I am
stuck with basic. In one macro I am processing a range in the macro. In the
other one I need to create a user defined function and pass the range in as
a parameter.
The range is A2:M2. Cell M2 is blank. In the macro that accesses the range
internally when processed M2 is seen as blank. When I pass the range to the
function M2 is seen as 0 and messes up any further processing.
Is this a normal consequence of passing a range to a user defined function?
Is there any way to keep a blank from becoming a zero?
Another question. When working with the range interally I can use getDataRange()
to access the data in the range, but if I pass in the range getDataRange() gives an
error. Whe is that?
Thanks, Jim
Re: Passing a range to a user defined function
Posted: Wed Nov 12, 2014 6:06 am
by FJCC
When passing a cell range as a parameter of a function, I believe the function receives either strings or values. The zero is just the value of the blank cell. Without seeing your code, it is hard to suggest a solution to your problem.
Re: Passing a range to a user defined function
Posted: Wed Nov 12, 2014 6:22 pm
by cleanman2
OK, here is some test code that shows the problem:
Code: Select all
function rangetest( inrange as variant )
rowcount = ubound(inrange, 1)
colcount = ubound(inrange, 2)
'print rowcount, colcount
for row = 1 to rowcount
for col = 1 to colcount
print inrange( row, col)
next
next
end function
The range I pass in is A2:M2. A2 contains a string. B2:M2 can contain the integers 0 to 10 or empty cells. In my test case the cell M:2 is empty, but any empty cell causes problems.
In the real macro after the range is put in an array, IsNumeric() is used to extract all numbers. Then the lowest two are discarded and the remaining numbers are summed. So a cell that is empty in the sheet but shows up as a zero in the macro makes the sum incorrect.
Regards, Jim
Re: Passing a range to a user defined function
Posted: Wed Nov 12, 2014 6:53 pm
by karolus
Hallo
In the real macro after the range is put in an array, IsNumeric() is used to extract all numbers. Then the lowest two are discarded and the remaining numbers are summed. So a cell that is empty in the sheet but shows up as a zero in the macro makes the sum incorrect.
As simple Calcfunction:
Code: Select all
=SUM(A2:M2)-MIN(A2:M2)-SMALL(A2:M2;2)
Re: Passing a range to a user defined function
Posted: Thu Nov 13, 2014 5:55 pm
by cleanman2
=SUM(A2:M2)-MIN(A2:M2)-SMALL(A2:M2;2)
WOW, thanks for that, worked fine.
But it sure takes all the fun out writing macros.
Thanks, Jim
Re: Passing a range to a user defined function
Posted: Thu Nov 13, 2014 7:23 pm
by karolus
cleanman2 wrote: =SUM(A2:M2)-MIN(A2:M2)-SMALL(A2:M2;2)
WOW, thanks for that, worked fine.
But it sure takes all the fun out writing macros.
If you're happy with writing messy basic-stuff?!
Re: Passing a range to a user defined function
Posted: Fri Nov 14, 2014 11:33 pm
by cleanman2
If you're happy with writing messy basic-stuff?!
No I'm not. Like mentioned in the origianl message, I would rather be using Python, but this one needed to be in Basic.
Regards, Jim