Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: VBA - store value after Excel closed and retrieve value when reopened
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2015
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,824
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - store value after Excel closed and retrieve value when reopened

    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
    Sincerely,
    Leith Ross

  3. #3
    Board Regular
    Join Date
    Jul 2015
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - store value after Excel closed and retrieve value when reopened

    Quote Originally Posted by Leith Ross View Post
    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?

  4. #4
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,824
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - store value after Excel closed and retrieve value when reopened

    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.
    Sincerely,
    Leith Ross

  5. #5
    Board Regular
    Join Date
    Jul 2015
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - store value after Excel closed and retrieve value when reopened

    Quote Originally Posted by Leith Ross View Post
    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

  6. #6
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,824
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - store value after Excel closed and retrieve value when reopened

    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.
    Sincerely,
    Leith Ross

  7. #7
    Board Regular
    Join Date
    Jul 2015
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - store value after Excel closed and retrieve value when reopened

    Quote Originally Posted by Leith Ross View Post
    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.

  8. #8
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,824
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - store value after Excel closed and retrieve value when reopened

    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.
    Sincerely,
    Leith Ross

  9. #9
    Board Regular
    Join Date
    Jul 2015
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - store value after Excel closed and retrieve value when reopened

    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?

  10. #10
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,824
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - store value after Excel closed and retrieve value when reopened

    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.
    Sincerely,
    Leith Ross

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •