Code Cleaner menu entry disappeared

[Walter Briscoe]

I use Excel 2003 on a Vista installation.
A couple of weeks ago, I lost Code Cleaner. It no longer appears at the
bottom of my Excel Tools menu. The situation is not catastrophic; it
appears on another installation, but transferring a file between
computers is tedious.
I uninstalled Excel VBA Code Cleaner 5.0.
I downloaded build 5.0.32 from <http://www.appspro.com/Downloads/CodeCle
aner.exe>
Tools > Macro > Security Trust Access to Visual Basic Project is
checked.
I reinstalled Code Cleaner, but still have the problem, even across a
reboot.
I suppose I can try interjecting several reboots.
That fails too.
The file I downloaded on 3 July 2011 was 559KB; today's file is 1073KB.

 

[Walter Briscoe]

In message <[email protected]> of Sun, 21 Jul 2013
12:16:24 in microsoft.public.excel.programming, Walter Briscoe

I use Excel 2003 on a Vista installation.
A couple of weeks ago, I lost Code Cleaner. It no longer appears at the
bottom of my Excel Tools menu. The situation is not catastrophic; it
appears on another installation, but transferring a file between
computers is tedious.
I uninstalled Excel VBA Code Cleaner 5.0.
I downloaded build 5.0.32 from <http://www.appspro.com/Downloads/CodeCle
aner.exe>
Tools > Macro > Security Trust Access to Visual Basic Project is
checked.
I reinstalled Code Cleaner, but still have the problem, even across a
reboot.
I suppose I can try interjecting several reboots.
That fails too.
The file I downloaded on 3 July 2011 was 559KB; today's file is 1073KB.

I have a partial analysis.
C:\Program Files\AppsPro\CodeCleaner\INSTALL.LOG contains
Could not Self-Register: C:\Program Files\AppsPro\CodeCleaner\VBACodeCle
aner.dll
When I run regsvr32 on that file, I get a 0x80004005 response -
insufficient privilege.
I can run in administrator, without error, but that is ineffective.
The problem is probably that the installation does not cater for User
Account Control.
I will check that, when I have time.

 

[GS]

I use the same installer as the author of Code Cleaner. (IN fact, he
gave me his script) The installer will run with elevated privileges
(normally) and so registering the DLL should not be an issue. Note that
this is a COM Addin and so needs to be added via the COM Addins dialog
if the installer didn't work. Otherwise, registration is all that's
required for Excel to know it's there.

Note that Rob installs/registers his COM Addin under HKCU and so it
will not be loaded in automated versions of Excell (as are not regular
addins nor macro security).

Also, you may need to run the installer under 'Run as Administrator'. I
don't have (nor recommend using) Vista (Win6.0) but I just installed
Rob's COM Addin on this machine (Win7 Pro x64 <a.k.a. Win6.1>) and it
ahows up as expected in both XL11/12.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Here's the Registry info...

HKCU\Software\Microsoft\Office\Excel\Addins\

...and make sure the REG_DWORD value is (3) for the LoadBehavior key.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Dave Unger]

Note that Rob installs/registers his COM Addin under HKCU and so it
will not be loaded in automated versions of Excell (as are not regular
addins nor macro security).

Thanks for pointing that out, Garry. I recently ran into that situation, and was wondering "why".

Regards,

DaveU

 

[GS]

Thanks for pointing that out, Garry. I recently ran into that
situation, and was wondering "why".

Regards,

DaveU

Yeah, I have issues with developers who don't respect conventional
protocol on this. As a result I've had to add code to my VB6
frontloaders to uninstall any COMAddins it finds loaded in my automated
instance. PITA!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[anthonylrm]

Can you please explain what an "automated version of Excel" is? If it turns out I have one, does that mean I cannot use Code Cleaner?

I am using my work computer, but I ran as admin, had no install errors, and can see the registry keys, which seem fine.

I would appreciate some help. Thanks.

 

[anthonylrm]

I got it working. The problem is apparently that you can only run the installer as an admin. Thus, the tool is installed for the admin account (under HKCU for the admin account). So if you are in a corporate/"enterprise" environment, as I am, and the account you normally use is a non-admin account, then the tool will not be available to you.

I just had to "manually" create the necessary registry entries from within the non-admin account. I used PowerShell ISE and ran the following code. The first line generated an error (I think because, again, my acct is non-admin) but it seems to have worked fine anyway.

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

New-Item -Path 'HKCU:\Software\Microsoft\Office\Excel\Addins\VBACodeCleaner..CodeCleanerConnect' -Force

New-ItemProperty -Path 'HKCU:\Software\Microsoft\Office\Excel\Addins\VBACodeCleaner.CodeCleanerConnect' -Name Description -PropertyType String -Value 'VBA Code Cleaner 5.0'

New-ItemProperty -Path 'HKCU:\Software\Microsoft\Office\Excel\Addins\VBACodeCleaner.CodeCleanerConnect' -Name FriendlyName -PropertyType String -Value'VBA Code Cleaner 5.0'

New-ItemProperty -Path 'HKCU:\Software\Microsoft\Office\Excel\Addins\VBACodeCleaner.CodeCleanerConnect' -Name LoadBehavior -PropertyType DWord -Value 3



This is on Windows 7 Enterprise with Office 2010.

 

[GS]

This is the default installation key in the registry given that Rob
Bovey (author of CodeCleaner) adheres to Excel development 'best
practices'!

I'm optimistic that he might be willing to modify his installer so the
user has the option to set this utility up for use by all users. This
is merely an issue of changing the registry key from HKCU to HKLM.
Optionally, if you were able (as non-admin) to get PowerShell to put it
under HKCU then it should be no problem to (remove it from there)
relocate to the same path under HKLM.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[anthonylrm]

Thanks for the suggestion. I'll give that a try.