Passing an entire column as an argument in a function 
Author Message
 Passing an entire column as an argument in a function

Hi,

I'm a bit new to VBA. I've been trying to write a function that requires to
pass a column (or portion of it) as a parameter, which is similar to the way
the built-in function sum() could be used, i.e. sum($A:$A).

So, I define my function as:

    Public Function Test(ByVal TestColumn As Variant) As Variant

But it does not work. And I try debugging it. I find that "TestColumn" is an
array from 1 to 65535,  the last cell, i.e. A65536, is out of range. So, how
can I solve this problem?

Thanks.

Patrick



Sun, 12 Aug 2001 03:00:00 GMT  
 Passing an entire column as an argument in a function
Patrick,

The following worked for me (XL97):

Option Explicit

Function MySum(r As Range)
  Dim i&
  Dim Tot#

  For i& = 0 To r.Rows.Count - 1
    Tot# = Tot# + r(i& + 1, 1)
  Next i&
  MySum = Tot#
End Function

From my spreadsheet a call =MySum(a:a) seemed to give the correct values.

Note that I have declared i as a long.  VBA seems only to support signed
integers and thus are limited to 32,767.  BTW it's not very fast!

HTH

Peter

Quote:

>Hi,

>I'm a bit new to VBA. I've been trying to write a function that requires to
>pass a column (or portion of it) as a parameter, which is similar to the
way
>the built-in function sum() could be used, i.e. sum($A:$A).

>So, I define my function as:

>    Public Function Test(ByVal TestColumn As Variant) As Variant

>But it does not work. And I try debugging it. I find that "TestColumn" is
an
>array from 1 to 65535,  the last cell, i.e. A65536, is out of range. So,
how
>can I solve this problem?

>Thanks.

>Patrick




Sun, 12 Aug 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Passing Functions or Procedures as arguments in Function or Procedure calls

2. passing many arguments to a function in Excel

3. pass arguments to function via OnAction?

4. How do I pass an Argument to Workbooks.Add.SaveAs function

5. Proper types of the arguments passed to a VBA function

6. Passing variables as arguments to functions!!

7. Passing an array as an argument to VBA function

8. Passing one dimension of a variant table as argument of a function

9. Need help in writing a worksheet function in VB - passing a cell range as an argument

10. Error Passing Arguments, Entry Required Function


 
Powered by phpBB® Forum Software © phpBB Group