Excel 365 VBA Error

[ItalianPlatinum]

Hello this will be a stretch but worth a shot as I am out of options. I have a macro (XLSM) that opens a few files, copies data, pastes into a main workbook called "N Compare", then closes said files used to copy from.

Issue I have is one individual from my team cannot run it. He receives Subscript out of range and is the only user on Microsoft 365. He keeps getting it on the below lines, each one. If i comment them out he can run through no problem but once i introduce one of them the Microsoft 365 user cannot support it. is there a known issue or limitation I am just not factoring in? or any options I have at my disposal?

'Set Range for Composite, & FFWD date
'Composite = Workbooks("N COMPARE").Sheets("Ns COMPARE").Range("Composite")
'FFWD = Workbooks("N COMPARE").Sheets("Ns COMPARE").Range("FFWD")
'Today = Workbooks("N COMPARE").Sheets("Ns COMPARE").Range("Today")

 

[rlv01]

I get the same error when I run it because I do not have Workbooks("N COMPARE") or the sheets, or the ranges on my PC. I suspect your colleague has the same issue. What are the declared data types for your variables Composite, FFWD, and Today?

 

[ItalianPlatinum]

I get the same error when I run it because I do not have Workbooks("N COMPARE") or the sheets, or the ranges on my PC. I suspect your colleague has the same issue. What are the declared data types for your variables Composite, FFWD, and Today?

That is the macro name where the code is run so every user will have that workbook open with that sheet present. Question is why does it only NOT work for the Microsoft 365 excel user

 

[rlv01]

That is the macro name where the code is run so every user will have that workbook open with that sheet present. Question is why does it only NOT work for the Microsoft 365 excel user

Try using the complete workbook name. So for example

    Composite = Workbooks("N COMPARE").Sheets("Ns COMPARE").Range("Composite")

would be replaced with

    Composite = Workbooks("N COMPARE.xlsm").Sheets("Ns COMPARE").Range("Composite")

(assuming it is not N COMPARE.xlsx)

 

[ItalianPlatinum]

Yeah was really hoping i didnt have to go down that road and start referencing the full path each time seeing it is quite long with a lot of sub folders. But I can see if that is the culprit

 

[rlv01]

Yeah was really hoping i didnt have to go down that road and start referencing the full path each time seeing it is quite long with a lot of sub folders. But I can see if that is the culprit

Not the full path, just the file extension. i.e. "N COMPARE" --> "N COMPARE.xlsm", which is what the Workbook.Name property returns.

 

[ItalianPlatinum]

Not the full path, just the file extension. i.e. "N COMPARE" --> "N COMPARE.xlsm", which is what the Workbook.Name property returns.

ok i can try that tomorrow and let you know

 

[Peter_SSs]

I think that the file name extension is likely to be your issue. That is, it is most likely a user setting issue, not an Excel 365 vba error.
Most likely, in Windows Explorer, your 365 user has this setting ..

.. while the other users do not have that setting. Rather than add the file extension into your code in lots of places, at least as a test, check this setting on your 365 user's machine and remove that check mark then try your existing code.

If that is the issue, then rather than trying to force everybody to have that same setting, you could code like this. (Check the actual file extension - yours might be .xlsx not .xlsm for example)

Dim wbNCOMP As Workbook

On Error Resume Next
Set wbNCOMP = Workbooks("N COMPARE")
Set wbNCOMP = Workbooks("N COMPARE.xlsm")
On Error GoTo 0

Composite = wbNCOMP.Sheets("Ns COMPARE").Range("Composite")

 

[ItalianPlatinum]

i think that is the culprit. see below. Yes its only a user error and one user. I will have the user try again and the VBA could would that observe the error and then proceed to the next option? Thanks that is a cool dynamic change

 

[rlv01]

Another possibility is to detect the status by checking the registry

If CreateObject("WScript.Shell").RegRead("HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced\HideFileExt") = 1 Then
    Set wbNCOMP = Workbooks("N COMPARE")
Else
    Set wbNCOMP = Workbooks("N COMPARE.xlsm")
End If

But to use that or to use the one @Peter_SSs suggested you are going to have to make changes to your code from what it is right now. And if you are going to change code anyway, wouldn't it be simpler to just standardize on always using the the file extension? That method that always works regardless of whether the view-filename ext box is checked or not. In other words this is always legal and does not depend on the user's explorer setting.

    Set wbNCOMP = Workbooks("N COMPARE.xlsm")