VBA function that works with array formulas

Worksheets("Sheet1").Range("E1:E3").FormulaArray = _
"=Sum(R1C1:R3C3)"

Oops! I misread your posting... please disregard! (nt)

Mike,

You can have a function accept a Range (not an array formula) as one of it's arguments, but what exactly are you trying to do here? I assume that your function is not what you're really trying to accomplish, but with what you provided you are trying to multiply each cell in the range you pass in by 2, in which case you would not need VBA - you could just multiply w/in your array formula (or using SUMPRODUCT). Maybe you could clarify what you're trying to do - it may not require VBA.

-Russell

Change your function :-

Function Mikey(x)
For Each cell In x
y = y + cell.Value * 2
Next
Mikey = y
End Function

The way it was written, it would only work for a single cell.

Russell,

Here is that I am wanting to do.

I have a Excel sheet for each product that I have for sale. I have a summary page that lists the sales data for a given month. My original non-VBA function looked something like this:

{=sum(if(10*year(indirect(concatenate("'", c$6, "'!", "all_dates")))+month(indirect(concatenate("'", c$6, "'!", "all_dates"))) = $a7, all_values, 0))}

where all_dates is a range of sales dates
where all_values is the range of all sales
where $a7 is the cell that contains the date of interest
where c$6 is the sheet name of the product

I was wanting to make this formula a little easier to read.

I've made this into a VBA function without any range passing by having the function look something like this:

=MonthSum($a7, c$6, "all_dates", "all_values")

This VBA function uses a loop and is dreadfully slow.

I was hoping to write a vba function that would work in the first that would replace:

10*year(indirect(concatenate("'", c$6, "'!", "all_dates")))+month(indirect(concatenate("'", c$6, "'!", "all_dates")))

and reduce the clutter a bit.

All of this code is by memory. If you like, I'll send you trimmed down copy of the Excel document to show you what I am doing.

I really appreciate your help.

Regards,

Mike McCollister Mike, You can have a function accept a Range (not an array formula) as one of it's arguments, but what exactly are you trying to do here? I assume that your function is not what you're really trying to accomplish, but with what you provided you are trying to multiply each cell in the range you pass in by 2, in which case you would not need VBA - you could just multiply w/in your array formula (or using SUMPRODUCT). Maybe you could clarify what you're trying to do - it may not require VBA. -Russell

If what you're trying to do is return an array then...it's not that simple. This will do it.

Function Mikey(x As Range) As Variant
Dim Temp1, Temp2
Dim LB1 As Integer, UB1 As Integer
Dim LB2 As Integer, UB2 As Integer
Dim i As Integer, j As Integer
Temp1 = x

'One cell only has the formula (not an array)
If x.Cells.Count = 1 Then Mikey = Temp1 * 2: Exit Function

'Get dimensions of the range (UB1 x UB2)
LB1 = LBound(Temp1, 1)
UB1 = UBound(Temp1, 1)
LB2 = LBound(Temp1, 2)
UB2 = UBound(Temp1, 2)

'Create destination range
ReDim Temp2(LB1 To UB1, LB2 To UB2)

'Fill with desired results
For i = LB1 To UB1
For j = LB2 To UB2
Temp2(i, j) = Temp1(i, j) * 2
Next j
Next i

Mikey = Temp2
End Function

You have to be careful with the ReDim of the destination range, and also, what you're trying to calculate. This returns a double if one cell is entered and an array if several are included.

Juan Pablo G.

Hi,

Go ahead and email me a copy if you like. Russell, Here is that I am wanting to do. I have a Excel sheet for each product that I have for sale. I have a summary page that lists the sales data for a given month. My original non-VBA function looked something like this: {=sum(if(10*year(indirect(concatenate("'", c$6, "'!", "all_dates")))+month(indirect(concatenate("'", c$6, "'!", "all_dates"))) = $a7, all_values, 0))} where all_dates is a range of sales dates

Russell,

I'll try to get you a copy this weekend.

On a separate note, do you know how to prevent a sheet from being recalculated unless it is accessed (viewed, printed or values accessed by anohter sheet)? My summary sheet is always updated when I am editing my other sheets, which is bogging down the calculations.

Again,

Thanks,

Mike McCollister Go ahead and email me a copy if you like. : Russell, : Here is that I am wanting to do. : I have a Excel sheet for each product that I have for sale. I have a summary page that lists the sales data for a given month. My original non-VBA function looked something like this

Russell,

Here is the VBA code that I have to conditionally sum a range of values. It is used to summarize the sales for a specific month for a specific sheet. Any ideas on getting this faster? I understand that loops in VBA are extremely slow.

Thanks for your help.

Mike McCollister

Function MonthTotal(thisMonth As Date, dateRange As Range, countRange As Range) As Single
Dim uMonth As Date
Dim total As Single

' do things to speed up the calculations
Application.ScreenUpdating = False
Application.Calculation = xlManual

' do the loop
uMonth = UniqueMonth(thisMonth)
total = 0
For x = 1 To dateRange.Count
If (UniqueMonth(dateRange(x)) = uMonth) Then
total = total + countRange(x)
End If
Next

' undo things to speed up the calculations
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

' return the total
MonthTotal = total
End Function

Function UniqueMonth(d As Date) As Date
UniqueMonth = 100 * Year(d) + Month(d)
End Function Go ahead and email me a copy if you like. : Russell, : Here is that I am wanting to do. : I have a Excel sheet for each product that I have for sale. I have a summary page that lists the sales data for a given month. My original non-VBA function looked something like this

I also responded to your email, but here is what I wrote

Looks like you don't need VBA.

Say your dateRange is A1:A10 and your countRange is B1:B10 (or better yet, you name them dateRange and countRange).

Put a date in another cell - say C1 - that is in the month and year you want evaluated (the day doesn't matter).

In the cell you want to have the monthly total in, type the following formula:

=SUMPRODUCT(MONTH(A1:A10)=MONTH(C1))*(YEAR(A1:A10)=YEAR(C1))*(B1:B10))

or, if you name your ranges (and say you name C1 as monthCell):

=SUMPRODUCT(MONTH(dateRange)=MONTH(monthCell))*(YEAR(dateRange)=YEAR(monthCell))*(countRange))

Let me know if you have any problems.

Happy Holidays,

Russell

Russell, Here is the VBA code that I have to conditionally sum a range of values. It is used to summarize the sales for a specific month for a specific sheet. Any ideas on getting this faster? I understand that loops in VBA are extremely slow. Thanks for your help. Function MonthTotal(thisMonth As Date, dateRange As Range, countRange As Range) As Single ' do things to speed up the calculations Application.ScreenUpdating = False Application.Calculation = xlManual ' do the loop uMonth = UniqueMonth(thisMonth) total = 0 For x = 1 To dateRange.Count If (UniqueMonth(dateRange(x)) = uMonth) Then total = total + countRange(x) End If Next ' undo things to speed up the calculations Application.ScreenUpdating = True Application.Calculation = xlAutomatic ' return the total MonthTotal = total