How to use array formula in VBA "Evaluate"?

[maria90]

Hello everyone

I'm working on a bigger project and, as I'm the only person working on this, I need some help from time to time. As I have lots of data, I decided to work with userforms and let VBA do the calculation.

To calculate, for example, the date of the first order, I use the following array formula in Excel:

{=MIN(IF(CustomerID=A3,OrderDate))}

- where A3 would be the cell with the customer's ID number. CustomerID and OrderDate are named ranges.

With VBA, I wrote following function:

Public Function FirstPurchase(ID As Integer) As Date
FirstPurchase= Evaluate("MIN(IF(CustomerID=" & ID & ",OrderDate))")
End Function

The procedure is called when a userform doubleclicks on a listview:

Private Sub ListViewCustomers_DblClick()
Dim intCustomerRow as Integer
intCustomerRow = ListViewCustomers.SelectedItem.Index + 2
'MsgBox intKundenID
Call GetData(intCustomerRow )
End Sub

When this function is called, I get the following runtime error message: Error 1004 - The item with the specified name wasn't found.

I think that the problem is caused with the named ranges CustomerID and OrderDate.

This problem doesn't occur on all computer.

What is wrong with this code? How do you write an array formula in VBA with Evaluate?

Thank you so much for your help.

Maria

PS: Unfortunately, I cannot upload part of my spreadsheet to this forum which might help you understand my problem.

 

[BiocideJ]

I don't see anything wrong with the way it is written and I tested it on my computer and it seems to work.

One possibility to consider is the scope of the named ranges. Are they set to Workbook or to a single sheet? If a single sheet then the macro will only work when that sheet is active.

 

[maria90]

I checked it and the named ranges are all set to workbook.

I also set a watch to the functions and I get the value 0.

This is driving me crazy

 

[Marcelo Branco]

I checked it and the named ranges are all set to workbook.

I also set a watch to the functions and I get the value 0.

This is driving me crazy

It worked perfectly for me.
1) Check if your dates are real dates or text
In your sheet try this formula =ISNUMBER(B2), for example, where B2 is an element (date) of the range OrderDate
If the formula returns TRUE then B2 is a real date. Otherwise it's a text seeming as a date

2) Check if both named ranges have the same size (height)

M.

 

[maria90]

It worked perfectly for me.
1) Check if your dates are real dates or text
In your sheet try this formula =ISNUMBER(B2), for example, where B2 is an element (date) of the range OrderDate
If the formula returns TRUE then B2 is a real date. Otherwise it's a text seeming as a date

2) Check if both named ranges have the same size (height)

1) The dates are real dates and
2) both named ranges have the same size. They are dynamic ranges.

I don't understand why the custom function FirstPurchase() works in the spreadsheet but not in VBA. I used the direct window and I get the same error message.

What else could be the problem. Should this code be written in VBA only, without EVALUATE?

 

[BiocideJ]

Maybe doing something like this...

Public Function FirstPurchase(ID As Integer) As Date
Range("IV65536").FormulaArray "=MIN(IF(CustomerID=" & ID & ",OrderDate))")
FirstPurchase = Range("IV65536").Value
Range("IV65536").Clear
End Function

I haven't tested this, but hopefully you get the jist of what I'm trying to do if it has a typo.

 

[maria90]

OK, I tried it, but I get a compile error...

 

[maria90]

I understand and it works now!

Is this is as fast as with the Evaluate method or what do you think?