Prevent Macro using MAC Address on the network

Question

A spreadsheet available on the network can be prevented from running a macro if the machine is not "authorized"?

I need to block certain users or PC’s using the MAC address for example.

Answer 1

If you wish to identify the user, there are two ways. You can use registered user identification in Excel itself by simply accessing the property Application.UserName. Or, you can access the registered user name in the operating system account (login), using the following code:

Private Declare Function Get_User_Name Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function GetUserName() As String
    Dim sName As String * 25

    Get_User_Name sName, 25
    GetUserName = Left(sName, InStr(sName, Chr(0)) - 1)
End Function

If you wish to identify the MAC Address you can use the following code (remembering that there is a MAC Address for each network adapter on the computer, then this example code returns a string with all existing ones separated by a "/" bar - you can change it to return a string array if you want):

Function GetMACAddr()
    Dim sComputer As String
    Dim oWMIService As Object
    Dim oAdapters As Object
    Dim oAdapter As Object
    Dim sRet As String

    sComputer = "."
    sRet = ""
    Set oWMIService = GetObject("winmgmts:" & "!\\" & sComputer & "\root\cimv2")
    Set oAdapters = oWMIService.ExecQuery("Select * from Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    For Each oAdapter In oAdapters
        If Len(sRet) > 0 Then
            sRet = sRet + "/" + oAdapter.MACAddress
        Else
            sRet = oAdapter.MACAddress
        End If
    Next oAdapter

    GetMACAddr = sRet
End Function

Now, if these functions can be used to "block" the access or execution of the spreadsheet, this is another problem. There are some important things to consider:

• The user chooses whether or not to run the VBA in Excel when opening the file. Thus, it can simply prevent macros from running and thus open the spreadsheet anyway (having at least access to static content).
• To change the behavior of the worksheet based on the user and/or MAC Address, you can compare the collected information to a preconfigured table. The difficulty there will be maintenance: when you need to add a new "authorized" user/computer, you will need to ensure that all Excel files over the network are updated. So instead of having this fixed table (hard-coded) within the VBA itself, it would be more indicated to have it in a text file accessible by the network or in an access available via Internet. Note, however, that this solution limits the use of the spreadsheet if by chance the user’s computer is without network/Internet.
• If you do not protect (with password) the VBA project in the Excel file, a more experienced user can simply open the code and change it. To protect the project, do so: inside the VBA editor, in the file tree of the project, right-click on the root of the project; then, choose "Vbaproject Properties" and add a password in the "Protection" tab (also ticking "Block project for display"):

Original sources of the codes:

• http://www.mrexcel.com/forum/excel-questions/2252-get-windows-user-name.html
• http://www.excelforum.com/excel-general/388084-mac-address.html