Excel-VBA - Creating objects with late binding causes activex runtime error 429 depending on file folder location

Question

I have come across a strange behavior in Excel VBA when creating objects using late binding.

System information:

• Windows 10 Enterprise Version: 1803
• Office 365 MSO (16.0.11126.20192)32 bit

Problem description:

I have an empty excel sheet with a very simple macro:

`Option Explicit

Sub test()     Dim ob As Object

    Set ob = CreateObject("Scripting.Dictionary")`

End Sub

Running this macro gives me the famous:

Run time error '429': ActiveX component can't create object

Depending on the folder where i store the macro enabled excel file.

Folders where i can run the macro without errors

• my one drive folder: C:\Users\myusername\OneDrive - MY Company Name\
• all the subfolders inside this one.

Folders where errors occurs

• C:\Users\myusername\
• C:\Users\myusername\Desktop\
• Shared network location ex: \xpto\xpto1\
• C:\Temp\
• C:\Q\ folder created by me and where i tried to mimic the same access rights as in the OneDrive folder where the macro works.

If i use early binding with the correct references the problem is not there anymore!

I have tried following some steps in: https://support.microsoft.com/en-ca/help/828550/you-receive-run-time-error-429-when-you-automate-office-applications

I also searched here for similar questions/answers but none seems to help me.

I cannot seem to get hold of the problem.

Anyone with similar experience?

Thanks

Paulo

[Moved from Excel/ Windows10/ Office 365 for Business]

Answer 1

I think that something must have changed as recently came across a situation where

On Error Resume Next

Set xlapp = GetObject(, "Excel.Application")

If Err Then

    bstartApp = True

    Set xlapp = CreateObject("Excel.Application")

End If

failed with the 429 error and I had to re-write the code to specifically trap that error, rather that being able to rely on "On Error Resume Next", which I have been doing for nigh on 20 years now.

Answer 2

Hi Doug,

Thanks for the reply, indeed this is strange.

Did you manage to have a better understanding about what has changed?

Thanks

Paulo