Solved: Verify pre-requisites BEFORE running macro

[Simo Bk]

Hi all,

I have a few macros running, but I need to add some code in the beginning to verify if Excel is setup correctly on the machine it's running from, and if not, to stop the macro. Here are the points the macro should validate. If any of them fails, the macro should stop before doing anything.

• Validate that Trust access to Visual Basic project and Trust all installed add-ins and templates are checked in Tools > Macros > Security.
• Verify that the following references are available :
  • Microsoft Windows Common Controls-2 6.0 (SP4)
  • Microsoft Visual Basic for Applications Extensibility 5.3
  • Visual Basic for Applications

Is there anyway to verify this or is it just... Surreal

Bonus question : Is there a way, if one of the preceeding points fail, to have it set up automatically by the macro? (I don't mind security alerts showing on screen... etc.)

Thanks for your help,

Simo

[Simo Bk]

(Other post removed from here. My mistake )

[tca_VB]

I have a similar problem. My excel file was written in 2002, but a user with 2007 cannot run it. He gets: MICROSOFT VISUAL BASIC - COMPILE ERROR IN HIDDEN MODULE: MODULE 2. I'm assuming it's because he doesn't have a library reference set. Since the plan is to have this excel file used by many, I would like to ensure proper library references are set via code.

I'm under 5 posts, so I can't put in a link reference, but there was an article in vbaexpress id#267 that seemed to offer a solution to add a reference library/check reference library - but I can't seem to get it to run. Maybe I need a reference library .

Can anyone fill in the gaps?
Thanks!!

[Bob Phillips]

I thought I had answered this.

This does Trusted access

[vba]

Function IsVBATrusted() As Boolean
Dim oVBC As Object
Application.DisplayAlerts = False
On Error Resume Next
Set oVBC = ThisWorkbook.VBProject.VBComponents.Item(1)
On Error GoTo 0
Application.DisplayAlerts = True
IsVBATrusted = Not oVBC Is Nothing
End Function
[/vba]

I don't have the addins code to hand, I will try and seek it out.

[tca_VB]

Thanks XLD. With that information and the kb (search on GUID) I'm getting closer. My next question, is if the "VBATrusted" code above and the Add References can be run "OnOpen" or does the user need to run the function and macro? (Does it run on open being in ThisWorkbook?)
Also, can the AddReferences be adapted to add more than one reference?
Thanks for the help!

[tca_VB]

Update:
I added the code below to ThisWorkbook. Question - should it run automatically or does it require a intervention to run?

Once run on the MS Excel 2007, there was still a reference missing. On my XL 2002 SP3 I have Microsoft Calendar Control 10.0 This was missing on XL 2007. I finally reconciled by checking Microsoft Works Calendar 5.0 Type Library. Question - can this reference be added in the AddReference part of the code using GUID:
Reference Name: MSACAL
Full Path to Reference: C:\PROGRA~1\MICROS~3\Office10\MSCAL.OCX
Reference GUID: {8E27C92E-1264-101C-8A2F-040224009C02}?
If not, does anyone know what GUID?

Finally, Question - In the AddReference, how can I adapt to add multiple libraries to ensure all match my references?

Thanks for any additional help and all the KBs that got me this far!

 
Function IsVBATrusted() As Boolean
    Dim oVBC As Object
    Application.DisplayAlerts = False
    On Error Resume Next
    Set oVBC = ThisWorkbook.VBProject.VBComponents.Item(1)
    On Error GoTo 0
    Application.DisplayAlerts = True
    IsVBATrusted = Not oVBC Is Nothing
End Function
Sub AddReference()
     'Macro purpose:  To add a reference to the project using the GUID for the
     'reference library
 
    Dim strGUID As String, theRef As Variant, i As Long
 
     'Update the GUID you need below.
    strGUID = "{00020813-0000-0000-C000-000000000046}"
 
     'Set to continue in case of error
    On Error Resume Next
 
     'Remove any missing references
    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
        Set theRef = ThisWorkbook.VBProject.References.Item(i)
        If theRef.isbroken = True Then
            ThisWorkbook.VBProject.References.Remove theRef
        End If
    Next i
 
     'Clear any errors so that error trapping for GUID additions can be evaluated
    Err.Clear
 
     'Add the reference
    ThisWorkbook.VBProject.References.AddFromGuid _
    GUID:=strGUID, Major:=1, Minor:=0
 
     'If an error was encountered, inform the user
    Select Case Err.Number
    Case Is = 32813
         'Reference already in use.  No action necessary
    Case Is = vbNullString
         'Reference added without issue
    Case Else
         'An unknown error was encountered, so alert the user
        MsgBox "A problem was encountered trying to" & vbNewLine _
        & "add or remove a reference in this file" & vbNewLine & "Please check the " _
        & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
    End Select
    On Error GoTo 0
End Sub

[Simo Bk]

I have found a workaround, even though it was not what I planned, this works 90% of the time for me.

I have the users run a batch file instead of the XLS file. That batch file does some checks, then opens Excel if all is good.

Content of the batch file :

@echo off
:: CHECK IF EXCEL IS OPEN
:CHECKEXCEL
tasklist.exe /FI "IMAGENAME eq excel.exe" 2>NULL | find.exe /i "excel.exe" 
IF ERRORLEVEL 1 GOTO CONTINUE
IF ERRORLEVEL 0 GOTO CLOSEEXCEL
 
:: ASK USER TO CLOSE EXCEL IF IT IS OPEN
:CLOSEEXCEL
cls
echo .                         
echo .                         
echo .                         
echo .     ***************************
echo .     !      EXCEL IS OPEN.     !
echo .     ***************************
echo .
echo .
echo .
pause
GOTO CHECKEXCEL
 
:: GO HERE IF EXCEL IS CLOSED
:CONTINUE
cls
:: CHANGE DIRECTORY
echo Accessing _DATA directory...
cd /d "G:\LOREM\IPSUM\_DATA"
echo ... OK
echo .
:: CHANGE REGISTRY TO ENABLE Tools > Macro > Security > Trusted Publishers
echo Setting up Excel security...
start /wait regedit.exe /S "securityTrustedPublishers.reg"
echo ... OK
echo .
:: INSTALL INF TO ADD MSCOMCT2.OCX
echo Registering MSCOMCT2.OCX...
start /wait rundll32.exe setupapi,InstallHinfSection DefaultInstall 132 G:\LOREM\IPSUM\_DATA\MSCOMCT2.INF
echo ... OK
echo .
:: RUN EXCEL MACRO FILE
"G:\LOREM\IPSUM\2008\New report.xls"
GOTO END
 
 
:END

Content of the REG file :

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security]
"AccessVBOM"=dword:00000001
"DontTrustInstalledFiles"=dword:00000000

