How do i get the Excel Version in visual basic?

Question

I am writing an Excel add-in in visual basic and i need to check the security settings to allow macros. I found the following question: How to check from .net code whether "Trust access to the VBA project object model" is enabled or not for an Excel application? but it only works if you know the Excel Version. Then i found this Microsoft page: https://support.microsoft.com/en-us/help/4058123/security-settings-for-com-objects-in-office and now i know how to check for it in the registry.

I have the following:

Protected Function CheckSecuritySettings() As Boolean
    Dim officeVersion As String = Application.Version
    Dim RegistryPath As String = ""

    If My.Computer.Info.OSPlatform.Contains("64") AndAlso checkVersion Then
        RegistryPath = "HKEY_CURRENT_USER\SOFTWARE\Wow6432Node\Microsoft\Office\"
    Else
        RegistryPath = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\"
    End If
    RegistryPath += officeVersion + "\Excel\Security"

    Try
        Dim settings As String = Microsoft.Win32.Registry.GetValue(RegistryPath, "VBAWarnings", 1)
        If settings <> "1" Then
            Dim errorMsg As String = "Security settings are too strict. Please enable all macros in the Trust Center. " + vbNewLine + "If you change the Trust Center settings you need to restart Excel in order for the changes to take effect."
            MsgBox(errorMsg, vbOKOnly)
            DisableButtons()
            Return False
        End If
        settings = Microsoft.Win32.Registry.GetValue(RegistryPath, "AccessVBOM", 1)
        If settings <> "1" Then
            Dim errorMsg As String = "Access to the VBA-Objectmodel in the macro settings in the Trust Center needs to be enabled to run the add-in." + vbNewLine + "If you change the Trust Center settings you need to restart Excel in order for the changes to take effect."
            MsgBox(errorMsg, vbOKOnly)
            DisableButtons()
            Return False
        End If
    Catch ex As Exception
    End Try
    Return True
End Function

BUT: now i need to know how to find out if the Excel Version is 32 or 64 bit, since it changes the location of the registry entries.

My question: What do i need to enter in the code for the checkVersion?

Create a new Excel App object: `dim excelApp = new Microsoft.Office.Interop.Excel.Application()`, then see the `excelApp.Version` and `Marshal.SizeOf(excelApp.HinstancePtr) * 8`. You may also be interested in `excelApp.StartupPath`, `.UILanguage`, `.UserName`, `excelApp.Application.Build` etc. — Jimi, Nov 26 '20 at 19:17
@Jimi thanks for your comment. Version is 10, 13, 16, depending on the Excel Version. The rest does not contain any information on whether or not it is a 32 bit Excel... — Eisenbrei, Nov 27 '20 at 07:44
`Marshal.SizeOf(excelApp.HinstancePtr)` gives you the size of the `Excel.Application` instance pointer. Thus 4 if it's a 32 bit app, 8 when 64 bit. Multiplied by 8, gives you... — Jimi, Nov 27 '20 at 09:12

score 0 · Accepted Answer · answered Nov 27 '20 at 09:28

 Protected Function CheckSecuritySettings() As Boolean
    Dim officeVersion As String = Application.Version
    Dim RegistryPath As String = ""
    Dim excelVersion As Integer = System.Runtime.InteropServices.Marshal.SizeOf(Application.HinstancePtr)

        If My.Computer.Info.OSPlatform.Contains("64") AndAlso excelVersion = 4 Then
        RegistryPath = "HKEY_CURRENT_USER\SOFTWARE\Wow6432Node\Microsoft\Office\"
    Else
        RegistryPath = "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\"
    End If
    RegistryPath += officeVersion + "\Excel\Security"

    Try
        Dim settings As String = Microsoft.Win32.Registry.GetValue(RegistryPath, "VBAWarnings", 1)
        If settings <> "1" Then
            Dim errorMsg As String = "Security settings are too strict. Please enable all macros in the Trust Center. " + vbNewLine + "If you change the Trust Center settings you need to restart Excel in order for the changes to take effect."
            MsgBox(errorMsg, vbOKOnly)
            DisableButtons()
            Return False
        End If
        settings = Microsoft.Win32.Registry.GetValue(RegistryPath, "AccessVBOM", 1)
        If settings <> "1" Then
            Dim errorMsg As String = "Access to the VBA-Objectmodel in the macro settings in the Trust Center needs to be enabled to run the add-in." + vbNewLine + "If you change the Trust Center settings you need to restart Excel in order for the changes to take effect."
            MsgBox(errorMsg, vbOKOnly)
            DisableButtons()
            Return False
        End If
    Catch ex As Exception
    End Try
    Return True
End Function

How do i get the Excel Version in visual basic?

1 Answers1