Getting different results when running on Surface tablet compared to laptop

Question

I'm currently developing my Excel workbook on a Core i7 laptop. I've sent my Excel file to colleagues on MS Surface tablets. These colleagues are getting different behaviours on the Surfaces.

The steps were to: open the Excel file, switch to a tab and send me a log file written in %appdata%.

The tab in question has the following code:

Option Explicit

Private Sub Worksheet_Activate()

    If p Is Nothing Then Set p = New Projects
    p.validateSheet

End Sub

Private Sub Worksheet_Change(ByVal target As Excel.Range)

    If p Is Nothing Then Set p = New Projects
    p.updateSheet target
    
End Sub

Essentially, from my log file, when the laptop switches to the tab it calls Worksheet_Activate. However, the Surfaces are calling Worksheet_Change for each row and cells in the worksheet.

I've confirmed, the Office version is the same in both instances.

Any ideas why this is happening?

Edit #1

Part of the log that was returned. Note "Starting" and "Ending" of updateSheet, and the range changing.

2020-10-13 9:53:08 AM: Starting updateSheet.
2020-10-13 9:53:08 AM: Range: $D$2
2020-10-13 9:53:08 AM: 0001
2020-10-13 9:53:08 AM: Reset credentialWindowCancelled.
2020-10-13 9:53:08 AM: Ending updateSheet.
2020-10-13 9:53:08 AM: Starting updateSheet.
2020-10-13 9:53:08 AM: Range: $E$2
2020-10-13 9:53:08 AM: 0001
2020-10-13 9:53:08 AM: Reset credentialWindowCancelled.
2020-10-13 9:53:08 AM: Ending updateSheet.
2020-10-13 9:53:08 AM: Starting updateSheet.
2020-10-13 9:53:08 AM: Range: $F$2
2020-10-13 9:53:08 AM: 0001
2020-10-13 9:53:08 AM: Reset credentialWindowCancelled.
2020-10-13 9:53:08 AM: Ending updateSheet.
2020-10-13 9:53:08 AM: Starting updateSheet.
2020-10-13 9:53:08 AM: Range: $G$2
2020-10-13 9:53:08 AM: 0001
2020-10-13 9:53:08 AM: Reset credentialWindowCancelled.
2020-10-13 9:53:08 AM: Ending updateSheet.
2020-10-13 9:53:08 AM: Starting updateSheet.
2020-10-13 9:53:08 AM: Range: $H$2
2020-10-13 9:53:08 AM: 0001
2020-10-13 9:53:08 AM: Reset credentialWindowCancelled.
2020-10-13 9:53:08 AM: Ending updateSheet.

Edit #2

How p is initialized in ThisWorkbook code:

Private Sub Workbook_Open()
    ...
    Set p = New Projects
    ...
End Sub

Update #1

The following code, run on my colleagues tablet, kept displaying Worksheet_Activate...

Option Explicit

Private Sub Worksheet_Activate()

    Debug.Print Now() & ": " & "Worksheet_Activate"

End Sub

Private Sub Worksheet_Change(ByVal target As Excel.Range)

    Debug.Print Now() & ": " & "Worksheet_Change"

End Sub

I went back to my logs and found this (truncated to illustrate my point)

2020-10-13 12:51:06 PM: Starting validateSheet.
2020-10-13 12:51:06 PM: Starting FetchIdentification
2020-10-13 12:51:06 PM: Starting sendBitsRequest
...
2020-10-13 12:51:21 PM: Ending sendBitsRequest
2020-10-13 12:51:22 PM: Starting updateSheet.
...
2020-10-13 12:51:35 PM: Ending validateSheet.

Notice "Starting validateSheet" called from Worksheet_Activate and "Starting updateSheet" called from Worksheet_Change. No "Ending validateSheet" nearby either.

There seems to be a transition from Worksheet_Activate to Worksheet_Change...

Also noting, only my machine seems to run this code properly. I was able to replicate on tablets and other laptops. So, all on me apparently.

p is being initialized in a file located under Modules. It's not being instantiated anywhere, however:

Public p As New Projects

It's initialized elsewhere, in ThisWorkbook code: Set p = New Projects — TechFanDan, Oct 13 '20 at 14:52
Without seeing the initialization code it is impossible to determine if the scope of ***p*** is "static enough" or "global enough" for the variable to be "shared" between subs. — Gary's Student, Oct 13 '20 at 14:57
What happens with just a test Change event.. i.e. just a simple `Debug.Print "whatever"` instead of your current code? "However, the Surfaces are calling Worksheet_Change for each row and cells in the worksheet" - how can you tell? More detail would be helpful. — BigBen, Oct 13 '20 at 14:58
@BigBen I added a snippet of my log file showing the calling of Worksheet_Change — TechFanDan, Oct 13 '20 at 15:06
@Gary'sStudent I added the initialization of p in ThisWorkbook. — TechFanDan, Oct 13 '20 at 15:09
When coding a worksheet change event, it is worth setting Application.EnableEvents = False at the beginning and re-enabling at the end with adequate error management. You may be calling the code multiple times. Also you still haven't shown where p is 'Dim'd (and thus the scope) - only where it is Set. — Tragamor, Oct 13 '20 at 15:16
Without the code from the Activate and Change events it's difficult to make sense of or test this. The whole `p` question seems like a red herring here, since if it's not alreadt assigned it just gets set to a new instance. Unless there are side-effects of creating a new `Projects` object... — Tim Williams, Oct 13 '20 at 15:38
Are *all* of your colleagues on a Surface, or are there laptop users for whom it works as expected? — Tim Williams, Oct 13 '20 at 15:44
@TimWilliams so far, 2 tablet users have been able to replicate this. — TechFanDan, Oct 13 '20 at 16:19
But does it run OK on laptops other than the one you used to develop it? — Tim Williams, Oct 13 '20 at 16:29
@TimWilliams I found another laptop, which gets the same behavior as the tablets. Something inherent to my machine/setup. Dammit! — TechFanDan, Oct 13 '20 at 16:55
At this point, the OS and Office versions being equal across tablets and laptops, can I not rule out the code as being the issue? Could it be one of my Excel settings that explains this somehow? — TechFanDan, Oct 14 '20 at 09:44
I think I'm onto something. For the problematic clients, Worksheet_Activate makes changes which also fires Worksheet_Change. I have to turn off events in Worksheet_Activate. However, the question remains, why isn't this causing an issue on my laptop like everywhere else? — TechFanDan, Oct 15 '20 at 17:45

score 0 · Accepted Answer · answered Nov 03 '20 at 15:57

On the tablet, I had on error goto which was catching an error. The error was related to a different browser being instantiated when using and instantiating HTMLDocument.

My machine:

compatMode: BackCompat
documentMode: 11

Other machines:

compatMode: BackCompat
documentMode: 5

I ended up using an InternetExplorer object instead of MSHTML.

Solution here: Forcing documentMode when using MSHTML.HTMLDocument

Getting different results when running on Surface tablet compared to laptop

1 Answers1