Prevent Macro Using MAC Address on the Network

2

Is 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 by using the MAC address for example.

    
asked by anonymous 24.02.2016 / 22:58

1 answer

2

If you want to identify the user , there are two ways. You can use the user ID registered in Excel itself simply by accessing the Application.UserName property. Or, you can access the user name registered in the operating system 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 want to identify 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 slash "/" - you can change it to return a string vector if you wish):

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" access or execution of the worksheet, this is another problem. There are a few important things to consider:

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

  

Originalsourcecodes:

    
    
25.02.2016 / 13:02