Hope this can help others in the same situation as I am. Marking this issue as resolved.

Thank you,

Simo

[tca_VB]

Simo,
Thanks for the batch and reg code. I'm trying to implement it, and I'm close.

In the batch file - I'm assuming that I need to create a directory to store the reg file, xls file to open, and MSCOMCT2.INF. I would need another user to create the same directory and store these files then run the batch?

Also, what is MSCOMCT2.OCX and/or MSCOMCT2.INF? Where do I get these files to put into the 'directory of choice'?

Thanks for your solution. I've not worked with batch and reg files before, but I've been able to work through most of it.

tca_VB

[Simo Bk]

Hey tca_VB

Yes, you do need to create a directory that has this data. In my situation, all computers have a drive G: mapped to the same remote server, so that is where I am storing the files.

You should find MSCOMCT2.OCX in you system32 directory (in one of the computers where the macro works)

Here is the content of the MSCOMCT2.INF file :

 
; INF file for MSCOMCT2.ocx
[DefaultInstall]
CopyFiles=install.files
RegisterOCXs=RegisterFiles
[DestinationDirs]
install.files=11
[install.files]
MSCOMCT2.OCX=MSCOMCT2.OCX
[RegisterFiles]
%11%\MSCOMCT2.OCX
[MSCOMCT2.OCX]
file-win32-x86=thiscab
RegisterServer=Yes
FileVersion=6,0,88,4
[version]
signature="$CHICAGO$"
AdvancedINF=2.0
[SourceDisksNames]
1="default",,1

Hope this helps,

Simo

[Simo Bk]

Also note that if you are only running it in two computers, I would highly suggest that you do the configuration changes manually. It won't take that long...