Application.match no longer found in object browser after upgrading to excel 2016

[fullerm]

Longer time user of this site, but rarely have i needed to post a question, as I normally find the anwser, but this one has had me stuck for a couple of weeks (off and on).

A wrote a rather large piece of code, which is basically a database for storing lots of information in mutliple table. Access was probably a better choice in hindsight but Im too far down the track, and I love excel.

This code was written with Excel2007 and I had no errors.

The problem I have is i wrote a macro using the application.match function.

However after upgrading to Excel2016 there are certains function no longer works - they have disappeared.

Further investigation reveals I cannot find the functions in the object browser.

I assume there is some sort of library reference I am missing.

I have the following VBA project References:
* Visual basic for Applications
* Microsoft Excel 16.0 Object Library
* OLE Automation
* Microsoft Forms 2.0 Object Library
* atpvbaen.xls (C:\Program Files\Microsoft Office\Office16\Library\Analysis\atpvbaen.xlam)
* Microsoft Office 16.0 Object Library
* Microsoft Scripting Runtime
* Microsoft HTML Object Library
* Microsoft Internet Controls
* Microsftt Win HTTP Services, version 5.1
* Microsoft ActiveX Data Objects Recordset 2.8 library
* Microsoft ActiveX Data Objects 6.1 Library
* Adobe Acrobat 10.0 Type Library
* Adobe Distiller
* Excel PlugInShell 1.0 Type Library
* EuroTool
* Microsoft Office euro Converter Object Library
* Ref Edit Control
* Solver
* AcessibilityCplAdmin 1.0 Type Library

(I have been activating libraries to try to get these functions back).


The application.match is important as I want to search over two criteria and return the row number of the table.

ElseIf LCase(StationCommodityTable.DataBodyRange(x, 2)) = LCase(StationTable.DataBodyRange(Application.Match(1, Application.CountIfs(StationCommodityTable.DataBodyRange(x, 1), StationTable.ListColumns(1).DataBodyRange, StationCommodityTable.DataBodyRange(x, 2), StationTable.ListColumns(2).DataBodyRange), 0), 2)) And _
             StationCommodityTable.DataBodyRange(x, 2) <>       StationTable.DataBodyRange(Application.Match(1, Application.CountIfs(StationCommodityTable.DataBodyRange(x, 1), StationTable.ListColumns(1).DataBodyRange, StationCommodityTable.DataBodyRange(x, 2), StationTable.ListColumns(2).DataBodyRange), 0), 2).value Then

In this part of the code I am just data checking records and if names are not Capitalised the same, then I correct it. This removes errors occuring later in the code.


The two functions that are missing from this piece of code are "application.match" and "application.countifs".


I sure there is a simply fix here, but I cannot for the life of me work it out.

Any assistance would be muchly appreciated.

[edited just to make the code line up ]

 

[Yongle]

Try
WorksheetFunction.CountIf
WorksheetFunction.Match

 

[fullerm]

Im using the application.match because of the way the errors are dealt with when there are matched.
If I use application.worksheetfunciton.match I do not get the same result.

 

[Yongle]

You did not mention any error messages.If the functions are missing, then you will be getting an error message. What is that message?

I asked you to use WorksheetFunction to help diagnose your error.
- try substituting Application with WorkSheetFunction to see what happens

There have been significant changes in Excel since 2007 and your VBA is also a different version. Both CountIf and Match work for me in Excel2016 and I suspect that your problem lies elsewhere.

a simple test for you to run:

In A1 to A5 enter values 1 to 5
Run this code,

Debug.Print WorksheetFunction.CountIf(Range("A:A"), 4)
Debug.Print Application.CountIf(Range("A:A"), 4)
Debug.Print WorksheetFunction.Match(4, Range("A:A"), 0)
Debug.Print Application.Match(4, Range("A:A"), 0)

The code should return 1 : 1 : 4 : 4 in the immediate window

 

[fullerm]

Hi Guys, Sorry for the delayed response, i have just flown back in and got over my jet lag.

The debug results are as predicted. So looks like I have dramatised my problem.

