Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Arrays in Excel UDF (Excel 2000 VBA)

    Hi,

    Could someone help me with some code showing me how to specify array arguments in a user defined function in Excel VBA.

    Consider the =CORREL(Array1, Array2, ...) function in Excel. The array arguments can accept multiple cells of info to calculate on. My reason for the post is not to find out about the =CORREL function, but how to program a UDF that contains arguments that are array defined, or can accept arrays?

    TIA
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Arrays in Excel UDF (Excel 2000 VBA)

    Here are two examples, not very useful in themselves, just as illustration:

    1. Wrapping the CORREL function in a UDF:

    Public Function MyCorrel(Array1 As Variant, Array2 As Variant) As Double
    MyCorrel = Application.WorksheetFunction.Correl(Array1, Array2)
    End Function

    Use this in a worksheet formula like this:

    =MyCorrel(A1:A5,B1:B5)

    or

    =MyCorrel({1,2,3,4,5};B1:B5)

    2. Simulating the SUMPRODUCT function:

    Public Function MySumProduct(Range1 As Range, Range2 As Range) As Double
    Dim i As Long
    For i = 1 To Range1.Cells.Count
    MySumProduct = MySumProduct+ Range1.Cells(i) * Range2.Cells(i)
    Next i
    End Function

    Use like this:

    =MySumProduct(A1:A5,B1:B5)

    (For a better simulation, you'd want to build in data type checking etc.)

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Arrays in Excel UDF (Excel 2000 VBA)

    Thanx Hans,
    I'd like to play with this second example before I ask you some more details about it!
    I'll come back to you with some Q's when I have some time later!
    Many Tx
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •