Dear Tim
Thanks for your comment.
The difference between the error that you get when an Integer variable is passed a value larger that its limit and the situation described below is that the Integer 'overflow' message is by design and documented, whereas the 'Type Mismatch' message in the pivot example is neither appropriate nor documented.
In my book therefore this is a VBA bug,
In the Integer case you simply declare the variable as Long. This is a fix.
With the pivot Type Mismatch you have to pass a Named Range as the SourceData parameter. This is a workaround.
By the way (and slightly off-topic) with modern 32 bit systems the amount of memory used by VBA Integers and Longs is exactly the same. Therefore there is no reason ever to use Integer variables. (OK one minor exception, when sending parameters to ancient API calls that specifically require the Integer type, which is hardly ever)
Finally I had a comment elsewhere from somebody, which I cannot verify, that Excel 2013 does error 2004 (Reference not valid) in this code at the same line where the overflow occurred. This is documented on MSDN for some versions of Excel and is acknowledged there as a bug.
The workaround is to use the specific name 'Database' for the Named Range, which seems to be the name of some pivot internal object. It may be necessary to delete this before you start. So :-
On Error Resume Next: ActiveWorkbook.Names("Database").Delete: On Error GoTo 0
ActiveWorkbook.Names.Add Name:="Database", RefersTo:=rSource ' THIS gets over the 65,000 limit
Dear All, if you have Excel 2013, please test this and report.
Many thanks
Derek Turner
England
+++
_._,___
Tidak ada komentar:
Posting Komentar