keep it frosty, serve it cold !!

Posted by Parajuli Sushil 2 weeks Ago 4 Comments 25 Min Read

What’s This About? This post is my best attempt at securing the Visual Basic code in our macro-enabled Excel files using a/symmetric key encryption. 

Why? Because Why Not?
As accountants, we spend countless hours creating modules, routines, forms, and tools in Excel. Sometimes, our creations are so good that they rival enterprise-level software. At this point, we often wonder: “Is there a way to secure this?” One obvious solution that comes to mind is password protecting the Visual Basic code. But here’s the issue: Visual Basic password cracking is fairly easy. With some research and the help of the internet, anyone can break the password.

But imagine if there were a way to secure these Excel files using asymmetric encryption. Better yet, imagine being able to load the Visual Basic scripts from the internet directly into your file. This way, you wouldn’t need to store the VB code within the Excel file at all. You could lock down or tweak your tools remotely, without even touching the Excel file itself. This could be extremely useful when deploying Excel utilities to a large number of users. You could update the code and automatically refresh all files, regardless of whose computer they’re on. 

In This Post, We’ll Discuss How to:
• Protect your macro-enabled Excel file using symmetric key encryption (While asymmetric encryption like AES or RSA is possible, I found it more complicated and time-consuming to implement. I’ll cover that in another post.)
• Ensure that the file is used only on authorized computers
• Implement a remote system to feed Visual Basic code to your Excel files
• Set up a notification system to alert you via email if someone cracks your Visual Basic password protection. This way, you can remotely lock down critical modules and sub-procedures, preventing unauthorized use.

To implement this, we’ll rely on our trusty server-side friend, PHP. People say PHP is a “dead” language, but I don’t understand why. It’s as useful today as ever! The implementation is straightforward, the logic is simple, and the cost is low—I can’t get enough of it.

The Cold Dish Analogy Since writing blog posts can be as boring as reading them, I’m going to have a little fun with this one. Think of it as preparing a cold dish. I hope, the silly headings below will be worth it!

Step 1: Prepare your dish

Make sure your VBA works
Before diving into complex layers, let’s make sure our VBA code is functional. No need to overcomplicate things—our goal here is to test a simple macro that paints Cell A1 of the workbook red when triggered by a button. 

            Sub colorcellred()
    ThisWorkbook.Worksheets("Sheet1").Range("A1").Interior.Color = RGB(255, 0, 0)
End Sub

This is straightforward and ensures that the foundational VBA logic works as intended. But here’s where it gets interesting: instead of embedding this script directly into the workbook, we’ll later explore how to load this code dynamically from an authenticated source via an API. Once the required parameters are passed, the script will execute seamlessly. For now, we’ll focus on encryption first. In the layering stage, we’ll revisit this step to integrate it with the API approach. Stay tuned!

Step 2: Frost It

Symmetric Encryption
To start, we’ll secure the file using symmetric encryption. For this, we’ll use the Vigenère Cipher. Why Vigenère Cipher?
It’s relatively easy to implement yet more secure than Caesar’s Cipher, as it introduces complexity that makes decryption a bit trickier. For our purposes, it strikes the right balance between simplicity and effectiveness.

We’ll create a PHP file named ende.php that handles both encryption and decryption. This file will serve as the backbone of our encryption process, ensuring the data remains secure while still allowing controlled access when needed.

            <?php
// filename ende.php
// Vigenère Cipher function
function vigenereCipher($text, $key, $mode = 'encrypt') {
    $result = '';
    $text = strtoupper($text);
    $key = strtoupper($key);
    $keyIndex = 0;
    $keyLength = strlen($key);
    
    for ($i = 0; $i < strlen($text); $i++) {
        $char = $text[$i];
        
        if (ctype_alpha($char)) { // Only process alphabetic characters
            $shift = ord($key[$keyIndex % $keyLength]) - 65;
            $charCode = ord($char) - 65;
            
            if ($mode === 'encrypt') {
                $newCharCode = ($charCode + $shift) % 26;
            } elseif ($mode === 'decrypt') {
                $newCharCode = ($charCode - $shift + 26) % 26;
            }
            
            $result .= chr($newCharCode + 65);
            $keyIndex++;
        } else {
            $result .= $char; // Non-alphabetic characters remain unchanged
        }
    }
    
    return $result;
}

