Array UDF (Excel 2003 SP 2)

Topic

New Reply

A fond hello to all the wonderful gurus here. And, of course, an Excel question.

I am trying to create an array UDF that will return results in a range of selected cells. The UDF uses four inputs: a constant value, a constant start date, a constant end date , and a range of quarter dates that extend a number of years along a row. The task is to compare the start date and end date with the first quarter in the quarter range and with the second quarter in the quarter range to determine if the start and end dates occur within the quarter. Depending on the particular result, X can be anywhere from 0 to 1 or some fraction in between. The contant value gets multiplied by X and that is the answer.

I know my logic works because I created a UDF that works if I enter it in one cell and then drag to fill the other cells. It returns valid results.

What I’m trying to do now is to create an array UDF where I can select the output range, enter the UDF, press CTRL+SHIFT+ENTER and get the various results along the selected range.

I have attached a sample workbook to show what I mean.

I just can’t figure out what I’m doing wrong.

Reply | Quote

Replies

That’s a rather complicated example to start creating array functions with…
If you’re learning something new, it’s always better to start with a simple example, so that you can see where things go wrong.

Remarks: qd shouldn’t be an array of Range, but simply a Range.
The variable qd1 isn’t defined in the array function – what should it be, perhaps fd1?

Reply | Quote

Hi Hans,

Yes, I agree with your view, but as with so much else in life, this was thrust upon me. Nothing like a baptism of fire.

To answer your question, qd is the range of quarter dates, and each cell in the array looks at two quarters, qd1 and qd2. What I am doing is selecting all the cells under the dates and defining them as qd. In the array function I refer to the first quarterly date as qd(i) and the second as qd(i + 1). That arrangement worked in the single cell UDF and I thought it would work here as well.

I know the logic looks complicated but it works in the single cell UDF. Somehow, though, I’m not able to get it going in the array UDF.

Does this answer your question?

Reply | Quote

In the single-cell version, you have arguments qd1 and qd2.
In the array version, you have an argument qd, but you still refer to an undefined entity qd1 in this version. As far as I can tell, you seem to use this both for qd(i) and qd(i + 1).
You will have to explain what qd1 should be in the array version.

Reply | Quote

The function works in the attached version, but I had to guess what you meant by qd1. Check carefully.

Reply | Quote

Well, it looks like your guess was on the money, because you did it.

What did you do that I was missing? I looked at your code, but I couldn’t see the difference at first glance. I thought that I was close, but I don’t know why I couldn’t make it work.

Thanks so much for your efforts. You are, once again, a lifesaver…

Reply | Quote

In the declaration of the function, I changed qd() As Range to qd As Range
I changed ColNum = qd().Columns.Count to ColNum = qd.Columns.Count
I removed the line to ReDim qd (it didn’t make sense)
I replaced all occurrences of qd1 with qd(i) if I remember correctly.

Finally, I reconfirmed the formula with Ctrl+Shift+Enter to force it to recalculate.

Reply | Quote

It makes sense. I had thought that variable required a Redim, but it doesn’t.

Thanks again.

Reply | Quote

An easier way to create the function is not to reinvent the wheel, but to use the existing function you created:

Function DateCalcArray(VCel As Range, sd1 As Range, fd1 As Range, qd As Range) As Variant
  Dim i As Integer
  Dim Temp()
  Dim ColNum As Integer
  ColNum = qd.Columns.Count - 1
  ReDim Temp(1 To ColNum)
  For i = 1 To ColNum
    Temp(i) = DateCalc(VCel, sd1, fd1, qd.Cells(i), qd.Cells(i + 1))
  Next i
  DateCalcArray = Temp
End Function

Steve

Reply | Quote

Hi Steve,

Never thought of that. I’ll try it tomorrow when I’m back in the office. That would make the array UDF a lot simpler. The only downside is that the array UDF would not be able to stand on its own in this form. But I really like the idea.

Thanks much for taking a look and for the suggestion.

Reply | Quote

I don’t see a reason why it needs to stand on its own. You have 2 functions in it, one that does the work and one that creates the array. If you never have the need for the non-array one, it could make sense to combine them, but if you have a need for the single item version, why not use it? It makes it easier if you ever have to modify it, since it only requires modification of just this one instead of both of them.

Steve

Reply | Quote