We help IT Professionals succeed at work.

Convert Excel formula to VBA formula

Massimo Scola
Massimo Scola used Ask the Experts™
on
Hello

I have a formula which calculates a customer's favourite shop. The problem is that it's an array formula and, even though I could add it to the worksheet (like I did in the attached workbook), it will take ages till the caluclation is complete. That's because I have over 300 shops and over 3000 customers.

I work a lot with userform and I need to the display the favourite shop in the userform only. Hence I want the calculation to take place in the userform only which would save a lot of calculation time.

This is the formula for F2:

=IFERROR(INDEX(Shop;MODE(IF((Customer_ID=E2)*(JAHR(DateVisited)=$F$1);IF(DateVisited<>"";MATCH(Shop;Shop;0)*{1\1}))));"")

Open in new window


When converted to VBA with the macro recorder, I get the following code:

'.FormulaArray = _
        "=IFERROR(INDEX(Shop,MODE(IF((Customer_ID=RC[-1])*(YEAR(DateVisited)=R1C6),IF(DateVisited<>"""",MATCH(Shop,Shop,0)*{1,1})))),"""")"

Open in new window


How would you code this with VBA/in a userform? The RC[-1] need to be replaced by listbox1.value.

Can you advise a good website or book on how to write worksheet formulas in VBA for userforms?

Thanks for your help

Massimo
favouriteshop.PNG
userform.PNG
Shops.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
How are you calculating the customer's favourite shop?
Massimo ScolaSoftware Engineer

Author

Commented:
With this worksheet formula:

IFERROR(INDEX(Shop;MODE(IF((Customer_ID=E2)*(JAHR(DateVisited)=$F$1);IF(DateVisited<>"";MATCH(Shop;Shop;0)*{1\1}))));"")
Massimo ScolaSoftware Engineer

Author

Commented:
To be honest, I got this formula from another website and changed it so that it would work with my worksheet.
NorieAnalyst Assistant

Commented:
I try getting the formua to work in VBA but couldn't, that might be my fault though.

If you knew what it did then it might be able to be translated in VBA  - I think I know what part of it does but not the whole thing though.

Unless you had your own definition for the customers favourite shop.

Perhaps something simple like the shop the customer visited the most times, which could be calculated for each year.
Massimo ScolaSoftware Engineer

Author

Commented:
Yes, that's it. The shop a customer has most visited is also the customer's favourite shop!
That would be much simpler, wouldnt it?
Robert SchuttSoftware Engineer

Commented:
Note: This is not what you need probably but it is possible to do what you originally asked.

I'll let @imnorie help you further with a solution that you'll be able to maintain more easily.

Private Sub cboYear_Change()
    UpdateFavShop
End Sub

Private Sub ListBox1_Change()
    UpdateFavShop
End Sub

Sub UpdateFavShop()
    If ListBox1.ListIndex <> -1 And cboYear.ListIndex <> -1 Then
        txtShop.Text = Evaluate("IFERROR(INDEX(Shop,MODE(IF((Customer_ID=" & ListBox1.Value & ")*(YEAR(DateVisited)=" & cboYear.Value & "),IF(DateVisited<>"""",MATCH(Shop,Shop,0)*{1,1})))),"""")")
    End If
End Sub

Open in new window

NorieAnalyst Assistant

Commented:
mscola

Ignore what Robert said but not his code, it's much better than what I was going to come up with.

I'd tried using Evaluate with the original formula but couldn't get it to work, could have just been a missing quote or something.

So was going to try doing it all in VBA without any Excel functions.

So go with Robert's solution.:)
Robert SchuttSoftware Engineer

