Trust settings for VBA code of an xlsm

[Gasman]

Hi all,
I created an excel workbook for the Community Car Scheme I volunteer for.
With help from another forum, it runs a mailmerge to produce pdf in label format.

The controller got a new computer and decided to install it on that computer not the old one.
When I went over there to install the files, I found that the person who sold the computer put some free office type program on it.
This was being activated when I was trying to open Word. I removed the program from the computer, but the bugger is like a virus and left an entry in the registry and shows up in the references.

I found code to list the references and modify them. I emailed him the xlsm and we put it in the folder with the rest of the scheme documents.

However using Anydesk to get onto his computer, when I try and run my code it complains about the Trust Center.
I added the folder to the Trust center and allowed macroes.

Still I get the error.
What else do I need to check so I can run my VBA code just as I would on my laptop?

TIA

This is the first executable line, and it balks on the first executable line Set oRefs

Sub xlfVBEListReferences()
' Requires References :: Microsoft Visual Basic for Applications Extensibility 5.3
'                        C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
Dim oRef As VBIDE.Reference   ' Item
Dim oRefs As VBIDE.References ' Collection
Dim i As Integer
 
Set oRefs = Application.VBE.ActiveVBProject.References
 
    Debug.Print "Print Time: " & Time & " :: Item - Name and Description"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.Name, oRef.Description, oRef.Major, oRef.Minor
        Next oRef
    Debug.Print vbNewLine
 
    i = 0
    Debug.Print "Print Time: " & Time & " :: Item - Full Path"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.FullPath
        Next oRef
    Debug.Print vbNewLine
 
    i = 0
    ' List the Globally Unique Identifier (GUID) for each library referenced in the current project
    Debug.Print "Print Time: " & Time & " :: Item - GUID"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.GUID
        Next oRef
    Debug.Print vbNewLine
 
End Sub

 

[Gasman]

Ok, got back on to his computer and Trust VBA project was not enabled.
This allowed me to then run the code.

Initial run showed

