I was helping a user in a forum and thought I’d publish the solution here in case it could help others as it currently is behind the forum’s PayWall.

The user wanted to know how we could,using VBA, retrieve a site’s SSL Certificate’s Expiry Date/Time.

The Solution

Once again, I decided to turn towards the power of PowerShell!

So if you haven’t already, you really should review and copy the functions I created so we can effectively use PowerShell in VBA. So checkout my initial article on the subject:

VBA - Run PowerShell Command

Have you ever wanted to run a PowerShell command from within VBA and get back a value? Executing a PowerShell Command From VBA Before getting into returning a response, let first look at simply executing a command. There are many instances in which we simply want to run a PowerShell command and don’t need to…

Continue reading →

So once we have that in place, it really is pretty straightforward to do!

'---------------------------------------------------------------------------------------
' Procedure : PS_URL_GETSSLExpiry
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the SSL Certificate Expiry Date of the specified URL
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Dependencies: PS_GetOutput() => https://www.devhut.net/vba-run-powershell-command/
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sURL      : URL to validate the SSL Certificate of
'
' Usage:
' ~~~~~~
' ? PS_URL_GETSSLExpiry("https://google.com")
'   Returns -> 5/15/2023 5:15:33 AM
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2023-03-13
'---------------------------------------------------------------------------------------
Function PS_URL_GETSSLExpiry(sURL As String) As Date
On Error GoTo Error_Handler

    Dim sPSCmd                As String
    
    sPSCmd = "[Net.ServicePointManager]::ServerCertificateValidationCallback = { $true } " & vbCrLf & _
             "$url = '" & sURL & "' " & vbCrLf & _
             "$req = [Net.HttpWebRequest]::Create($url) " & vbCrLf & _
             "$req.GetResponse() | Out-Null " & vbCrLf & _
             "$output = $req.ServicePoint.Certificate.GetExpirationDateString() "
    PS_URL_GETSSLExpiry = CDate(PS_GetOutput(sPSCmd))
    
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: PS_URL_GETSSLExpiry" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Then we can use it by simply doing:

Debug.Print PS_URL_GETSSLExpiry("https://google.com")

which will return something like:

5/15/2023 5:15:33 AM

OR

Dim ExpDt                 As Date

ExpDt = PS_URL_GETSSLExpiry("https://www.google.com")
Debug.Print Format(ExpDt, "yyyy-mm-dd")

which should output

2023-05-15