VBA Snippets

Addins_ATP_IsInstalled

Public Function Addin_ATP_Installed(ByVal bCheckAddin As Boolean, _ 
                                    ByVal bCheckVBAAddin As Boolean) As Boolean 
Dim ldate As Long 
   On Error GoTo AnError 
   If bCheckAddin = True Then 
      ldate = Application.Run("EOMONTH", "01/01/1977", 1) 
   End If 
   
   If bCheckVBAAddin = True Then 
      ldate = Application.Run("atpvbaen.xla!EOMONTH", "01/01/1977", 1) 
   End If 
   
   Addins_ATP_Installed = True 
   Exit Function 
AnError: 
   Addins_ATP_Installed = False 
End Function 

Addins_ATP_Load

Public Sub Addin_ATP_Load(ByVal bLoadAddin As Boolean, _ 
                          ByVal bLoadVBAAddin As Boolean, _ 
                 Optional ByVal bInformUser As Boolean = True) 
Dim objaddin As AddIn 
 
   On Error GoTo AnError 
   
   For Each objaddin In AddIns 
     If bLoadAddin = True Then 
        If UCase(objaddin.Name) = "ANALYS32.XLL" Then 
           objaddin.Installed = True 
           If bInformUser = True Then 
              Call MsgBox("'Analysis-ToolPak' add-in has been loaded.")  
           End If 
        End If 
     End If 
     
     If bLoadVBAAddin = True Then 
        If UCase(objaddin.Name) = "ATPVBAEN.XLA" Then 
           objaddin.Installed = True 
           If bInformUser = True Then 
              Call MsgBox("'Analysis-ToolPak - VBA' add-in has been loaded.") 
           End If 
        End If 
     End If 
   Next objaddin 
   
'The following line may not work with non-English user interfaces since the name might have been localised
Addins("Analysis-ToolPak").Installed = True 
   
   Exit Sub 
   
AnError: 
 
End Sub 

Addins_COM_ListAll

Public Sub Addins_COM_ListAll()
Dim icount As Integer
   For icount = 1 To Application.COMAddIns.Count
      Range("A" & icount).Value = Application.COMAddIns(icount).progID
      Range("B" & icount).Value = Application.COMAddIns(icount).Connect
      Range("C" & icount).Value = Application.COMAddIns(icount).Description
   Next icount
End Sub

Addins_VBA_ListAll

Public Sub Addins_VBA_ListAll() 
Dim icount As Integer 
   For icount = 1 To Application.Addins.Count 
      Range("A" & icount).Value = Application.AddIns(icount).Name 
      Range("B" & icount).Value = Application.AddIns(icount).FullName 
      Range("C" & icount).Value = Application.AddIns(icount).Installed 
      Range("D" & icount).Value = Application.AddIns(icount).Path 
   Next icount 
End Sub 

Message_AddinHasExpired

Public Sub AddinHasExpiredExclamation()

Dim sMessage As String
   sMessage = "The " & gsSOLUTION_NAME & " add-in has now expired." & _
              vbCrLf & vbCrLf & _
              "This add-in should be uninstalled." & _
              vbCrLf & vbCrLf & _
              "If you want to find out more about this add-in, " & _
              "please visit the BetterSolutions.com website."

   Call MsgBox(sMessage, vbOKOnly + vbExclamation, "Add-in Expired")
   Call Tracer_Add("MESSAGE", sMessage)
End Sub

Message_AddinNotValidVersion

Public Sub AddinNotValidVersion()

Dim sMessage As String
   sMessage = "The " & gsSOLUTION_NAME & " add-in does not work with this version of Office." & _
              vbCrLf & vbCrLf & _
              "This add-in should be uninstalled." & _
              vbCrLf & vbCrLf & _
              "If you want to find out more about this add-in, " & _
              "please visit the BetterSolutions.com website."

   Call MsgBox(sMessage, vbOKOnly + vbExclamation, "Add-in Not Valid")
   Call Tracer_Add("MESSAGE", sMessage)
End Sub