+ Reply to Thread
Results 1 to 4 of 4

How to write a range function?

  1. #1

    How to write a range function?

    Hi,

    How can I write a function (such as sum) which could be called on the
    result of range operation such as A1:A10/A1:A10.

    In other words, I want to call function in the following way:

    mysum(A1:A10/A1:A10) Ctrl-Shift-Enter

    The following does not work:

    Function mysum(r As Range) As Double
    Dim i As Integer
    pgSum = 0
    For i = 1 To r.Rows.Count
    pgSum = pgSum + r(i)
    Next i
    End Function

    Many thanks in advance!

    Aaron Fude


  2. #2
    Norman Jones
    Guest

    Re: How to write a range function?

    Hi Aaron,

    Try:

    '=============>>
    Public Function mySum(r As Range) As Double
    Dim i As Integer

    For i = 1 To r.Rows.Count
    If IsNumeric(r(i)) Then
    mySum = mySum + r(i)
    End If
    Next i
    End Function
    '<<=============


    ---
    Regards,
    Norman



    <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > How can I write a function (such as sum) which could be called on the
    > result of range operation such as A1:A10/A1:A10.
    >
    > In other words, I want to call function in the following way:
    >
    > mysum(A1:A10/A1:A10) Ctrl-Shift-Enter
    >
    > The following does not work:
    >
    > Function mysum(r As Range) As Double
    > Dim i As Integer
    > pgSum = 0
    > For i = 1 To r.Rows.Count
    > pgSum = pgSum + r(i)
    > Next i
    > End Function
    >
    > Many thanks in advance!
    >
    > Aaron Fude
    >




  3. #3
    Norman Jones
    Guest

    Re: How to write a range function?

    Hi Aaron,

    I would additionally suggest that you change:

    > Dim i As Integer


    to

    Dim i As Long


    ---
    Regards,
    Norman



  4. #4
    kounoike
    Guest

    Re: How to write a range function?

    Try:

    Function mysum(ByVal r As Variant) As Variant
    Dim tmp As Variant
    Dim i As Long, j As Long
    Dim pgsum As Variant
    tmp = r
    For i = LBound(tmp, 1) To UBound(tmp, 1)
    For j = LBound(tmp, 2) To UBound(tmp, 2)
    pgsum = pgsum + tmp(i, j)
    Next
    Next
    mysum = pgsum
    End Function

    keizi

    <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > How can I write a function (such as sum) which could be called on the
    > result of range operation such as A1:A10/A1:A10.
    >
    > In other words, I want to call function in the following way:
    >
    > mysum(A1:A10/A1:A10) Ctrl-Shift-Enter
    >
    > The following does not work:
    >
    > Function mysum(r As Range) As Double
    > Dim i As Integer
    > pgSum = 0
    > For i = 1 To r.Rows.Count
    > pgSum = pgSum + r(i)
    > Next i
    > End Function
    >
    > Many thanks in advance!
    >
    > Aaron Fude
    >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1