Possible to Disable/Enable Plugins Quickly in VBA?

[Ghost662]

We have a lot of models at work that rely on a ton of macros, most of which were recorded (using the record feature in the Developer tab) and thus are ridiculously inefficient and cause Excel to crash regularly. Although I'm newer to VBA, I know I can clean them up considerable; however, I'm looking for a temporary quick solution.

Along with the macros, we also use a lot of Excel plugins, but we don't need them running constantly. As such, I tried running some of our more tasking macros with Excel in safe mode and not one of them crashed!

That got me thinking, is there a line or so of VBA code that I can throw at the beginning and end of the macros that disables the plugins, runs the code, then re-enables the plugins? Or perhaps some sort of safe mode toggle (although I think it needs to boot into safe mode)? If you know a way to disable individual plugins, that should also work since we all really use the same set.

I appreciate any help and insight! Thanks in advance!

[SamT]

I am not sure what you mean by "Excel Plugin." I've never heard the term before.

This code is from my collection of handy subs and functions I will use as an example to show how I would handle your particular situation

Function BrowseForFolder(Prompt As String, Optional OpenAt As Variant) As String
Dim ShellApp As Object

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, Prompt, 0, OpenAt)

On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then
BrowseForFolder = ""
End If
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then
BrowseForFolder = ""
End If
Case Else
BrowseForFolder = ""
End Select

ExitFunction:
Set ShellApp = Nothing
End Function

The first thing to do is to select one of your most used and least trustworthy Macros to work on.
Underneath the Macro declaration, (Function BrowseForFolder(Prompt As String, Optional OpenAt As Variant) As String,)
add the following

Function BrowseForFolder(Prompt As String, Optional OpenAt As Variant) As String
Const ProName = "Function BrowseForFolder" 'Name of Macro
Dim Inputs As String
Inputs = "1: " Prompt & "; 2:" & OpenAt '[ & "; 3:" & Next input name] 'use Inputs = "" if Macro has no inputs
Const Location = '["Name of CodePage where Macro is located"]
Dim Section As String
On Error GoTo ErrorHandler

'Your Macro code here
'
'
'

Then, mentally break the Macro code into sections, and scatter Section Variable assignments thru it


Function BrowseForFolder(Prompt As String, Optional OpenAt As Variant) As String
Const ProName = "Function BrowseForFolder"
Dim Inputs As String
Inputs = "1: "Prompt & "; 2:" & OpenAt '[ & "; 3:" & Next input name] 'use Inputs = "" if Macro has no inputs
Const Location = '["Name of CodePage where Macro is located"] 'Look at VBA Titlebar for Name
Dim Section As String
On Error GoTo ErrorHandler

Section = "Section 1"
Dim ShellApp As Object

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, Prompt, 0, OpenAt)

Section = "Section 2"
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

Section = "Section 3"
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then
BrowseForFolder = ""
End If
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then
BrowseForFolder = ""
End If
Case Else
BrowseForFolder = ""
End Select

Section = "Section 4"
ExitFunction:
Set ShellApp = Nothing
End Function

Finally, immediately above the "End Sub" line in your Macros, add the following


Exit Sub
ErrorHandler:
modErrorHandler.ErrorReporter SubName:=ProName, Params:=Inputs, Mod:=Location, Code:=Section, Ernum:=Err
End Sub
You can make the above additions to any and all Macros. If there is any other error handling be sure to copy the line On Error GoTo ErrorHandler after the existing error handler.

Handling the Errors
In VBA, press F4 to insure the Properties Window is visible.

Right Click the ThisWorkbook Name and select Insert>>Module
In the Properties Window, rename the new Module "modErrorHandler" and paste this code into it's Code Page


Sub ErrorReporter(SubName As String, Params As String, Location As String, Code As String, ErrNum As Long)
Const Question As String= vbCrLf & vbCrLf & vbCrLf & "Have you written down this information?"
Dim Message As String
Dim Report As String
Dim Answer As Long

Report = "The problem occured in Code Page " & Location & vbCrLf
Report = Report & "The Problem Macro is " & SubName & vbCrLf
If Params <> "" Then Report = Report & "The Macro inputs were "& Params & vbCrLf
Report = Report & "The Error was " & Error(ErrNum)
Message = Report & Question

Retry:
Answer = MsgBox Prompt:= Message, Buttons:=vbYesNo
If Answer = vbNo Then GoTo Retry
End 'This stops all program flow and should prevent Excel from actually "Crashing."
End sub

Now you are ready to start turning your Macros into Subs by getting rid of all the Select X carraige return Selection instances. (Leave the X.)

[snb]

You can make an inventory of addins.
if a listbox is populated with this inventory you can select/deselect which addins to install/uninstall


Sub M_snb()
For Each it In AddIns
c00 = c00 & vbLf & it.Name & vbTab & "installed :" & it.Installed
Next
MsgBox c00
End Sub

[Paul_Hossler]

You could do a 'control' macro to enable the set you want by user action, or you could include the .Installed = True in the WB open event



Sub Macro1()
'Add in has .Title, .Subject, and .Name -- Use .Title in the ( )
' . Comments is the longer description at the bottom of the dialog
AddIns("Utility Macros").Installed = False
AddIns("Utility Macros").Installed = True
End Sub



14288

[snb]

Sub M_snb()
Application.Dialogs(321).Show
End Sub

[Paul_Hossler]

That got me thinking, is there a line or so of VBA code that I can throw at the beginning and end of the macros that disables the plugins, runs the code, then re-enables the plugins?

I think the OP was looking for a more automated approach.

[SamT]

We have a lot of models [Projects] at work that rely on a ton of macros, most of which were recorded... and [maybe] cause Excel to crash regularly. ... we also use a lot of Excel plugins

Recorded Macros + Custom Procedures + Add-ins, all kludged together to mostly work. I'll bet some of the Procedures were copied from the web.

Disabling which Add-in will cause a project to fail? Or crash? Or was it, as the OP thinks, an inefficient, (but safe,) recorded Macro?

Would you start troubleshooting a problem by disabling parts of the Project?