// Handle incoming request
if ($_SERVER["REQUEST_METHOD"] === "GET") {
    // Get parameters from query string
    $text = isset($_GET['text']) ? $_GET['text'] : null;
    $key = isset($_GET['key']) ? $_GET['key'] : null;
    $mode = isset($_GET['mode']) ? $_GET['mode'] : 'encrypt'; // Default to 'encrypt'

    // Validate inputs
    if ($text && $key) {
        // Check if mode is valid
        if (!in_array($mode, ['encrypt', 'decrypt'])) {
            echo json_encode(["error" => "Invalid mode. Use 'encrypt' or 'decrypt'."]);
            exit;
        }

        // Perform cipher operation
        $result = vigenereCipher($text, $key, $mode);
        echo json_encode(["result" => $result]);
    } else {
        echo json_encode(["error" => "Please provide both 'text' and 'key' parameters."]);
    }
} else {
    echo json_encode(["error" => "Invalid request method. Please use GET."]);
}
?>

Then we will create init.php that will be the first php to be initialized as soon as the excel file in opened.

            <?php
// filename init.php
header('Content-Type: text/plain');

// Validate required parameters
if (!isset($_GET['fn']) || !isset($_GET['md']) || !isset($_GET['vp']) || !isset($_GET['seed'])) {
    die('ERROR: Missing required parameters');
}

$filename = $_GET['fn'];
$macId = $_GET['md'];
$vbaPassword = $_GET['vp'];
$seedValue = $_GET['seed'];

// Function to generate a random string
function generateRandomString($length = 8) {
    $characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
    $charactersLength = strlen($characters);
    $randomString = '';
    for ($i = 0; $i < $length; $i++) {
        $randomString .= $characters[rand(0, $charactersLength - 1)];
    }
    return $randomString;
}

// Read CSV file
$csvFile = 'init.csv';
$rows = array_map('str_getcsv', file($csvFile));
$header = array_shift($rows);
$csv = [];
foreach ($rows as $row) {
    $csv[] = array_combine($header, $row);
}

// Find matching filename
$fileFound = false;
foreach ($csv as &$row) {
    if ($row['filename'] === $filename) {
        $fileFound = true;
        
        // Generate random string for seedplus
        $randomString = generateRandomString(8); // 8-character random string
        $newSeedPlus = $seedValue . $randomString; // Concatenate seedValue with random string
        $row['seedplus'] = $newSeedPlus;
        
        // Generate key string
        $keyString = $filename . $macId . $vbaPassword . $newSeedPlus;
        
        // Get encrypted key
        $encryptUrl = "https://sushilparajuli.com/project_serveitcold/ende.php?text=" . 
                      urlencode($keyString) . 
                      "&key=" . urlencode($newSeedPlus) . 
                      "&mode=encrypt";
        
        $encryptedKey = file_get_contents($encryptUrl);
        $encryptedKeyData = json_decode($encryptedKey, true);
        
        if (isset($encryptedKeyData['result'])) {
            $row['key'] = $keyString;
            
            // Write back to CSV
            $fp = fopen($csvFile, 'w');
            fputcsv($fp, $header);
            foreach ($csv as $csvRow) {
                fputcsv($fp, $csvRow);
            }
            fclose($fp);
            
            echo $encryptedKeyData['result'];
            exit;
        }
        break;
    }
}

if (!$fileFound) {
    echo 'ERROR: File not found';
}
?>

Next step we will implement passwordchecker.php

            <?php
// filename: passwordchecker.php
header('Content-Type: text/plain');

if (!isset($_GET['password']) || !isset($_GET['filename'])) {
    die('ERROR: Missing required parameters');
}

$password = $_GET['password'];
$filename = $_GET['filename'];

// Read CSV file
$csvFile = 'init.csv';
$rows = array_map('str_getcsv', file($csvFile));
$header = array_shift($rows);
$csv = [];
foreach ($rows as $row) {
    $csv[] = array_combine($header, $row);
}

