0

Maybe I'm trying to do too much here, but I've worked for hours on this with no luck. I hope you can help.

I am creating a function whose only parameter is a named range on a another sheet in the same workbook. My problem is that I can't figure out how to pass that name into the function and use it.

My goal is call the function to sum the column specified by the named range on the other worksheet, called Client Payments.

Here's my code that works:

Function PayPerMonth()
PayPerMonth = Application.WorksheetFunction.Sum(Worksheets("Client Payments").Range("C:C"))
End Function

I get a total that is correct, but of course I specified the column in the function. Not too useful.

Here's what I tried that doesn't work:

Function PayPerMonth(ColumnToAdd)
PayPerMonth = Application.WorksheetFunction.Sum(Worksheets("Client Payments").Range("ColumnToAdd"))
End Function

It doesn't work when I remove the quotes around ColumnToAdd, and many other variations I have tried.

What I want to do is to enter =PayPerMonth(MBPADV) or =PayPerMonth(SBTSADV) etc. to call the function and aim at the right column, where MBPADV and SBTSADV are named columns.

I'm not hung up on using the column names. It just makes it more readable (to me, anyway!).

Can this be done?

Community
  • 1
  • 1
Richard
  • 11
  • 7

1 Answers1

1

Range("named range") returns a range in the WorkBook so it should be good to pass into your function that takes a range.

Assuming you have a named range in the workbook called ColumnToAdd, this should work:

Function PayPerMonth(ColumnToAdd)
  PayPerMonth = Application.WorksheetFunction.Sum(Range("ColumnToAdd")
End Function

Update per comments:

Function PayPerMonth(ColumnToAdd As Range)
  PayPerMonth = Application.WorksheetFunction.Sum(Range(ColumnToAdd))
End Function

Usage:

Dim MyRange as Range
MyRange = Sheet.Columns("C")
Amount = PayPerMonth(MyRange)

Note:

Your function call will return a variant. You may want to declare As Integer, As Single or As Float at the end of Function PayPerMonth(ColumnToAdd As Range) to help A) speed the processing, since Excel won't have to spend time trying to figure out what type of variable it's working with, and B) yourself/the next coder when it comes time to maintain the code - being explicit makes your code more readable.

FreeMan
  • 5,660
  • 1
  • 27
  • 53
  • Whoops! Thanks for your reply, FreeMan. No there isn't a column name ColumnToAdd. I intended it to be a variable inside the function. What I want to do, if it is possible, is to pass into the function the name of the column I wish to sum. Can I do this? – Richard Apr 09 '15 at 14:52
  • I'm sure we're getting close, but still no cigar. In your Usage comments above you note the function is called from VBA (e.g. another function or Sub). That's not what I want to do. – Richard Apr 13 '15 at 21:39
  • I want to call the function in a several cells in the worksheet, e.g. =PayPerMonth(SBTSADV), =PayPerMonth(MBPADV), etc. where the parameter is the named column on another worksheet. The actual final function is much more complicated this this simple sum. That's why I want to call a function in the cell. The only stumbling block I've got is passing the name of the column into the function and then using it in my calculation. I tried your 'Update per comments:', but it didn't work. – Richard Apr 13 '15 at 21:53
  • What is it, exactly, that you're getting in the value of the parameter when it's passed into your function? Have you put a break-point on the first line to look at what `ColumnToAdd` is pointing to? You may need to use `Indirect()` to get it to reference the address of the cell. Something along the lines of `=PayPerMonth(Indirect())`. – FreeMan Apr 13 '15 at 22:44
  • Your question is a good one, FreeMan. What is being passed? I have no idea how to find that out. I tried to reference the column directly when calling the function from the worksheet (`=PayPerMonth(INDIRECT('Client Payments'!C:C))`) but that didn't change things. I guess it can't be done. Funny, I thought it would be something lots of people want to do, namely pass a range into a function and then do operations on it. – Richard Apr 14 '15 at 18:37
  • I give up. Thanks for giving me your time with this. Regards. – Richard Apr 14 '15 at 18:38
  • Put a breakpoint on the first line of your function then refresh the sheet. It should stop on that line then you can F8 through code one line at a time and look at all the variables. Do a Google search on how to debug VBA for some tips. – FreeMan Apr 14 '15 at 22:35