VBA - store value after Excel closed and retrieve value when reopened

[spidaman]

Can anyone help with this please?

I want to extract the MAC address (used for IP) then store this value after Excel is closed down. On reopening I'd like to check that the previously stored MAC address = the current MAC address. Ideally I'd like this to work in an Add-In xlam rather than an xlsm, in case this has a bearing on where the MAC address value needs to be stored. The same macro would be run each subsequent time the Add-in is used, checking the MAC address each time.

I'm using this code to obtain and display the MAC address:

Sub Get_MACAddress()


Call GetMACAddress2(myMAcAddress)
        If Len(myMAcAddress) < 4 Then
            MsgBox "Please check your internet connection and try again"
        Else
            MsgBox myMAcAddress
        End If
        
End Sub

Function GetMACAddress2(myMAcAddress) As String
    Dim sComputer As String
    Dim oWMIService As Object
    Dim cItems As Object
    Dim oItem As Object
    
    sComputer = "."
    
    Set oWMIService = GetObject("winmgmts:\\" & sComputer & "\root\cimv2")
    
    Set cItems = oWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
        
    For Each oItem In cItems
        If Not IsNull(oItem.IPAddress) Then myMAcAddress = oItem.macAddress
        Exit For
    Next


    GetMACAddress2 = myMAcAddress
    
End Function

I have tried various methods to save the myMAcAddress value after closing Excel then retrieving it on re-opening with no success so far.

Also I am aware that the code to do this will need to include an if clause to deal with the first run where myMAcAddress value will be empty.

Any help is much appreciated

 

[Leith Ross]

Hello spidaman,

This amended version of your macro stores the MAC address in the Registry of the local machine. If the current MAC address is different from the saved MAC address then a message is displayed showing the previous and current MAC addresses. The first time the macro runs the MAC address is just saved and not compared.

Function GetMACAddress2(myMacAddress) As String


    Dim sComputer As String
    Dim oWMIService As Object
    Dim cItems As Object
    Dim oItem As Object
    
        sComputer = "."
    
        Set oWMIService = GetObject("winmgmts:\\" & sComputer & "\root\cimv2")
    
        Set cItems = oWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
        
        For Each oItem In cItems
            If Not IsNull(oItem.IPAddress) Then myMacAddress = oItem.macAddress
            Exit For
        Next


        oldMacAddress = GetSetting("NetworkConfig", "Adapter", "MAC")
        
        If oldMacAddress = "" Then
            SaveSetting AppName:="NetworkConfig", Section:="Adapter", Key:="MAC", Setting:=myMacAddress
        Else
            If myMacAddress <> oldMacAddress Then
                myMacAddress = "MAC Address has changed" & vbLf _
                        & "Previous Address: " & oldMacAddress & vbLf _
                        & "Current Address:  " & myMacAddress
            End If
        End If
        
        
        GetMACAddress2 = myMacAddress
    
End Function

 

[spidaman]

Hello spidaman,

This amended version of your macro stores the MAC address in the Registry of the local machine. If the current MAC address is different from the saved MAC address then a message is displayed showing the previous and current MAC addresses. The first time the macro runs the MAC address is just saved and not compared.

Function GetMACAddress2(myMacAddress) As String


    Dim sComputer As String
    Dim oWMIService As Object
    Dim cItems As Object
    Dim oItem As Object
    
        sComputer = "."
    
        Set oWMIService = GetObject("winmgmts:\\" & sComputer & "\root\cimv2")
    
        Set cItems = oWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
        
        For Each oItem In cItems
            If Not IsNull(oItem.IPAddress) Then myMacAddress = oItem.macAddress
            Exit For
        Next


        oldMacAddress = GetSetting("NetworkConfig", "Adapter", "MAC")
        
        If oldMacAddress = "" Then
            SaveSetting AppName:="NetworkConfig", Section:="Adapter", Key:="MAC", Setting:=myMacAddress
        Else
            If myMacAddress <> oldMacAddress Then
                myMacAddress = "MAC Address has changed" & vbLf _
                        & "Previous Address: " & oldMacAddress & vbLf _
                        & "Current Address:  " & myMacAddress
            End If
        End If
        
        
        GetMACAddress2 = myMacAddress
    