Print Time: 18:12:00 :: Item - Name and Description
Item 1 VBA Visual Basic For Applications 4 0
Item 2 Excel Microsoft Excel 12.0 Object Library 1 6
Item 3 stdole OLE Automation 2 0
Item 4 Office Microsoft Office 12.0 Object Library 2 4
Item 5 VBIDE Microsoft Visual Basic for Applications Extensibility 5.3 5 3
Print Time: 18:12:00 :: Item - Full Path
Item 1 C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Item 2 C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE
Item 3 C:\Windows\SysWOW64\stdole2.tlb
Item 4 C:\Users\User\AppData\Local\Kingsoft\WPS Office\11.2.0.11516\office6\ksoapi.dll
Item 5 C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
Print Time: 18:12:00 :: Item - GUID
Item 1 {000204EF-0000-0000-C000-000000000046}
Item 2 {00020813-0000-0000-C000-000000000046}
Item 3 {00020430-0000-0000-C000-000000000046}
Item 4 {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Item 5 {0002E157-0000-0000-C000-000000000046}

As you can see that Kingsoft is at Item 4
So I ran the delete sub and that removed it. However each time I run the add/remove subs it complains about 'cannot do in break mode'?
Anyway a subsequen run ensures that Kingsoft has dissapeared.

Then I run the Add sub.

Sub xlfVBEAddReferences()
Dim oRefs As References
Set oRefs = Application.VBE.ActiveVBProject.References
 
    On Error GoTo OnError
    oRefs.AddFromFile "C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE12\MSO.DLL"
 
OnError:
End Sub

Then when I run the list code again, that Kingsoft is back in, albeit at item5.

So I go to that folder and delete the whole folder.
I remove the item again, list to ensure it has gone, and then run the add again.

And the bugger is back in again????
How can I get the MS dll in as Office?

 

[CJ_London]

Have you tried going into the registry and checking all references have been removed?

 

[Gasman]

No, I am doing it via AnyDesk when I have the opportunity.
The wps page mentions a removal tool, but no link to it.
I will look in my registry to see where the entries are.
Are we meant to reboot between deleting and adding references?

 

[CJ_London]

I usually do but don’t think it is always necessary. One entry to look out for is something like ‘load on start’ or ‘start menu’. On my phone so cannot check but I have heard if this is set some apps will do a refresh and effectively do a reinstall

 

[Gasman]

OK, managed to get back on to the computer tonight.

The errant file is C:\Users\User\AppData\Local\Kingsoft\WPS Office\11.2.0.11516\office6\ksoapi.dll

Now I deleted Kingsoft folder from that path on my last access to it.

The ksoapi.dll file was found in these locations on the controller's computer.

Computer\HKEY_CLASSES_ROOT\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.4\0\win32

Computer\HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{B722BCCD-4E68-101B-A2BC-00AA00404770}\InprocServer32

Computer\HKEY_CLASSES_ROOT\WOW6432Node\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.4\0\win32

Computer\HKEY_CURRENT_USER\Software\Classes\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.4\0\win32

Computer\HKEY_CURRENT_USER\Software\Classes\WOW6432Node\CLSID\{B722BCCD-4E68-101B-A2BC-00AA00404770}\InprocServer32

Computer\HKEY_CURRENT_USER\Software\Classes\WOW6432Node\CLSID\{B722BCCD-4E68-101B-A2BC-00AA00404770}\InprocServer32

Computer\HKEY_USERS\S-1-5-21-391862321-1362446027-2051636491-1001\Software\Classes\WOW6432Node\CLSID\{B722BCCD-4E68-101B-A2BC-00AA00404770}\InprocServer32

Computer\HKEY_USERS\S-1-5-21-391862321-1362446027-2051636491-1001_Classes\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.4\0\win32

Computer\HKEY_USERS\S-1-5-21-391862321-1362446027-2051636491-1001_Classes\WOW6432Node\CLSID\{B722BCCD-4E68-101B-A2BC-00AA00404770}\InprocServer32

I am going to double check my computer again, but the MSO.DLL was found in two more locations (the last two)

Computer\HKEY_CLASSES_ROOT\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.4\0\win32

Computer\HKEY_CLASSES_ROOT\WOW6432Node\CLSID\{000C0126-0000-0000-C000-000000000046}\InprocServer32

Computer\HKEY_CLASSES_ROOT\WOW6432Node\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.4\0\win32

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.4\0\win32

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Classes\WOW6432Node\CLSID\{000C0126-0000-0000-C000-000000000046}\InprocServer32

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Classes\WOW6432Node\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.4\0\win32

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Components\D94C8360B8BB1DC41B1950E0F8237563

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Classes\CLSID\{000C0126-0000-0000-C000-000000000046}\InprocServer32

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Classes\TypeLib\{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}\2.4\0\win32

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\12.0\Common\FilesPaths

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Windows\CurrentVersion\SharedDlls

So would it just be a case of replacing his with mine?
I could set up a reg file to add them in one go, could I not?

I know the GUIDs would be different.

This is just like a virus, but I cannot find anything to clean it completely. Besides I have already uninstalled it via Apps in Settings.

 

[Gasman]

I forgot to say I tried to add the reference using the Guid of MS Office ending in 046, but nothing happened.

 

[CJ_London]

Before going further, had another thought. Something else to check is if the docx file is set to open with the kingsoft app as a default. As a check, right click on a docx file and select the Open With option, see what you get. Word should be the default so you may need to select it and then tick the option 'always use this app to open .docx'

With regards registry mods, not sure replacing with yours is a good idea. GUID's are intended to be be unique across the universe so it may be it is unique the the machine.

I would take a copy of the registry in case the following actions have unexpected effects.

Then I would delete all those keys where the ksoapi.dll exists.

I would also check the program file folders and the 4 folders in the username/appdata for a Kingsoft folder and delete if found

Finally repair or preferable reinstall office

If that doesn't work, reinstate the registry

I see references in your registry for Office 12 (i.e. 2007) - it will be different for a later version of access 14 for 2010, 16 for 2013 and later

 

[Gasman]

Thanks @CJ_London
I have already uninstalled WPS and I deleted that folder in Appdata after it came back in.
Why the uninstall did not do it I do not know.

On my laptop the GUID list is the same for all entries for 2007.

Your first paragraph is a good thing to check. Next time I get the chance I will look at that.

The controller mentioned it to the shop he bought the computer from and they said 'Why don't you just uninstall the program'
The controller is not computer savvy and thought that was a good option, which of course a lot of times it would be.

 

[Gasman]

I am thinking now of just replacing the entries values with the file path of the MSO.dll file ?

I can see I made a mistake with the GUID when trying to add by that. I used the 046 when Office is E52

Print Time: 09:22:03 :: Item - Name and Description
Item 1        VBA           Visual Basic For Applications              4             0 
Item 2        Excel         Microsoft Excel 12.0 Object Library        1             6 
Item 3        stdole        OLE Automation               2             0 
Item 4        VBIDE         Microsoft Visual Basic for Applications Extensibility 5.3              5             3 
Item 5        Office        Microsoft Office 12.0 Object Library       2             4 


Print Time: 09:22:03 :: Item - Full Path
Item 1        C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Item 2        C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE
Item 3        C:\Windows\SysWOW64\stdole2.tlb
Item 4        C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
Item 5        C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE12\MSO.DLL


Print Time: 09:22:03 :: Item - GUID
Item 1        {000204EF-0000-0000-C000-000000000046}
Item 2        {00020813-0000-0000-C000-000000000046}
Item 3        {00020430-0000-0000-C000-000000000046}
Item 4        {0002E157-0000-0000-C000-000000000046}
Item 5        {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}

 

[CJ_London]

I really don't know, I've never had your problem - usually just going through and deleting all references to the uninstalled app