How to use XMATCH with Application.WorksheetFunction in Excel VBA? Today, a question from Hydrogen Peroxide. How can you refer to an array inside of a formula in Excel VBA.
This workbook takes a look at how to simulate an Excel function in VBA.
We start off with a simple one, just as MID which is already built in to VBA.
Then, for a function like VLOOKUP that is not in Excel VBA, you can use Application.WorksheetFunction.
However, today's question needs to concatenate two ranges and then pass that to Application.WorksheetFunction and this does not work.
The solution, thanks to Brad Yundt, is to do the concatenation inside of Application.Evaluate.
Check out Brad Yundt's answers on Quora: Brad Yundt
This workbook takes a look at how to simulate an Excel function in VBA.
We start off with a simple one, just as MID which is already built in to VBA.
Then, for a function like VLOOKUP that is not in Excel VBA, you can use Application.WorksheetFunction.
However, today's question needs to concatenate two ranges and then pass that to Application.WorksheetFunction and this does not work.
The solution, thanks to Brad Yundt, is to do the concatenation inside of Application.Evaluate.
Check out Brad Yundt's answers on Quora: Brad Yundt
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2435.
How to do an Excel function inside of VBA.
In this case a particularly tricky XMATCH using arrays.
Hey, welcome back to the MrExcel netcast.
I'm Bill jelen. This question came in one morning via YouTube and I said well this is going to be super easy, but it turns out that it was remarkably hard.
The issue is using an Excel formula inside of VBA.
Not to return a value to the grid, but just to return an answer to your VBA so you can do something with it.
Let's walk through it.
Alright, so here's the question sent in by Hydrogen Peroxide.
Take a look at this formula.
It's a tricky XMATCH formula where we are joining two different cells together, H4 and E7.
And then the lookin range is all of these values in C concatenated with all of these values and D.
Then an exact match and then looks like they're adding two.
So when we're trying to use an Excel formula inside of VBA, it's one of three situations.
Sometimes the equivalent formula is already built into VBA, and those are just incredibly simple.
So here I have a little MID function.
Taking the MID of A1 starting at 68, returning a length of four.
That gives me the word easy.
Now MID is already built into VBA, so this one we would just say MID of the range of A1, range of A3 range of A4 and that would return the word “easy”.
Those are the easy ones, But there are a lot of functions in Excel that aren't built into VBA.
For example the VLOOKUP function.
So here I have a VLOOKUP working in the grid.
I'm looking up Ed over in that table there in H and I.
Returning the second column and I want zero for an exact match.
So in this case we use Application.WorksheetFunction.VLOOKUP.
And then inside the parentheses pass it the four arguments.
So, B2 dot value for Ed.
H2 to I6 dot value.
Which column do we want?
Column two and then zero for an exact match and that works great.
Ah, but then when this question came in from Hydrogen Peroxide.
The formula here, I build a smaller version just to see if I could get it to work.
The formula here is taking both Mike and Alabama and looking for it in the array of everything in column G concatenated with everything in column H.
And when we look at this in the Evaluate Formula, that is Formula, Evaluate Formula.
You eventually see Mike Alabama joined together.
And then this big array of Andy, Alabama, Barb, Alabama, Chris, Alabama, and so on.
But when I try to do that in VBA, it would not work.
The Look For was easy enough, whatever's in B4 with whatever's in B5.
That worked great.
But then when I try to concatenate G and H together, yeah, there was no way it wasn't going to happen.
I just tried to point to G4:G33, H4:H33 and it turns out that both of those generated a two-dimensional array with, for example, 30 rows and one column.
And then when I tried to join those two arrays together, it gave me an error.
It wasn't going to do it.
I tried many different things.
That's the funny thing about Excel: there's more than one way to solve a problem.
And so it's four or five in the morning, and I'm trying to solve this.
Going through all of these different things.
At one point I was ready to cheat, right?
I mean, the formula is working great in Excel.
Let's just use Excel.
So I went out to an empty cell: Range Z1.
I wrote the formula in it.
It, calculates instantly.
And then assigned whatever we got in Z1 to “a”.
clear Z1 so they never knew we were there and then use that a later in the VBA.
Yeah, but I knew that wasn't the right way to go.
And then then I did something crazy where I actually started out with LookIn1 and then figured out how many items we had.
In this case it was 30 elements.
And then loop from 1 to 30 adding in the corresponding value from Lookin2.
But I realized with 100,000 rows that was going to take forever.
And what's the point if we're going to go through all 100,000 things in a loop, we might as well just be looking.
For Mike, Alabama and be done with it.
I knew that there had to be a way to solve this.
And again, it's about 5:00 in the morning.
I'm thinking who of my friends might be awake right now that could solve this problem.
And I came up with Brad Yundt.
Brad is a long time Excel MVP.
He answers a lot of questions out on Quora.
I almost always start my day out at Quora, just reading a couple of answers.
I saw some code that Brad posted recently that was just short and bulletproof and elegant.
So I sent my horrible code to Brad.
“Brad, do you have a solution for this?” And he was so nice, he said, you know, look, I ran into the same problem when I was trying to use SUMPRODUCT in VBA 15 years ago.
And he pointed me to an article on Experts-Exchange where he had documented the solution, which was just beautiful.
So here, thanks to Brad Yundt and his years of experience is how to solve Hydrogen Peroxide’s problem.
So they LookFor that was always working B4 and B5 concatenated right there in VBA.
Ah, but then to get all of G and all of H concatenated together in VBA.
It's funny -it's here as text.
G4 to G33 ampersand H4 to H33.
All of that in text passed to Application.Evaluate and amazingly that return a array with 30 rows and two columns.
And that was enough that when I passed it to Application.WorksheetFunction.XMATCH.
What are we looking for: we are looking for B4 and B5.
Where are we looking: we are looking there.
Comma zero?
That happened to work.
Alright, we haven't done VBA in quite some time, so there's a good good VBA refresher.
If you like these videos, please, down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Thanks to Brad Yundt.
Thanks to Hydrogen Peroxide.
And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
How to do an Excel function inside of VBA.
In this case a particularly tricky XMATCH using arrays.
Hey, welcome back to the MrExcel netcast.
I'm Bill jelen. This question came in one morning via YouTube and I said well this is going to be super easy, but it turns out that it was remarkably hard.
The issue is using an Excel formula inside of VBA.
Not to return a value to the grid, but just to return an answer to your VBA so you can do something with it.
Let's walk through it.
Alright, so here's the question sent in by Hydrogen Peroxide.
Take a look at this formula.
It's a tricky XMATCH formula where we are joining two different cells together, H4 and E7.
And then the lookin range is all of these values in C concatenated with all of these values and D.
Then an exact match and then looks like they're adding two.
So when we're trying to use an Excel formula inside of VBA, it's one of three situations.
Sometimes the equivalent formula is already built into VBA, and those are just incredibly simple.
So here I have a little MID function.
Taking the MID of A1 starting at 68, returning a length of four.
That gives me the word easy.
Now MID is already built into VBA, so this one we would just say MID of the range of A1, range of A3 range of A4 and that would return the word “easy”.
Those are the easy ones, But there are a lot of functions in Excel that aren't built into VBA.
For example the VLOOKUP function.
So here I have a VLOOKUP working in the grid.
I'm looking up Ed over in that table there in H and I.
Returning the second column and I want zero for an exact match.
So in this case we use Application.WorksheetFunction.VLOOKUP.
And then inside the parentheses pass it the four arguments.
So, B2 dot value for Ed.
H2 to I6 dot value.
Which column do we want?
Column two and then zero for an exact match and that works great.
Ah, but then when this question came in from Hydrogen Peroxide.
The formula here, I build a smaller version just to see if I could get it to work.
The formula here is taking both Mike and Alabama and looking for it in the array of everything in column G concatenated with everything in column H.
And when we look at this in the Evaluate Formula, that is Formula, Evaluate Formula.
You eventually see Mike Alabama joined together.
And then this big array of Andy, Alabama, Barb, Alabama, Chris, Alabama, and so on.
But when I try to do that in VBA, it would not work.
The Look For was easy enough, whatever's in B4 with whatever's in B5.
That worked great.
But then when I try to concatenate G and H together, yeah, there was no way it wasn't going to happen.
I just tried to point to G4:G33, H4:H33 and it turns out that both of those generated a two-dimensional array with, for example, 30 rows and one column.
And then when I tried to join those two arrays together, it gave me an error.
It wasn't going to do it.
I tried many different things.
That's the funny thing about Excel: there's more than one way to solve a problem.
And so it's four or five in the morning, and I'm trying to solve this.
Going through all of these different things.
At one point I was ready to cheat, right?
I mean, the formula is working great in Excel.
Let's just use Excel.
So I went out to an empty cell: Range Z1.
I wrote the formula in it.
It, calculates instantly.
And then assigned whatever we got in Z1 to “a”.
clear Z1 so they never knew we were there and then use that a later in the VBA.
Yeah, but I knew that wasn't the right way to go.
And then then I did something crazy where I actually started out with LookIn1 and then figured out how many items we had.
In this case it was 30 elements.
And then loop from 1 to 30 adding in the corresponding value from Lookin2.
But I realized with 100,000 rows that was going to take forever.
And what's the point if we're going to go through all 100,000 things in a loop, we might as well just be looking.
For Mike, Alabama and be done with it.
I knew that there had to be a way to solve this.
And again, it's about 5:00 in the morning.
I'm thinking who of my friends might be awake right now that could solve this problem.
And I came up with Brad Yundt.
Brad is a long time Excel MVP.
He answers a lot of questions out on Quora.
I almost always start my day out at Quora, just reading a couple of answers.
I saw some code that Brad posted recently that was just short and bulletproof and elegant.
So I sent my horrible code to Brad.
“Brad, do you have a solution for this?” And he was so nice, he said, you know, look, I ran into the same problem when I was trying to use SUMPRODUCT in VBA 15 years ago.
And he pointed me to an article on Experts-Exchange where he had documented the solution, which was just beautiful.
So here, thanks to Brad Yundt and his years of experience is how to solve Hydrogen Peroxide’s problem.
So they LookFor that was always working B4 and B5 concatenated right there in VBA.
Ah, but then to get all of G and all of H concatenated together in VBA.
It's funny -it's here as text.
G4 to G33 ampersand H4 to H33.
All of that in text passed to Application.Evaluate and amazingly that return a array with 30 rows and two columns.
And that was enough that when I passed it to Application.WorksheetFunction.XMATCH.
What are we looking for: we are looking for B4 and B5.
Where are we looking: we are looking there.
Comma zero?
That happened to work.
Alright, we haven't done VBA in quite some time, so there's a good good VBA refresher.
If you like these videos, please, down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Thanks to Brad Yundt.
Thanks to Hydrogen Peroxide.
And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
