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

spidaman

Board Regular
Joined
Jul 26, 2015
Messages
78
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:

Code:
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,869
Office Version
2010, 2007
Platform
Windows
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.

Code:
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

Board Regular
Joined
Jul 26, 2015
Messages
78
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.

Code:
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,869
Office Version
2010, 2007
Platform
Windows
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

Board Regular
Joined
Jul 26, 2015
Messages
78
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.

Code:
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,869
Office Version
2010, 2007
Platform
Windows
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

Board Regular
Joined
Jul 26, 2015
Messages
78
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,869
Office Version
2010, 2007
Platform
Windows
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

Board Regular
Joined
Jul 26, 2015
Messages
78
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,869
Office Version
2010, 2007
Platform
Windows
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.
 

Forum statistics

Threads
1,081,835
Messages
5,361,596
Members
400,640
Latest member
fruitbros

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top