End Function

Thanks for your help Leith Ross. I'll run this tomorrow and get back to you.

Would this work for iOS as well or would I need to adjust it?

 

[Leith Ross]

Hello spidaman,

Since it uses a standard VBA method to store and retrieve the information from the registry, it should work. But, I cannot guarantee it because I do not own any Apple devices to test it.

 

[spidaman]

Hello spidaman,

This amended version of your macro stores the MAC address in the Registry of the local machine. If the current MAC address is different from the saved MAC address then a message is displayed showing the previous and current MAC addresses. The first time the macro runs the MAC address is just saved and not compared.

Function GetMACAddress2(myMacAddress) As String


    Dim sComputer As String
    Dim oWMIService As Object
    Dim cItems As Object
    Dim oItem As Object
    
        sComputer = "."
    
        Set oWMIService = GetObject("winmgmts:\\" & sComputer & "\root\cimv2")
    
        Set cItems = oWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
        
        For Each oItem In cItems
            If Not IsNull(oItem.IPAddress) Then myMacAddress = oItem.macAddress
            Exit For
        Next


        oldMacAddress = GetSetting("NetworkConfig", "Adapter", "MAC")
        
        If oldMacAddress = "" Then
            SaveSetting AppName:="NetworkConfig", Section:="Adapter", Key:="MAC", Setting:=myMacAddress
        Else
            If myMacAddress <> oldMacAddress Then
                myMacAddress = "MAC Address has changed" & vbLf _
                        & "Previous Address: " & oldMacAddress & vbLf _
                        & "Current Address:  " & myMacAddress
            End If
        End If
        
        
        GetMACAddress2 = myMacAddress
    
End Function

Hello Leith Ross,

The code works well (I just defined the variable oldMacAddress), but it occurred to me that saving the MAC address in the registry may not be suited to the purpose. This is because the intention is that if the file is subsequently copied and used on another device after the original MAC address was captured then the change of MAC address will be detected. At the moment if the file is copied and used on another device the code will just run again, saving the new MAC address in the new registry (correct me if I'm wrong).

I think the only option is to save the MAC address in the xlam file somehow (maybe in ThisWorkbook?) so that the original MAC address is still retained after the file is copied and run on another device.

Is this possible?

Thanks

 

[Leith Ross]

Hello spidaman,

When the macro runs the first time on any computer, it will check if the Registry has the entries created that the macro expects. If they do not exist then they are created. The MAC address of the device will be saved to the local machine's Registry. Every time the macro runs after that, the current MAC address is compared to the saved MAC address in the local Registry. So, copying the macro to a new machine should not be an issue.

 

[spidaman]

Hello spidaman,

When the macro runs the first time on any computer, it will check if the Registry has the entries created that the macro expects. If they do not exist then they are created. The MAC address of the device will be saved to the local machine's Registry. Every time the macro runs after that, the current MAC address is compared to the saved MAC address in the local Registry. So, copying the macro to a new machine should not be an issue.

But what I'd like to do is prevent use of the Add-In on another machine by detecting the change of MAC address if the file is copied. If a different MAC address is detected I intend to produce an error message and exit sub.

 

[Leith Ross]

Hello spidaman,

Then you will need to store the MAC address in a cell on a worksheet. I would set the visible property of the worksheet to xlSheetVeryHidden. This will prevent anyone from seeing the sheet unless they are editing the VBA code. Alternatively once you have the code fully debugged, you could password protect your VBA Project to prevent the code from being seen or altered.

 

[spidaman]

But is that possible if I'm using the project as an Add-In? Would I need to save the GetMacAddress function in This Workbook rather than a module?

 

[Leith Ross]

Hello spidaman,

An Add-in is workbook that is hidden. You still have access to the internals of the Add-In workbook. ThisWorkbook will refer to the Add-In while ActiveWorkbook refers to the workbook running the Add-in.