Home → Techniques and Tips → VBA Programming with @RISK → Launching @RISK from a Visual Basic Macro in Excel

10.13. Launching @RISK from a Visual Basic Macro in Excel

Applies to: @RISK 5.x–7.x, Professional and Industrial Editions
(@RISK Standard Edition cannot be automated with VBA macros.)

I'm writing an elaborate workbook that will use @RISK. Users will have @RISK installed, but I'd like to start @RISK through VBA in my workbook instead of having them click a desktop icon or use the Windows Start button. How can I do it?

To test whether @RISK is already loaded:

If workbook RISK.XLA is open, @RISK is loaded. If that workbook isn't open, @RISK is not loaded. (This is tested in the sample code below for opening @RISK.)

To load @RISK if it is not already loaded:

The Shell method is simplest and will load @RISK asynchronously. This means that you launch @RISK and immediately return control to Excel, as opposed to waiting in the code till @RISK has loaded.

It's a good idea not to hard-code the path to the Risk.exe executable, but instead read it from the System Registry. Here's some sample code:

Option Explicit

' Check whether @RISK is running, and load it if it's not.
Sub loadAtRisk()
    ' This is the folder for the version of @RISK that should be loaded.
    ' If you want to load @RISK 6 or 5 instead, change the 7 to 6 or 5.
    Const AtRiskFolder = "Risk7"
    
    ' If the @RISK add-in is already open, there's no need to open it again.
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks("Risk.xla")
    On Error GoTo 0
    If Not (wb Is Nothing) Then Exit Sub
    
    ' Risk.xla isn't open, so open @RISK by using the Risk.exe launcher.
    ' It will be in a sub-folder under the Palisade main folder.
    Dim sPath As String
    If Palisade_MainDirectory() = "" Then
        MsgBox "No Palisade key found in System Registry - @RISK isn't intalled.", , _
            "loadAtRisk( )"
        Exit Sub
    End If
    sPath = Palisade_MainDirectory() & AtRiskFolder & "\Risk.exe"
    If Dir(sPath) = "" Then
        MsgBox "@RISK not found at " & Chr(13) & sPath, , "loadAtRisk( )"
    Else
        Shell sPath
        ' Control must pass immediately to Excel.
        Exit Sub
    End If
End Sub

Function Palisade_MainDirectory() As String
    ' Adapted 2015-08-31 from
    ' http://www.jpsoftwaretech.com/vba/grab-registry-settings-through-vba-using-wmi/
    Const HKEY_LOCAL_MACHINE = &H80000002
    Dim temp As Object
    Dim sKey As String
    Dim sValue As String
    Dim sData As String

    Set temp = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
        ".\root\default:StdRegProv")

    ' This retrieves the key if Windows is 32-bit.
    ' If the key's not there, sData is is set to a zero-length string.
    sKey = "Software\Palisade"
    sValue = "Main Directory"
    temp.getstringvalue HKEY_LOCAL_MACHINE, sKey, sValue, sData
    
    ' This retrieves the key if Windows is 64-bit.
    ' If the key's not there, sData is is set to a zero-length string.
    If sData = "" Then
        sKey = Replace(sKey, "Palisade", "WOW6432Node\Palisade")
        temp.getstringvalue HKEY_LOCAL_MACHINE, sKey, sValue, sData
    End If
    
    ' Palisade's Main Directory key exists, so it looks like some Palisade software is
    ' installed. Some versions have a trailing \ in this key and others do not, so
    ' make it uniform.
    If Len(sData) > 0 And Right(sData, 1) <> "\" Then sData = sData & "\"
    
    Palisade_MainDirectory = sData
End Function

See also: Shutting Down @RISK from VBA Code.

Additional keywords: Run or open @RISK programmatically

Last edited: 2015-11-20

This page was: Helpful | Not Helpful