// Find matching filename
foreach ($csv as $row) {
    if ($row['filename'] === $filename) {
        // Get the stored key and seedplus
        $storedKey = $row['key'];
        $seedplus = $row['seedplus'];
        
        // Decrypt the provided password
        $decryptUrl = "https://sushilparajuli.com/project_serveitcold/ende.php?text=" . 
                      urlencode($password) . 
                      "&key=" . urlencode($seedplus) . 
                      "&mode=decrypt";
        
        $decryptedPassword = file_get_contents($decryptUrl);
        $decryptedData = json_decode($decryptedPassword, true);
        
        if (isset($decryptedData['result'])) {
            // Perform case-insensitive comparison
            echo (strcasecmp($decryptedData['result'], $storedKey) === 0) ? '1' : '0';
            exit;
        }
        break;
    }
}

echo 'ERROR: File not found';
?>

All of these data are stored in a csv filled called init.csv in the following format

            filename,macid,lockdown,seedplus,key,vb
coldtest.xlsm,E4-0D-36-59-66-AE,No,20250110184205GZVtyvuM,coldtest.xlsmE4:0D:36:59:66:AE090900920250110184205GZVtyvuM,0909009
efile2,11:22:33:44:55:66,Yes,654321,wxyz5678,123

And lastly we will implement them all in the visual basic code in excel: 

            Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
#Else
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
#End If

Private Declare PtrSafe Function GetMACAddress Lib "iphlpapi.dll" Alias "GetAdaptersInfo" _
    (lpAdapterInfo As Any, ByRef lSize As Long) As Long

Private Type IP_ADAPTER_INFO
    Next As Long
    ComboIndex As Long
    AdapterName(259) As Byte
    Description(129) As Byte
    AddressLength As Long
    Address(7) As Byte
    Index As Long
    Type As Long
    DhcpEnabled As Long
    CurrentIpAddress As Long
    IpAddressList As Long
    GatewayList As Long
    DhcpServer As Long
    HaveWins As Long
    PrimaryWinsServer As Long
    SecondaryWinsServer As Long
    LeaseObtained As Long
    LeaseExpires As Long
End Type

Private Function GetMAC() As String
    
    Dim wmi As Object
    Dim objItem As Object
    Dim strMac As String
    Set wmi = GetObject("winmgmts:\\.\root\cimv2")
    Set objItem = wmi.ExecQuery("SELECT * FROM Win32_NetworkAdapter WHERE NetEnabled = True")
    For Each objItem In objItem
        strMac = objItem.macAddress
        Exit For
    Next
    GetMAC = strMac


End Function

Private Function CheckInternetConnection() As Boolean
    On Error Resume Next
    Dim objHTTP As Object
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    
    objHTTP.Open "GET", "https://www.google.com", False
    objHTTP.Send
    
    If Err.Number = 0 Then
        CheckInternetConnection = True
    Else
        CheckInternetConnection = False
    End If
    
    Set objHTTP = Nothing
    On Error GoTo 0
End Function

Private Function URLEncode(text As String) As String
    Dim i As Integer
    Dim acode As Integer
    Dim char As String
    
    URLEncode = ""
    For i = 1 To Len(text)
        char = Mid(text, i, 1)
        acode = Asc(char)
        If acode < 48 Or acode > 57 And acode < 65 Or acode > 90 And acode < 97 Or acode > 122 Then
            URLEncode = URLEncode & "%" & Hex(acode)
        Else
            URLEncode = URLEncode & char
        End If
    Next i
End Function

