1

Context

I have a XLA AddIn macro at work. It is use by 10+ people, thus on multiple PC and Excel versions :

  • 2010 / 2013 / 2016 (14.0 / 15.0 / 16.0)
  • 32bit / 64bit.

The macro runs fine on 2010 / 2013 with 32bit, but make the whole Office (including Word) really slow/buggy on 2016 64bit, although the code does not chrash or lead to unexpected errors.

Where I'm lost

I thus had the Office Code Compatibility Inspector installed and had it inspect my XLA AddIn. I'm very confused by the results :

Total lines scanned:        21788
Total items found:          724
Deprecated items:           537
Removed items:          158
Changed items:              29
Redesigned items:           0
Declare statements:         0
References Found:           0

That's a lot of found items, but what confused me is in the detailled report.

Example 1 :

TYPE:       REMOVED
ITEM:       [mso]FileDialogFilter.Description
URL:        http://go.microsoft.com/fwlink/?LinkID=215358
CODE:       log_erreur "prepare_onglet_donnees", err.description, err.source

The inspector tell me that [mso]FileDialogFilter.Description was removed. The problem is that the Description property is not on a FileDialogFilter but an err object !

Example 2 :

TYPE:       REMOVED
ITEM:       [mso]OfficeDataSourceObject.Columns
URL:        http://go.microsoft.com/fwlink/?LinkID=215358
CODE:       .columns(col_pcsid).cells.font.size = 10

Idem : the Columns object is not on a OfficeDataSourceObject but a Sheet object ! (since there is a With wb_Donnees.Sheets("Données") a few line above.

Example 3 :

TYPE:       DEPRECATION
ITEM:       [mso]ChartFont.Bold
URL:        http://go.microsoft.com/fwlink/?LinkID=215358
CODE:       .cells(l, c).font.bold = true

Idem : not ChartFont but Cells.

And so on...

So... ? :

Do I not read the report correctly or is the Compatibility inspector going crazy ?

braX
  • 11,506
  • 5
  • 20
  • 33
Tibo
  • 383
  • 5
  • 27
  • Never used the Inspector, however, you could code some things for compatibility with each version like: `#If VBA7 Then : Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) : #Else : Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) : #End If` – danieltakeshi Nov 22 '17 at 11:39
  • 3
    It might be just my opinion but: Saying "*… make the whole Office (including Word) really slow/buggy on 2016 64bit, although the code does not chrash or lead to unexpected errors.*" might be too general and broad to answer. What **exactly is** slow and buggy if there are no errors? I mean this would be hard to debug without seeing the code and not knowing what your AddIn does. Therfore you should already have an idea where in the code your issue is to ask a proper question here. Otherwise I see no chance that this question can be answered. (And I personally won't thrust the inspector.) – Pᴇʜ Nov 22 '17 at 11:54
  • @danieltakeshi, I only uses one `Declare statement` (`Public Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (...)`), which is already PtrSafe according to this page [this page](http://www.jkp-ads.com/articles/apideclarations.asp). The inspector find no problem with this declaration so my question was about the 724 other errors detected by the inspecor. I'll have a look anyway since this should be the only difference between 32 and 64bit and since looking at [this page](https://msdn.microsoft.com/en-us/library/ms775123(v=vs.85).aspx), I have some doubts. – Tibo Nov 22 '17 at 12:00
  • @danieltakeshi, + according to the answer to [this question](https://stackoverflow.com/questions/35336118/convert-code-for-32-bit-to-64-bit), the `Long` pointers should have been transformed to `LongPtr`. – Tibo Nov 22 '17 at 12:05
  • @Peh. Any action in Word or Excel (having a Menu or Windows show up, running any Sub...) is slow or won't render at all. "_Therfore you should already have an idea where in the code your issue is to ask a proper question here_" : You realise this is exactly what I'm trying when I'm installing Windows "code Inspector" ? How could I possibly do so if I don't get any errors. – Tibo Nov 22 '17 at 12:13
  • 3
    @tibo As I said if we even don't know what your code does, it's even hard to say anything about where the issue **could** be. An approach to locate the issues can be to remove not necessarily needed code parts from the AddIn and see if the error is still there. This way you find out if the issue is in the removed code part or in the code part you kept active. So hopefully you will come up with a smaller part of code then in which the issue must be. Locating such an issue can be really cumbersome so I always start narrowing down the issue to some code we finally can test and debug. – Pᴇʜ Nov 22 '17 at 12:55
  • @Peh, sure. That's why my post is actually about the inspector and not about my code. Debugging is all the more difficult when the problem appear randomly, on a single PC (the only on running 16.0 64 bit), won't show up for a whole day and is not VBA raising an error but Office have "performance" issue (menu won't show up, screen will freeze, VBA will be very slow). – Tibo Nov 22 '17 at 21:11
  • @Tibo I doubt the inspector will solve your problem. Nor can anyone say if the inspector result is correct or not because we can't proof it on the code. Therefore I won't trust the inspector. You can now keep repeating yourself or start answering some of the points I mentioned in my comments above. But just in case I have some more points due to your last comment: If you say "randomly" means that it doesn't slow down every time? If this is the only PC you tested it on, Setup another (eg. a virtual box) to ensure it is your VBA and not a PC-Setup issue (I won't trust the result of a single PC). – Pᴇʜ Nov 23 '17 at 08:35
  • @Peh, I had a PC installed today with Excel 2016 64bit. I tried on another one with Excel 2013 64bit and was unable to reproduce the problem. I went back on the PC (2016 64bit) when problems happens, tried anything possible to have the macro crash, with no luck. Went back to work, and 15 mins later, the user had Office bugging again. When I arrived, Task managers was showing **10 Excel process**, even after closing all Excel Windows and/or killing Excel from Task Manager. I think that's probably what makes Office unresponsive, but I don't understand why Excel opens so many instances... – Tibo Nov 23 '17 at 16:44
  • @tibo I don't know what your code does therefore I cannot help you. – Pᴇʜ Nov 24 '17 at 07:17

0 Answers0