So there is no problem with application.match but just my line of code that is doing a vlookup of two criteria.
It was working with 32 bit, but once I upgraded to 64 bit the code no longer works.

I have used the code based on this problem and answer: https://www.mrexcel.com/forum/excel-questions/822553-match-function-w-multiple-criteria-vba.html
Using this code as a basis for my multiple criteria match function:
With Application
vResult = .Match(1, .CountIfs(rC1, r1, rC2, r2, rC3, r3), 0)
End With
But it no longer works in 64 bit. Any ideas

 

[Yongle]

We need to work back from where you are having the problem . Let's carry out a few quick checks and see where that leads us

STEP 1
prove to yourself that what is in VBA actually works in the worksheet itself by placing the equivalent formula in the worksheet
=MATCH(1,COUNTIFS(.....),0)

.... and post that complete proven formula in your reply

STEP 2
Use something like this to establish what VBA is trying to do (modify so that it gives you something you can match up to the formula)
Print results to immediate window with Debug.Print
(I can help you with this step if required)

Debug.Print rC1.Address(0, 0), C1.Address(0, 0), rC2.Address(0, 0), C2.Address(0, 0), rC3.Address(0, 0), C3.Address(0, 0)

 

[fullerm]

STEP 1

        Debug.Print Application.CountIfs(StationTable.ListColumns(1).DataBodyRange, StationCommodityTable.DataBodyRange(x, 1), StationTable.ListColumns(2).DataBodyRange, StationCommodityTable.DataBodyRange(x, 2))

Result: 1
there should be only one record. So all looks good at this step


STEP 2

        Debug.Print StationCommodityTable.DataBodyRange(x, 1).Address, StationTable.ListColumns(1).DataBodyRange.Address, StationCommodityTable.DataBodyRange(x, 2).Address, StationTable.ListColumns(2).DataBodyRange.Address

Result: $B$2 $B$2:$B$62206 $C$2 $C$2:$C$62206
The results are correct. However the criteria address (single cell reference) are in one worksheet, the range reference is in another worksheet and the active worksheet is again a different worksheet.

STEP 3

        Debug.Print Evaluate(Application.Match(1, Application.CountIfs(StationTable.ListColumns(1).DataBodyRange, StationCommodityTable.DataBodyRange(x, 1), StationTable.ListColumns(2).DataBodyRange, StationCommodityTable.DataBodyRange(x, 2)), 0))

Result: Error 2042
Tried activating the worksheet containing the range whilst running the code but still get an error


STEP 4

        Debug.Print Application.Match(1, Application.CountIfs(StationCommodityTable.DataBodyRange(x, 1), StationTable.ListColumns(1).DataBodyRange, StationCommodityTable.DataBodyRange(x, 2), StationTable.ListColumns(2).DataBodyRange), 0)

NB: removed the evaluate function from step 4, but should not make any difference
Result: 22 WTF?!
So now it is working...

In Step 3, you may note that I had change the way the Countifs arguments were set (IE changed to r1, rC1, r2, rC2). I had spent so much time trying to work out the problem that I was questioning everything.
In Step 4 I used my original argument method (IE rC1, r1, rC2, r2), and I was getting ERROR 2015. But that was before I change the active sheet to the r1 & r2 worksheet.


My conclusion is that you have to have the right worksheet active for the 'match with multiple criteria' to work properly. The worksheet with the ranges must be active. If not, it errors out.
Testing complete and all good. It take about 30 mintues for the code to run.

This would be why it worked when i had 32 bit and not with 64 bit ... because I cleaned up the code in between.



Thanks Yongle for you're help .... and yes I am kicking my own asss and swearing at myself. I think I have spent more than a month trying to resolve this, knowing it should have been something simple. I guess I wont forget it now.

"We learn so much from out mistakes, it's a pity we dont make more." - quote by me.

 

[fullerm]

OK. after 24 hours of testing, the error is back.

I havent changed the code since it worked and now I get "Run-time Error '1004': Application-defined or object defined error" ... the same as before.

As a side note, I had the code running, cycling through the data records and I scrolled my mouse on the VB code screen and then it suddenly errored and it keeps erroring.