Public Sub Workbook_Open()

    ' Check if the VBA project is password-protected
    If Not IsVBAPasswordProtected() Then
        Dim notifyURL As String
        notifyURL = "https://sushilparajuli.com/project_serveitcold/email.php?filename=" & URLEncode(ThisWorkbook.Name)
        
        
        ' Send notification to the server
        Dim objHTTP As Object
        Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
        On Error Resume Next
        objHTTP.Open "GET", notifyURL, False
        objHTTP.Send
        On Error GoTo 0
        Set objHTTP = Nothing

        ' Notify the user and close the workbook
        MsgBox "The VBA project is not password-protected. The file will now close for security reasons.", vbCritical
        ThisWorkbook.Close SaveChanges:=False
        Exit Sub
    Else
        'Debug.Print ("Password Protected")
    End If


    ' Check internet connection
    If Not CheckInternetConnection() Then
        MsgBox "No internet connection. The file will now close.", vbCritical
        ThisWorkbook.Close SaveChanges:=False
        Exit Sub
    End If
    
    ' Get MAC Address
    Dim macAddress As String
    macAddress = GetMAC()
    If macAddress = "ERROR" Then
        MsgBox "Could not get device information. The file will now close.", vbCritical
        ThisWorkbook.Close SaveChanges:=False
        Exit Sub
    End If
    
    ' Generate current timestamp
    Dim seedValue As String
    seedValue = ConvertToAlphabets(Format(Now, "yyyymmddhhnnss"))
    
    ' Prepare API call
    Dim filename As String
    filename = ThisWorkbook.Name
    Dim vbaPassword As String
    vbaPassword = "0909009" ' Set this to your actual VBA password
    
    Dim initURL As String
    initURL = "https://sushilparajuli.com/project_serveitcold/init.php?" & _
              "fn=" & URLEncode(filename) & _
              "&md=" & URLEncode(macAddress) & _
              "&vp=" & URLEncode(vbaPassword) & _
              "&seed=" & seedValue

    ' Make API call
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    
    On Error Resume Next
    objHTTP.Open "GET", initURL, False
    objHTTP.Send
    
    If Err.Number <> 0 Then
        MsgBox "Error connecting to server. The file will now close.", vbCritical
        ThisWorkbook.Close SaveChanges:=False
        Exit Sub
    End If
    On Error GoTo 0
    
    ' Parse response
    Dim response As String
    response = objHTTP.responseText
    
    'Debug.Print (initURL)
    
    Set objHTTP = Nothing
    
    ' Open URL in default browser
    ' Open URL using Shell command
    Shell "cmd.exe /c start """" """ & initURL & """", vbNormalFocus

    
    ' Check if we got a valid key
    If InStr(response, "error") > 0 Then
        MsgBox "Server error. The file will now close.", vbCritical
        ThisWorkbook.Close SaveChanges:=False
        Exit Sub
    End If
    
    ' Prompt for password
    Dim userPassword As String
    userPassword = InputBox("Please enter the file password:", "Password Required")
    
    If userPassword = "" Then
        ThisWorkbook.Close SaveChanges:=False
        Exit Sub
    End If
    
    ' Verify password
    Dim verifyURL As String
    verifyURL = "https://sushilparajuli.com/project_serveitcold/passwordchecker.php?" & _
                "password=" & URLEncode(userPassword) & _
                "&filename=" & URLEncode(filename)
    
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    objHTTP.Open "GET", verifyURL, False
    objHTTP.Send
    
    'Debug.Print (verifyURL)
    Debug.Print (objHTTP.responseText)
    
    If objHTTP.responseText <> "1" Then
        MsgBox "Invalid password. The file will now close.", vbCritical
        ThisWorkbook.Close SaveChanges:=False
    End If
    
    Set objHTTP = Nothing
End Sub


Function ConvertToAlphabets(seedValue As String) As String
    Dim alphabetMapping As String
    Dim result As String
    Dim i As Integer
    Dim currentDigit As String
    
    ' Define mapping from digits to alphabets
    alphabetMapping = "ABCDEFGHIJ" ' 0 ? A, 1 ? B, ..., 9 ? J
    
    ' Initialize result
    result = ""
    
    ' Loop through each character in the seedValue
    For i = 1 To Len(seedValue)
        currentDigit = Mid(seedValue, i, 1)
        If IsNumeric(currentDigit) Then
            ' Convert the digit to the corresponding alphabet
            result = result & Mid(alphabetMapping, CInt(currentDigit) + 1, 1)
        Else
            ' Append non-numeric characters as-is
            result = result & currentDigit
        End If
    Next i
    
    ' Return the result
    ConvertToAlphabets = result
End Function





Public Function IsVBAPasswordProtected() As Boolean
    Dim attempt As Boolean
    
    ' First try: Check through Trust Access settings
    If Application.VBE.ActiveVBProject.Protection = 1 Then
        IsVBAPasswordProtected = True
        Exit Function
    End If
    
    ' Second try: Attempt to access VBComponents
    On Error Resume Next
    attempt = False
    
    Dim comp As Object
    For Each comp In ThisWorkbook.VBProject.VBComponents
        ' Try to access a property that requires VBA project access
        Dim dummy As String
        dummy = comp.Name
        attempt = True
        Exit For
    Next comp
    
    ' If we couldn't even attempt the check, it means we don't have Trust Access
    If Not attempt Then
        ' Show instructions to enable Trust Access
        MsgBox "Please enable 'Trust access to the VBA project object model' in:" & vbNewLine & _
               "File > Options > Trust Center > Trust Center Settings > Macro Settings", _
               vbInformation, "Trust Access Required"
        IsVBAPasswordProtected = True
        Exit Function
    End If
    
    ' If we got an error during the component check, it's protected
    If Err.Number <> 0 Then
        IsVBAPasswordProtected = True
    Else
        IsVBAPasswordProtected = False
    End If
    
    On Error GoTo 0
    'Debug.Print (IsVBAPasswordProtected)
End Function

An email.php that will inform us if the excel file is vba password protected or not. If not we will be sent an email with that notification. 

            <?php
// filename: email.php
if (isset($_GET['filename'])) {
    $filename = htmlspecialchars($_GET['filename'], ENT_QUOTES, 'UTF-8');
    $to = "contact@sushilparajuli.com";
    $subject = "The file $filename has security issues";
    $body = "It seems the VBA password for the $filename has been compromised.";
    $headers = "From: no-reply@sushilparajuli.com";

    if (mail($to, $subject, $body, $headers)) {
        echo "Email sent successfully.";
    } else {
        echo "Failed to send email.";
    }
} else {
    echo "Filename not provided.";
}
?>

Step 3: Must Not Look Delicious

Obfuscation
What Could Have Been Obfuscated (But Wasn’t) In the previous step, there are several layers of obfuscation we could have implemented but chose not to for simplicity. Here’s what we left out:
• Obfuscating Keys During API Communication
While sending keys to and from the API, we could have obfuscated them using techniques like converting ASCII text to hex code or similar methods. Additionally, the appended URL parameters, which are uniquely generated with a seed value, could have served as a crude form of obfuscation for the transferred data.
• Using a Password-Protected Database Instead of CSV Files
In this example, I’ve stored key data in simple CSV files. Ideally, this should be replaced with password-protected database files for added security. However, since this is a basic test and demonstration, I opted for CSVs to keep it straightforward.

A database would add a critical layer of security, but I find connecting to a database through PHP to be one of the most tedious tasks. That said, incorporating a database is an essential step for a production-ready implementation.

Step 4: Only a Few Are Invited

Add more layers
In this implementation, the user will be prompted to enter a password when opening the Excel file. The password will be conveniently provided in a browser tab that automatically opens, allowing the user to copy it.

Here’s how it works:
• The password displayed in the browser is actually an encrypted text.
• Once the user enters it in the dialog box, this encrypted text is sent to passwordchecker.php and ende.php.
•These PHP files decrypt the text and verify if it matches the correct encryption password.
However, the real purpose of this step is not just password verification but to dynamically serve the VBA script to the Excel file directly from the web.

To achieve this, we use locator.php, a script that identifies and retrieves the required sub-procedure for execution within the Excel file. This ensures the VBA code remains secure and can be updated or served remotely as needed.

            <?php
// filename: locator.php
// Get the filename and vbacodephp from the query parameters
$filename = isset($_GET['filename']) ? $_GET['filename'] : '';
$vbacodephp = isset($_GET['vbacodephp']) ? $_GET['vbacodephp'] : '';

// Path to the CSV file
$csvFile = 'modules.csv';

// Read the CSV file
if (($handle = fopen($csvFile, 'r')) !== FALSE) {
    $found = false;

    // Loop through the CSV rows
    while (($data = fgetcsv($handle)) !== FALSE) {
        $csvFilename = $data[0];
        $csvVbacodephp = $data[1];
        $lockdown = $data[2];

        // Check if filename and vbacodephp match
        if ($filename == $csvFilename && $vbacodephp == $csvVbacodephp) {
            $found = true;

            // If lockdown is 0, execute the PHP script with the vbacodephp value
            if ($lockdown == 0) {
                $phpScriptUrl = "https://sushilparajuli.com/project_serveitcold/vbacodephp/" . $vbacodephp . ".php";
                echo file_get_contents($phpScriptUrl);
            } else {
                // If lockdown is 1, deny access
                echo 'Sub AccessDeniedMessage()
                    MsgBox "You do not have the access to use this tool", vbExclamation, "Access Denied"
                End Sub';
            }
            break;
        }
    }

    fclose($handle);

    // If no match was found, echo access denied
    if (!$found) {
        echo 'Sub AccessDeniedMessage()
            MsgBox "You do not have the access to use this tool", vbExclamation, "Access Denied"
        End Sub';
    }
} else {
    // Error reading the CSV file
    echo 'Sub AccessDeniedMessage()
        MsgBox "Error reading the CSV file", vbCritical, "File Error"
    End Sub';
}
?>

A separate php will will exist for actually sending the stored php code in the location /vbacodephp/colorcellred.php

            <?php
//filename: vbacodephp/colorcellred.php
// Set headers to output plain VBA code
header('Content-Type: text/plain');

// Output the VBA code
echo '
Sub colorcellred()
    ThisWorkbook.Worksheets("Sheet1").Range("A1").Interior.Color = RGB(255, 0, 0)
End Sub
';
?>

This will return the code from the server to the excel file which then the excel file executes without saving it in the macro enabled worksheet. Here is the VBA implementation that will allow us to do so. 

            Sub colorcellred()
    Dim http As Object
    Dim url As String
    Dim responseText As String
    Dim vbaCode As String
    Dim moduleName As String
    Dim VBComponent As Object

    subprocname = "colorcellred"

    ' Construct the URL with the workbook name and current VBA procedure
    url = "https://sushilparajuli.com/project_serveitcold/locator.php?filename=" & ThisWorkbook.Name & "&vbacodephp=" & subprocname & "&t=" & CStr(Timer)
    

    ' Create an HTTP object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.Send

    ' Fetch the response from the PHP script
    responseText = http.responseText

    ' Check if the response is code or an error message
    If InStr(responseText, "Sub AccessDeniedMessage") > 0 Then
        ' If access is denied, run the AccessDeniedMessage
        ThisWorkbook.VBProject.VBComponents.Add(1).CodeModule.AddFromString responseText
        Application.Run "AccessDeniedMessage"
        Exit Sub
    Else
        ' If code is received, execute the remote VBA code
        vbaCode = responseText
        moduleName = "TempRemoteCode"

        On Error GoTo Cleanup

        ' Add a new module and insert the code
        Set VBComponent = ThisWorkbook.VBProject.VBComponents.Add(1) ' vbext_ct_StdModule = 1
        VBComponent.Name = moduleName
        VBComponent.CodeModule.AddFromString vbaCode

        ' Execute the downloaded code
        Application.Run moduleName & "." & subprocname
    End If

Cleanup:
    ' Remove the temporary module to maintain security
    On Error Resume Next
    ThisWorkbook.VBProject.VBComponents.Remove VBComponent
    If Err.Number <> 0 Then
        MsgBox "An error occurred while reverting the module: " & Err.Description, vbCritical
    End If
    On Error GoTo 0
End Sub

Step 5: Serve it cold

Here’s a brief demonstration of the implementation described above. At first glance, it might look like a simple macro coloring Cell A1 red, but there’s much more happening behind the scenes. This isn’t just a typical VBA macro—it’s dynamically pulling the script from the internet and executing it locally within the Excel file, all without embedding the code directly into the file. This approach keeps the VBA script secure and allows for remote updates or changes.

Here is the link to the excel file if you want to play around: Excel File