[Solved] Passing a range to a user defined function

[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

[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.

[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

[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) 

[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

[karolus]

=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?!

[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