Commented:
Why I said that was that it could be very slow. Especially if the data is sorted it could be possible to make a quicker VBA function (but I'm not so sure now that I tested it by running it for all possible values).

The reason the original formula didn't work was probably the reference to the year (R1C6). I replaced that with the combo value.
NorieAnalyst Assistant

Commented:
Robert

I should have said ignore my idea.

I can't see any problem with the code you've posted.

It doesn't seem to slow things down.

I can imagine a formula like this slowing  things down if it was used in a large range or with a lot of data.

It's only being calculated once here.
Robert SchuttSoftware Engineer

Commented:
@imnorie: You're absolutely right. Now what's left for me is to decide if I feel sorry for shooting under your wings or feel proud that I stopped this from going the wrong way ;-)

@mscola: How are you seeing all this?
NorieAnalyst Assistant

Commented:
Robert

I'm not bothered either way.:)

I tried the Evaluate thing and it didn't work the first couple of times.

I came to the conclusion that the problem was that it was an array formula so didn't persevere.

mscola

Go with Robert's solution.
Massimo ScolaSoftware Engineer

Author

Commented:
@Hi Robert,  As I do have a lot of data, and I will add more data every day. That's why I prefer to have only one calculation at a time.

The example works very well. However, when I try to implent it into my project, I get a error message (error message 13 type mismatch). I use named ranges (which are in German):

Here are my named Ranges:
ShoppingTaxi_Geschäft
Kunde_ID
ShoppingTaxi_Auftragsdatum


txtShop.Text is the name of the box which needs to be filled.

What is wrong in my code?

Massimo




    txtShop.Text = Evaluate("IFERROR(INDEX(Range(""ShoppingTaxi_Geschäft""),MODE(IF((Range(""Kunde_ID"")=" & KundenID & ")*(YEAR(Range(""ShoppingTaxi_Auftragsdatum""))=" & 2012 & "),IF(Range(""ShoppingTaxi_Auftragsdatum"")<>"""",MATCH(Range(""ShoppingTaxi_Geschäft""),Range(""ShoppingTaxi_Geschäft""),0)*{1,1})))),"""")")

Open in new window

Robert SchuttSoftware Engineer

Commented:
You've put the range names in quotes, I haven't tried it yet but that would be my first guess.
Robert SchuttSoftware Engineer

Commented:
So actually, instead of:

Range(""Kunde_ID"")

just put:

 Kunde_ID

I'll try it out shortly myself.
Massimo ScolaSoftware Engineer

Author

Commented:
Thanks robert.

Should I upload some of the data here?
Robert SchuttSoftware Engineer

Commented:
I just tried it out, it works without the Range() and quotes.

Here's the adapted version of your latest posted code, but of course this does only 1 year as it's hardcoded instead of using the combobox.

Evaluate("IFERROR(INDEX(ShoppingTaxi_Geschäft,MODE(IF((Kunde_ID=" & KundenID & ")*(YEAR(ShoppingTaxi_Auftragsdatum)=" & 2012 & "),IF(ShoppingTaxi_Auftragsdatum<>"""",MATCH(ShoppingTaxi_Geschäft,ShoppingTaxi_Geschäft,0)*{1,1})))),"""")")

Open in new window

Massimo ScolaSoftware Engineer

Author

Commented:
Hi Robert

Thanks a lot. Although I don't get any error messages it returns blank/nothing.
That's because my original formula is an array formula. :-o I tried adding { at the beginning and a } at the end of the formula. But it doesn't work. What needs to be added or changed to the formula?
Software Engineer
Commented:
No, it should work just like that. Here's the workbook I've been working with. The ranges are defined both in english and german now but that shouldn't matter. I added a test routine but also tested just selecting 1 customer and 1 year and checking against the results in the sheet you posted originally.
Shops.xlsm
NorieAnalyst Assistant
Commented:
I expanded the data to 10000+ rows and Robert's code still works.
Massimo ScolaSoftware Engineer

Author

Commented:
it works!!

the problem was that, in my project, I had the control txtShop hidden behind another empty textbox! How stupid of me!

Thank you so much!!!

Massimo
NorieAnalyst Assistant

Commented:
Massimo

I've done that plenty of times - sometimes 3 or 4 textboxes on top of each other.
Robert SchuttSoftware Engineer

Commented:
Well, good thing you found out! Good luck with your project and see you around!