Im going to reboot and see if that helps. The computer has been on for a week or so.

N.B. I tried to recreate the error last night by skipping the lin of code that activated the worksheet with the range on it, and I could not replicate the error. Hence the 24 hours of testing.

N.B.#2 Last week there was a significant excel update, so I dont know if that made a difference in getting my code working.

 

[fullerm]

[I thought post #8 (above) didn't go through, so some of the info is repeated]

I have been testing for nearly 24 hours. I was able to cause the problem again. Whilst the code is running if I click into Visual Basic Editior (already have it open) and then I scroll the mouse up and down I get the error:
"Run-time Error '1004: Application-defined or object-defined error"

Once this happens it will continue to error no matter what I do. Even if I stop and reset the macro.
I either have to close and re-open excel or save the file as a new version. Just foud the second one out

N.B.: the I am using forms and have some code that enables a mouse scroll on a drop down / combo box. I assume this is part of the problem - but I could be wrong.

Another note: Do not substitute '1' for 'True' in the 'match with mylitple criteria' statement, it doesnt like it. based on Countifs arguments set as (r1, rC1, r2, rC2).

This code errors out, becuase 'TRUE' has been used instead of a '1'

        ElseIf LCase(StationCommodityTable.DataBodyRange(x, 2)) = LCase(StationTable.DataBodyRange(Application.Match(TRUE, Application.CountIfs(StationCommodityTable.DataBodyRange(x, 1), StationTable.ListColumns(1).DataBodyRange, StationCommodityTable.DataBodyRange(x, 2), StationTable.ListColumns(2).DataBodyRange), 0), 2)) And _
                     StationCommodityTable.DataBodyRange(x, 2) <> StationTable.DataBodyRange(Application.Match(TRUE, Application.CountIfs(StationCommodityTable.DataBodyRange(x, 1), StationTable.ListColumns(1).DataBodyRange, StationCommodityTable.DataBodyRange(x, 2), StationTable.ListColumns(2).DataBodyRange), 0), 2).value Then

This is the correct way with the use of '1' instead of 'TRUE'

        ElseIf LCase(StationCommodityTable.DataBodyRange(x, 2)) = LCase(StationTable.DataBodyRange(Application.Match(1, Application.CountIfs(StationCommodityTable.DataBodyRange(x, 1), StationTable.ListColumns(1).DataBodyRange, StationCommodityTable.DataBodyRange(x, 2), StationTable.ListColumns(2).DataBodyRange), 0), 2)) And _
                     StationCommodityTable.DataBodyRange(x, 2) <> StationTable.DataBodyRange(Application.Match(1, Application.CountIfs(StationCommodityTable.DataBodyRange(x, 1), StationTable.ListColumns(1).DataBodyRange, StationCommodityTable.DataBodyRange(x, 2), StationTable.ListColumns(2).DataBodyRange), 0), 2).value Then

You may ask why I used TRUE? Because I wanted use the 'TRUE' to remind me of the problem.



Through my testing I was trying to re-create the error based on my original conclusion:

My conclusion is that you have to have the right worksheet active for the 'match with multiple criteria' to work properly. The worksheet with the ranges must be active. If not, it errors out.
Testing complete and all good. It take about 30 mintues for part of the code to run.

However, once I had everything working, I skipped the line of code that changes the active worksheet to the worksheet that contains the ranges in the 'match with mylitple criteria' statement and I did not get the error.

So MY REVISED CONCLUSION is the mouse scroll button being used to scroll up or down through the visual basic code whilst the code is running (even if it is paused) changes something in the background making the 'match with multiple criteria' statement have a critical error that cannot be cleared unless excel is closed & re-opened or you save the file as a new name.

FYI:
Win10, excel 64 bit, logitech G502 mouse. The mouse is new and was attached about a week ago. Previosuly I was using a logitech G5 mouse. So it doesnt seem to matter what mouse I use.

I hope this information helps someone else.

 

[Yongle]

1. Avoid doing anything in the VBA window whilst any code is running - it is not worth the risk!

2. Efficient code generally suffers fewer issues and runs faster- Consider starting a new thread and asking for help