Scraping data from WIFI networks that are available

[johnnyL]

Ultimately, I would like to combine the results from the following onto one sheet:

1) CreateObject("WScript.Shell").Run "cmd /c netsh wlan show networks mode=BSSID" & _
"|clip""", 0, True
specifically:
SSID, Networktype, Authentication, Encryption, MAC Address (BSSID), Signal level, Radiotype, Band, & Channel ... I have the code to do this

2) Data scraped from WLAN_AVAILABLE_NETWORK
specifically:
uNumberOfBssids, uNumberOfPhyTypes, dot11PhyTypes

3) Data scraped from WLAN_BSS_ENTRY
specifically:
lRssi, uLinkQuality, usBeaconPeriod, ullTimestamp


The code I have for getting the data from WLAN_AVAILABLE_NETWORK works perfectly.

The problems I encounter is when I try getting the data from WLAN_BSS_ENTRY.

The problems seem to be multiple.
The gathering of data from WLAN_BSS_ENTRY only occasionally works for the first row of data, most of the time it produces 'garbage' info. All additional rows seem to result in 'garbage' info.

I would tend to think that the issue is a memory pointer issue not being incremented properly.

I have also noticed that the WLAN_AVAILABLE_NETWORK section that works perfectly, doesn't 'jive' with the results from WLAN_BSS_ENTRY ... ie. The SSID's aren't matched up.


Any help would be most appreciated. Probably serves me right for delving into this area of API's and such that I am not used to dealing with.

Perhaps someone such as @Jaafar Tribak or others that are wise to this approach of 'scraping' data could assist.

The following is the code I am currently trying to use to scrape the data from WLAN_AVAILABLE_NETWORK & WLAN_BSS_ENTRY:

Option Explicit
'
    Private Const DOT11_SSID_MAX_LENGTH                 As Long = 32
    Private Const WLAN_MAX_PHY_TYPE_NUMBER              As Long = 8
    Private Const DOT11_RATE_SET_MAX_LENGTH             As Long = 126
'
    Private Type GUID
'        data1                                           As Long                         '   The first 4 bytes of the GUID.
'        data2                                           As Integer                      '   The next 2 bytes of the GUID.
'        data3                                           As Integer                      '   The next 2 bytes of the GUID.
'        data4(7)                                        As Byte                         '   The last 8 bytes of the GUID as an array of bytes.
        Data(15)                                        As Byte                         '   The entire 16 bytes of the GUID as an array of bytes.
    End Type
'
    Private Type WLAN_INTERFACE_INFO
        interfaceGuid                                   As GUID                         '   The globally unique identifier (GUID) of the WLAN interface.
        strInterfaceDescription(255)                    As Byte                         '   The description of the WLAN interface as an array of bytes with a maximum
'                                                                                       '           length of 255 characters.
'        IsState                                         As Long                         '   The current state of the WLAN interface.
    End Type
'
    Private Type DOT11_SSID
        uSSIDLength                                     As Long                         '   The length of the SSID (Service Set Identifier) in bytes.
        ucSSID(DOT11_SSID_MAX_LENGTH - 1)               As Byte                         '   The array of bytes that stores the actual SSID data.
    End Type
'
    Private Type DOT11_MAC_ADDRESS
        DOT11_MAC_ADDRESS(5)                            As Byte
    End Type
'
    Private Enum DOT11_BSS_TYPE
        infrastructure = 1                                                              '   An infrastructure network, which is a traditional Wi-Fi network with access
'                                                                                       '           points (APs).
        independent = 2                                                                 '   An independent or ad-hoc network, where devices communicate directly with
'                                                                                       '           each other without the use of an access point.
        Any_ = 3                                                                        '   Any BSS (Basic Service Set) type, which includes both infrastructure and
'                                                                                       '           independent networks.
    End Enum
'
    Private Enum DOT11_PHY_TYPE
        unknown = 0                                                                     '   An unknown or unspecified PHY type.
        Any_ = 0                                                                        '   Any PHY type. Note that "Any_" is used instead of "Any" due to the reserved
'                                                                                       '           keyword "Any" in VBA
        fhss = 1                                                                        '   Frequency-hopping spread spectrum (FHSS) PHY type
        dsss = 2                                                                        '   Direct-sequence spread spectrum (DSSS) PHY type.
        irbaseband = 3                                                                  '   Infrared baseband PHY type.
        ofdm = 4                                                                        '   Orthogonal frequency-division multiplexing (OFDM) PHY type.
        hrdsss = 5                                                                      '   High-rate DSSS (HRDSSS) PHY type
        erp = 6                                                                         '   Extended rate PHY (ERP) type.
        ht = 7                                                                          '   High throughput (HT) PHY type.
        vht = 8                                                                         '   Very high throughput (VHT) PHY type.
'        IHV_start   = 0x80000000
'        IHV_end     = 0xffffffff
    End Enum
'
    Private Type WLAN_RATE_SET
        uRateSetLength                                  As Long                         '   Length of the rate set in bytes.
        usRateSet(DOT11_RATE_SET_MAX_LENGTH - 1)        As Integer                      '   Array to store rate values with a maximum length of (DOT11_RATE_SET_MAX_LENGTH - 1) elements.
    End Type
'
    Private Type WLAN_AVAILABLE_NETWORK
        strProfileName(511)                             As Byte                         '   * 1: Profile name of the available network, stored as a byte array with a
'                                                                                       '           maximum length of 511 characters.
        dot11Ssid                                       As DOT11_SSID                   '   * 2: SSID of the available network, represented by the DOT11_SSID type.
        dot11BssType                                    As Long                         '   Type of Basic Service Set (BSS) of the network (e.g., infrastructure, independent).
        uNumberOfBssids                                 As Long                         '   * 7: Number of BSSIDs (MAC addresses) for the available network.
        bNetworkConnectable                             As Long                         '   Flag indicating if the network is currently connectable.
        wlanNotConnectableReason                        As Long                         '   Reason code for why the network is not connectable.
        uNumberOfPhyTypes                               As Long                         '   * 8: Number of supported PHY types for the network.
        dot11PhyTypes(WLAN_MAX_PHY_TYPE_NUMBER - 1)     As Long                         '   * 9: Array of supported PHY types (e.g., OFDM, HT) with a maximum length defined
'                                                                                       '           by WLAN_MAX_PHY_TYPE_NUMBER.
        bMorePhyTypes                                   As Long                         '   Flag indicating if there are additional PHY types not included in the array.
        wlanSignalQuality                               As Long                         '   * 3: Signal quality of the network.
        bSecurityEnabled                                As Long                         '   Flag indicating if the network has security enabled.
        dot11DefaultAuthAlgorithm                       As Long                         '   * 10: Default authentication algorithm used by the network (e.g., WPA, WPA2, etc).
        dot11DefaultCipherAlgorithm                     As Long                         '   * 11: Default cipher algorithm used by the network (e.g., TKIP, AES. etc).
        dwFlags                                         As Long                         '   * 6: Additional flags providing information about the network (e.g., hidden network).
        dwReserved                                      As Long                         '   Reserved field for future use.
    End Type
'
    Private Type WLAN_AVAILABLE_NETWORK_LIST                                            '   List of available WLAN networks
        dwNumberOfItems                                 As Long                         '   Number of items in the list
        dwIndex                                         As Long                         '   Index of the list
'        Network                                         As WLAN_AVAILABLE_NETWORK
    End Type
'
    Private Type WLAN_INTERFACE_INFO_LIST
        dwNumberOfItems                                 As Long                         '   The number of WLAN interfaces in the list.
        dwIndex                                         As Long                         '   The index of the current WLAN interface (used for enumeration).
        InterfaceInfo                                   As WLAN_INTERFACE_INFO          '   The WLAN_INTERFACE_INFO structure that holds information about the WLAN interface.
    End Type
'
    Private Type WLAN_BSS_ENTRY
        dot11Ssid                                       As DOT11_SSID                   '   SSID of the BSS
        uPhyId                                          As Long                         '   PHY ID
        dot11Bssid(0 To 5)                              As Byte                         '   BSSID (MAC address)
        dot11BssType                                    As DOT11_BSS_TYPE               '   Type of BSS (infrastructure, independent, or any)
        dot11BssPhyType                                 As DOT11_PHY_TYPE               '   PHY type of the BSS
        lRssi                                           As Long                         '   Received Signal Strength Indicator (RSSI) value
        uLinkQuality                                    As Long                         '   Link quality value
        bInRegDomain                                    As Boolean                      '   Is the BSS in a regulatory domain?
        usBeaconPeriod                                  As Integer                      '   Beacon period of the BSS
        ullTimestamp                                    As Double                       '   Timestamp
        ullHostTimestamp                                As Double                       '   Host timestamp
        usCapabilityInformation                         As Integer                      '   Capability information of the BSS
        ulChCenterFrequency                             As Long                         '   Channel center frequency of the BSS
        wlanRateSet                                     As WLAN_RATE_SET                '   WLAN rate set of the BSS
        ulIeOffset                                      As Long                         '   Information element offset
        ulIeSize                                        As Long                         '   Information element size
    End Type
'
    Private Type WLAN_BSS_LIST                                                          '   List of WLAN_BSS_ENTRY
        dwTotalSize                                     As Long                         '   Total size of the list
        dwNumberOfItems                                 As Long                         '   Number of items in the list
        wlanBssEntries(1)                               As WLAN_BSS_ENTRY               '   Fixed-size array of WLAN_BSS_ENTRY
    End Type
'
    Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, _
            Source As Any, ByVal length As Long)
'
    Declare PtrSafe Function WlanOpenHandle Lib "wlanapi.dll" (ByVal dwClientVersion As Long, _
            ByVal pdwReserved As LongPtr, ByRef pdwNegotiaitedVersion As Long, _
            ByRef phClientHandle As LongPtr) As Long
'
    Declare PtrSafe Function WlanCloseHandle Lib "wlanapi.dll" (ByVal hClientHandle As LongPtr, _
            Optional ByVal pReserved As LongPtr) As Long
'
    Declare PtrSafe Function WlanEnumInterfaces Lib "wlanapi.dll" (ByVal hClientHandle As LongPtr, _
            ByVal pReserved As LongPtr, ByRef ppInterfaceList As LongPtr) As Long
'
    Declare PtrSafe Function WlanScan Lib "wlanapi.dll" (ByVal hClientHandle As LongPtr, _
            ByRef pInterfaceGuid As GUID, Optional ByVal pDot11Ssid As LongPtr, _
            Optional ByVal pIeData As LongPtr, Optional ByVal pReserved As LongPtr) As Long
'
    Declare PtrSafe Function WlanGetAvailableNetworkList Lib "wlanapi.dll" ( _
            ByVal hClientHandle As LongPtr, ByRef pInterfaceGuid As GUID, ByVal dwFlags As Long, _
            ByVal pReserved As LongPtr, ByRef ppAvailableNetworkList As LongPtr) As Long
'
    Private Declare PtrSafe Function WlanGetNetworkBssList Lib "wlanapi.dll" (ByVal hClientHandle As LongPtr, _
            ByRef pInterfaceGuid As Any, ByVal pDot11Ssid As Any, ByVal dot11BssType As Long, _
            ByVal bSecurityEnabled As Long, ByVal pReserved As Any, ByRef ppWlanBssList As LongPtr) As Long
'
    Declare PtrSafe Sub WlanFreeMemory Lib "wlanapi.dll" (ByVal pMemory As LongPtr)
                

Sub GetWiFiConnetionsDetectedV2_CombinedV1()
'
    Dim Arrayrow                As Long
    Dim EnumInterfacesList      As Long
    Dim i                       As Long
    Dim lngAvailable            As Long
    Dim lngReturn               As Long
    Dim lngStartUdtNetwork      As Long
    Dim lngStartUdtWLANbss      As Long
    Dim NetworkBssList          As Long
    Dim NumberOfItems           As Long
    Dim OpenHandleClientHandle  As Long
    Dim OpenHandleClientVersion As Long
    Dim MacAddress              As String
    Dim PhyTypesString          As String
    Dim strProfile              As String
    Dim strSSID                 As String
    Dim HeaderArray             As Variant
    Dim resultArray()           As Variant
    Dim udtNetwork              As WLAN_AVAILABLE_NETWORK
    Dim udtAvailableList        As WLAN_AVAILABLE_NETWORK_LIST
    Dim udtWLANbss              As WLAN_BSS_ENTRY
    Dim wlanBssList             As WLAN_BSS_LIST
    Dim InterfaceInfoList       As WLAN_INTERFACE_INFO_LIST
'
    Application.ScreenUpdating = False
'
    HeaderArray = Array("Profile", "SSID", "Signal Quality", "Connected", "Number of BSSids", _
            "Number of PHY Types", "PHY Types", "Default Auth Algorithm", "Default Cypher Algorithm", _
            "SSID", "PhyId", "Bssid", "BssType", "Rssi", "LinkQuality", "InRegDomain", "BeaconPeriod", "Timestamp", _
            "HostTimestamp", "CapabilityInformation", "ChCenterFrequency", "IeOffset", "IeSize", "wlanRateSet")
'
    Range("A1").CurrentRegion.ClearContents                                                 ' Clear any existing data
'
' Get a Handle
    lngReturn = WlanOpenHandle(2&, 0&, OpenHandleClientVersion, OpenHandleClientHandle)
'
    If lngReturn = 0 Then                                                                   ' If WLAN handle was successful obtained then ...
        lngReturn = WlanEnumInterfaces(ByVal OpenHandleClientHandle, 0&, EnumInterfacesList) '   Enumerate the WLAN Interfaces, (Note: this code only looks at first interface)
        CopyMemory InterfaceInfoList, ByVal EnumInterfacesList, Len(InterfaceInfoList)      '   Copy the enumerated interface information to InterfaceInfoList
'
        lngReturn = WlanScan(OpenHandleClientHandle, InterfaceInfoList.InterfaceInfo.interfaceGuid) '   Refresh the available WIFI networks list
'
        lngReturn = WlanGetAvailableNetworkList(OpenHandleClientHandle, _
                InterfaceInfoList.InterfaceInfo.interfaceGuid, 2&, 0&, lngAvailable)        '   Get network list
        CopyMemory udtAvailableList, ByVal lngAvailable, LenB(udtAvailableList)             '   Copy the network list information to udtAvailableList
        lngStartUdtNetwork = lngAvailable + 8                                               '   Set the starting memory address for the available networks data.
'
        lngReturn = WlanGetNetworkBssList(OpenHandleClientHandle, InterfaceInfoList.InterfaceInfo.interfaceGuid, _
                0&, 1, False, ByVal 0&, NetworkBssList)                                     '   Get BSS List
        CopyMemory wlanBssList, ByVal NetworkBssList, LenB(wlanBssList)                     '   Copy the BSS list information to wlanBssList
        lngStartUdtWLANbss = NetworkBssList + 8                                             '   Set the starting memory address for the BSS list data.
'
' ********************************
' * Scrape the data that we want *
' ********************************
'
        ReDim resultArray(1 To 100, 1 To UBound(HeaderArray, 1) + 1)                        '   Establish the dimensions to use for resultArray, we can correct them later
'
        Do                                                                                  '   Loop through the available WIFI networks found
            Arrayrow = Arrayrow + 1                                                         '       Increment Arrayrow
'
' Populate the Available network structure
            CopyMemory udtNetwork, ByVal lngStartUdtNetwork, Len(udtNetwork)                '       Copy the network WLAN_AVAILABLE_NETWORK data to udtNetwork
            CopyMemory udtWLANbss, ByVal lngStartUdtWLANbss, Len(udtWLANbss)                '       Copy the network WLAN_BSS_ENTRY data to udtWLANbss
'
' Save the Data from WLAN_AVAILABLE_NETWORK
            For i = 0 To UBound(udtNetwork.strProfileName)                                  '       Loop through the bytes of strProfileName
                strProfile = strProfile & Chr(udtNetwork.strProfileName(i))                 '           Save the formatted strProfileName byte to strProfile
            Next                                                                            '       Loop back
'
            strProfile = StrConv(strProfile, vbFromUnicode)                                 '       Strip the bs characters from strProfile
'
            strProfile = Left$(strProfile, InStr(strProfile, Chr(0)) - 1)                   '       Strip rest of bs characters from strProfile
'
            resultArray(Arrayrow, 1) = strProfile                                           '       Save strProfile to resultArray
            strProfile = ""                                                                 '       Clear strProfile
'
            strSSID = Replace(StrConv(udtNetwork.dot11Ssid.ucSSID, vbUnicode), Chr(0), "")  '       Format dot11Ssid.ucSSID & save it to strSSID
'
            If Len(strSSID) < 1 Then strSSID = "Unnamed"                                    '       If strSSID = "" then set strSSID = "Unnamed"
'
            resultArray(Arrayrow, 2) = strSSID                                              '       Save strSSID to resultArray
            resultArray(Arrayrow, 3) = udtNetwork.wlanSignalQuality                         '       Save wlanSignalQuality to resultArray
'
            resultArray(Arrayrow, 4) = udtNetwork.dwFlags                                   '       Save dwFlags to resultArray
            resultArray(Arrayrow, 5) = udtNetwork.uNumberOfBssids                           '       Save uNumberOfBssids to resultArray
            resultArray(Arrayrow, 6) = udtNetwork.uNumberOfPhyTypes                         '       Save uNumberOfPhyTypes to resultArray
'
            For i = 0 To UBound(udtNetwork.dot11PhyTypes)                                   '       Loop through the dot11PhyTypes
                If PhyTypesString <> "" Then                                                '           If this is NOT the first dot11PhyType then ...
                    PhyTypesString = PhyTypesString & "," & udtNetwork.dot11PhyTypes(i)     '               Append the value to PhyTypesString
                Else                                                                        '           Else ...
                    PhyTypesString = udtNetwork.dot11PhyTypes(i)                            '               Save value to PhyTypesString
                End If
            Next                                                                            '       Loop back
'
            PhyTypesString = Replace(PhyTypesString, ",0", "")                              '       Delete the excess zeros from PhyTypesString
'
            resultArray(Arrayrow, 7) = PhyTypesString                                       '       Save PhyTypesString to resultArray
            PhyTypesString = ""                                                             '       Clear PhyTypesString
'
            Select Case udtNetwork.dot11DefaultAuthAlgorithm                                '       Get dot11DefaultAuthAlgorithm and save the equated value to resultArray
                Case 1: resultArray(Arrayrow, 8) = "Open"
                Case 2: resultArray(Arrayrow, 8) = "WEP"
                Case 3: resultArray(Arrayrow, 8) = "WPA"
                Case 4: resultArray(Arrayrow, 8) = "WPA_PSK"
                Case 6: resultArray(Arrayrow, 8) = "WPA2"
                Case 7: resultArray(Arrayrow, 8) = "WPA2_PSK"
                Case 8: resultArray(Arrayrow, 8) = "WPA3"
                Case 9: resultArray(Arrayrow, 8) = "WPA3_SAE"
                Case 10: resultArray(Arrayrow, 8) = "OWE"
                Case 11: resultArray(Arrayrow, 8) = "WPA3_ENT"
            End Select
'
            resultArray(Arrayrow, 9) = udtNetwork.dot11DefaultCipherAlgorithm               '       Save dot11DefaultCipherAlgorithm to resultArray
'
' Save the Data from WLAN_BSS_ENTRY
            strSSID = Replace(StrConv(udtWLANbss.dot11Ssid.ucSSID, vbUnicode), Chr(0), "")  '       Format dot11Ssid.ucSSID & save it to strSSID
'
            If Len(strSSID) < 1 Then strSSID = "Unnamed"                                    '       If strSSID = "" then set strSSID = "Unnamed"
'
            resultArray(Arrayrow, 10) = strSSID                                             '       Save strSSID to resultArray
            resultArray(Arrayrow, 11) = udtWLANbss.uPhyId                                   '       Save uPhyId to resultArray
'
            For i = 0 To 5                                                                  '       Loop through the 6 bytes of the BSSID (MAC ADDRESS)
                MacAddress = MacAddress & Right$("0" & Hex(udtWLANbss.dot11Bssid(i)), 2) & ":"  '       Save the byte to MacAddress
            Next                                                                            '       Loop back
'
            MacAddress = Left$(MacAddress, Len(MacAddress) - 1)                             '
'
            resultArray(Arrayrow, 12) = MacAddress                                          '       Save MacAddress to resultArray
            MacAddress = ""                                                                 '       Clear MacAddress



            resultArray(Arrayrow, 13) = udtWLANbss.dot11BssType                             '       Save dot11BssType to resultArray
            resultArray(Arrayrow, 14) = udtWLANbss.lRssi                                    '       Save lRssi to resultArray
            resultArray(Arrayrow, 15) = udtWLANbss.uLinkQuality                             '       Save uLinkQuality to resultArray
            resultArray(Arrayrow, 16) = udtWLANbss.bInRegDomain                             '       Save bInRegDomain to resultArray
            resultArray(Arrayrow, 17) = udtWLANbss.usBeaconPeriod                           '       Save usBeaconPeriod to resultArray
            resultArray(Arrayrow, 18) = udtWLANbss.ullTimestamp                             '       Save ullTimestamp to resultArray
            resultArray(Arrayrow, 19) = udtWLANbss.ullHostTimestamp                         '       Save ullHostTimestamp to resultArray
            resultArray(Arrayrow, 20) = udtWLANbss.usCapabilityInformation                  '       Save usCapabilityInformation to resultArray
            resultArray(Arrayrow, 21) = udtWLANbss.ulChCenterFrequency                      '       Save ulChCenterFrequency to resultArray
            resultArray(Arrayrow, 22) = udtWLANbss.ulIeOffset                               '       Save ulIeOffset to resultArray
            resultArray(Arrayrow, 23) = udtWLANbss.ulIeSize                                 '       Save ulIeSize to resultArray
'            resultArray(Arrayrow, 24) = udtWLANbss.wlanRateSet                             ' Compile error on this line
'
            NumberOfItems = NumberOfItems + 1                                               '       Increment NumberOfItems
            lngStartUdtNetwork = lngStartUdtNetwork + Len(udtNetwork)                       '       Advance the lngStartUdtNetwork memory address
            lngStartUdtWLANbss = lngStartUdtWLANbss + Len(udtWLANbss)                       '       Advance the lngStartUdtWLANbss memory address
        Loop Until NumberOfItems = udtAvailableList.dwNumberOfItems                         '   Loop back if more WIFI networks are available
'
        Range("A1").Resize(, UBound(HeaderArray, 1) + 1) = HeaderArray                      '   Display HeaderArray to the sheet
'
        resultArray = ReDimPreserve(resultArray, Arrayrow, UBound(HeaderArray, 1) + 1)      '   Resize resultArray to actual dimensions that we ended up needing
'
        Range("A2").Resize(UBound(resultArray, 1), UBound(resultArray, 2)) = resultArray    '   Display resultArray to the sheet
'
        With Range("C2:I" & Range("I" & Rows.Count).End(xlUp).Row)                          '   Center the data in the cells in columns C:I
            .HorizontalAlignment = xlCenter
              .VerticalAlignment = xlCenter
        End With
'
        ActiveSheet.UsedRange.EntireColumn.AutoFit                                          '   Autofit the widths of the used columns in the sheet
    End If
'
    WlanFreeMemory EnumInterfacesList
    WlanFreeMemory lngAvailable
    WlanFreeMemory NetworkBssList
    WlanCloseHandle OpenHandleClientHandle                                                  ' Release handle
'
    Application.ScreenUpdating = True                                                       ' Turn ScreenUpdating back on
End Sub


Public Function ReDimPreserve(ArrayNameToResize, NewRowUbound, NewColumnUbound)
'
' Code inspired by Control Freak
'
' Preserve Original data & LBounds & Redim both dimensions for a 2D array
'
' example usage of the function:
' ResizedArrayName = ReDimPreserve(ArrayNameToResize,NewRowSize,NewColumnSize)
' ie.
' InputArray = ReDimPreserve(InputArray,10,20)
'
' This function will keep the LBounds (Lower Bounds) of the original array.
'
    Dim NewColumn                   As Long, NewRow                      As Long
    Dim OldColumnLbound             As Long, OldRowLbound                As Long
    Dim OldColumnUbound             As Long, OldRowUbound                As Long
    Dim NewResizedArray()           As Variant
'
    ReDimPreserve = False
'
    If IsArray(ArrayNameToResize) Then                                                                      ' If the variable is an array then ...
           OldRowLbound = LBound(ArrayNameToResize, 1)                                                      '   Save the original row Lbound to OldRowLbound
        OldColumnLbound = LBound(ArrayNameToResize, 2)                                                      '   Save the original column Lbound to OldColumnLbound
'
        ReDim NewResizedArray(OldRowLbound To NewRowUbound, OldColumnLbound To NewColumnUbound)             '   Create a New 2D Array with same Lbounds as the original array
'
        OldRowUbound = UBound(ArrayNameToResize, 1)                                                         '   Save row Ubound of original array
        OldColumnUbound = UBound(ArrayNameToResize, 2)                                                      '   Save column Ubound of original array
'
        For NewRow = OldRowLbound To NewRowUbound                                                           '   Loop through rows of original array
            For NewColumn = OldColumnLbound To NewColumnUbound                                              '       Loop through columns of original array
                If OldRowUbound >= NewRow And OldColumnUbound >= NewColumn Then                             '           If more data to copy then ...
                    NewResizedArray(NewRow, NewColumn) = ArrayNameToResize(NewRow, NewColumn)               '               Append rows/columns to NewResizedArray
                End If
            Next                                                                                            '       Loop back
        Next                                                                                                '   Loop back
'
        Erase ArrayNameToResize                                                                             '   Free up the memory the Original array was taking
'
        If IsArray(NewResizedArray) Then ReDimPreserve = NewResizedArray
    End If
End Function

 

[Jaafar Tribak]

@Jaafar Tribak, sorry if I was not clear enough in my last post ...

The compile error occurred at the line:

    Private Enum LongPtr

I solved the compile error by changing that line to:

    Public Enum LongPtr

Again, sorry for not being clear enough in my previous post. After I made that one change, Excel 2007 & above versions that I have all tested fine.

No problem. I know you fixed the problem by making the Enum Public but was that in the bas module or in the UserForm module ?

 

[johnnyL]

OOPS, sorry, the compile error actually occurred in the bas module at the line:

Public lHandle                  As LongPtr

 

[johnnyL]

I didn't include the Userform module, so the error & correction I made would be in the bas module.

 

[Jaafar Tribak]

OOPS, sorry, the compile error actually occurred in the bas module at the line:

Public lHandle                  As LongPtr

Ok I see.

Instead of changing the Enum to Private, it is preferable to leave it as is and change the declaration of the the lHandle variable from Public to Private

So you will have :

    Private Enum LongPtr
        [_]
    End Enum

And then change this :

Public lHandle                  As LongPtr

To this :

Private lHandle                  As LongPtr

 

[johnnyL]

I tried that initially, but it basically just produced the same compile error on the next line of the bas module:

    Declare Function WlanOpenHandle Lib "wlanapi.dll" ( _
            ByVal dwClientVersion As Long, ByVal pdwReserved As LongPtr, _
            ByRef pdwNegotiaitedVersion As Long, ByRef phClientHandle As LongPtr) As Long   ' Declare a function to open a handle to WLAN (Wireless Local Area Network)

 

[Jaafar Tribak]

I tried that initially, but it basically just produced the same compile error on the next line of the bas module:

    Declare Function WlanOpenHandle Lib "wlanapi.dll" ( _
            ByVal dwClientVersion As Long, ByVal pdwReserved As LongPtr, _
            ByRef pdwNegotiaitedVersion As Long, ByRef phClientHandle As LongPtr) As Long   ' Declare a function to open a handle to WLAN (Wireless Local Area Network)

One of the *good programming practices* that I personally always try to follow is to not use Public unless it is absolutely necessary.
The reason you are getting those compile errors is because of all the Public declarartions including the API declares.

Since all the module level variables as well as the api functions are only being used in the bas module (module scope), there is really no need to declare any of them as Public.

So, if I were you, I would make sure I have all the module level variables declared as Private (in this particular case, we only needed to change the lHandle var) and I would also declare all the apis as Private.

Note that if you don't specify the Private keyword before an api declaration, it defaults to Public !
In other words, the compiler interpretes this ;

    Declare Function WlanOpenHandle Lib "wlanapi.dll" ( _
            ByVal dwClientVersion As Long, ByVal pdwReserved As LongPtr, _
            ByRef pdwNegotiaitedVersion As Long, ByRef phClientHandle As LongPtr) As Long   ' Declare a function to open a handle to WLAN (Wireless Local Area Network)

The same as this :

Public Declare Function WlanOpenHandle Lib "wlanapi.dll" ( _
            ByVal dwClientVersion As Long, ByVal pdwReserved As LongPtr, _
            ByRef pdwNegotiaitedVersion As Long, ByRef phClientHandle As LongPtr) As Long   ' Declare a function to open a handle to WLAN (Wireless Local Area Network)

So, to sum up, the best thing is to make the variables, the api declarartions , the UDTs and the Enums ALL explicitly Private... This will make the code cleaner, easier to maintain and debug, and less prone to compile errors.

This is how I recommend writing the entire bas module declarations section in order to avoid unexpected compile errors:

Option Explicit
'
#If VBA7 Then                                                                               'Conditional compilation directive for VBA version 7 or higher
    Private Declare PtrSafe Function WlanOpenHandle Lib "wlanapi.dll" ( _
            ByVal dwClientVersion As Long, ByVal pdwReserved As LongPtr, _
            ByRef pdwNegotiaitedVersion As Long, ByRef phClientHandle As LongPtr) As Long   ' Declare a function to open a handle to WLAN (Wireless Local Area Network)
    Private Declare PtrSafe Function WlanCloseHandle Lib "wlanapi.dll" ( _
            ByVal hClientHandle As LongPtr, ByVal pdwReserved As LongPtr) As Long           ' Declare a function to close a handle to WLAN
    Private Declare PtrSafe Function WlanEnumInterfaces Lib "wlanapi.dll" ( _
            ByVal hClientHandle As LongPtr, ByVal pReserved As LongPtr, _
            ppInterfaceList As LongPtr) As Long                                             ' Declare a function to enumerate WLAN interfaces
    Private Declare PtrSafe Function WlanScan Lib "wlanapi.dll" ( _
            ByVal hClientHandle As LongPtr, pInterfaceGuid As GUID, _
            pDot11Ssid As LongPtr, pIeData As LongPtr, reserved As LongPtr) As Long         ' Declare a function to initiate a WLAN scan
    Private Declare PtrSafe Function WlanGetNetworkBssList Lib "wlanapi.dll" ( _
            ByVal hClientHandle As LongPtr, pInterfaceGui As GUID, _
            ByVal pDot11Ssid As Long, ByVal dot11BssType As LongPtr, _
            ByVal bSecurityEnabled As LongPtr, ByVal pReserved As LongPtr, _
            ppWlanBssList As LongPtr) As Long                                               ' Declare a function to retrieve the list of available WLAN network BSS (Basic Service Set)
    Private Declare PtrSafe Sub WlanFreeMemory Lib "wlanapi.dll" (ByVal pMemory As LongPtr)         ' Declare a sub to free memory allocated by WLAN functions
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" ( _
            Destination As Any, Source As Any, ByVal Length As LongPtr)                     ' Declare a sub to copy memory from source to destination
    Private Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)             ' Declare a sub to pause execution for a specified time in milli-seconds
    Private Declare PtrSafe Function SetTimer Lib "user32" ( _
            ByVal hwnd As LongPtr, ByVal nIDEvent As Long, ByVal uElapse As Long, _
            ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" ( _
            ByVal hwnd As LongPtr, ByVal nIDEvent As Long) As Long
#Else
    Private Enum LongPtr
        [_]
    End Enum
    Private Declare Function WlanOpenHandle Lib "wlanapi.dll" ( _
            ByVal dwClientVersion As Long, ByVal pdwReserved As LongPtr, _
            ByRef pdwNegotiaitedVersion As Long, ByRef phClientHandle As LongPtr) As Long   ' Declare a function to open a handle to WLAN (Wireless Local Area Network)
    Private Declare Function WlanCloseHandle Lib "wlanapi.dll" ( _
            ByVal hClientHandle As LongPtr, ByVal pdwReserved As LongPtr) As Long           ' Declare a function to close a handle to WLAN
    Private Declare Function WlanEnumInterfaces Lib "wlanapi.dll" ( _
            ByVal hClientHandle As LongPtr, ByVal pReserved As LongPtr, _
            ppInterfaceList As LongPtr) As Long                                             ' Declare a function to enumerate WLAN interfaces
    Private Declare Function WlanScan Lib "wlanapi.dll" ( _
            ByVal hClientHandle As LongPtr, pInterfaceGuid As GUID, _
            pDot11Ssid As LongPtr, pIeData As LongPtr, reserved As LongPtr) As Long         ' Declare a function to initiate a WLAN scan
    Private Declare Function WlanGetNetworkBssList Lib "wlanapi.dll" ( _
            ByVal hClientHandle As LongPtr, pInterfaceGui As GUID, _
            ByVal pDot11Ssid As Long, ByVal dot11BssType As LongPtr, _
            ByVal bSecurityEnabled As LongPtr, ByVal pReserved As LongPtr, _
            ppWlanBssList As LongPtr) As Long                                               ' Declare a function to retrieve the list of available WLAN network BSS (Basic Service Set)
    Private Declare Sub WlanFreeMemory Lib "wlanapi.dll" (ByVal pMemory As LongPtr)         ' Declare a sub to free memory allocated by WLAN functions
    Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" ( _
            Destination As Any, Source As Any, ByVal Length As LongPtr)                     ' Declare a sub to copy memory from source to destination
    Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)             ' Declare a sub to pause execution for a specified time in milli-seconds
    Private Declare Function SetTimer Lib "user32" ( _
            ByVal hwnd As LongPtr, ByVal nIDEvent As Long, ByVal uElapse As Long, _
            ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare Function KillTimer Lib "user32" ( _
            ByVal hwnd As LongPtr, ByVal nIDEvent As Long) As Long
#End If
'
' Define a custom data type for storing information about a Wi-Fi SSID (Service Set Identifier)
Private Type DOT11_SSID
    uSSIDLength                 As Long                                                     ' Length of the SSID
    ucSSID(31)                  As Byte                                                     ' Array of bytes representing the SSID (up to 32 characters)
End Type
'
' Define an enumeration for different types of Wi-Fi Basic Service Set (BSS)
Private Enum DOT11_BSS_TYPE
    dot11_BSS_type_infrastructure = 1                                                       ' Infrastructure BSS (Connected to an access point)
       dot11_BSS_type_independent = 2                                                       ' Independent BSS (Ad-hoc network)
               DOT11_BSS_TYPE_ANY = 3                                                       ' Any BSS type
End Enum
'
' Define an enumeration for different types of Wi-Fi PHY (Physical) modes
Private Enum DOT11_PHY_TYPE
       dot11_phy_type_unknown = 0                                                           ' Unknown PHY type
           dot11_phy_type_any = 0                                                           ' Any PHY type
          dot11_phy_type_fhss = 1                                                           ' Frequency Hopping Spread Spectrum (FHSS)
          dot11_phy_type_dsss = 2                                                           ' Direct Sequence Spread Spectrum (DSSS)
    dot11_phy_type_irbaseband = 3                                                           ' Infrared Baseband
          dot11_phy_type_ofdm = 4                                                           ' Orthogonal Frequency Division Multiplexing (OFDM)
        dot11_phy_type_hrdsss = 5                                                           ' High-Rate DSSS (HRDSSS)
           dot11_phy_type_erp = 6                                                           ' Extended Rate PHY (ERP)
            dot11_phy_type_ht = 7                                                           ' High Throughput PHY (HT)
           dot11_phy_type_vht = 8                                                           ' Very High Throughput PHY (VHT)
     dot11_phy_type_IHV_start = &H80000000                                                  ' Start of vendor-specific PHY types
       dot11_phy_type_IHV_end = &HFFFFFFFF                                                  ' End of vendor-specific PHY types
End Enum
'
' Define a custom data type for storing FILETIME, a 64-bit value representing date and time
Private Type FILETIME
    dwLowDateTime               As Long                                                     ' Low-order bits of the file time
    dwHighDateTime              As Long                                                     ' High-order bits of the file time
End Type
'
' Define a custom data type for storing information about a Wi-Fi rate set
Private Type WLAN_RATE_SET
    uRateSetLength              As Long                                                     ' Length of the rate set
    usRateSet(125)              As Integer                                                  ' Array of integers representing supported rates
End Type
'
' Define a custom data type for storing a GUID (Globally Unique Identifier)
Private Type GUID
    data1                       As Long                                                     ' First 4 bytes of the GUID
    data2                       As Integer                                                  ' Next 2 bytes of the GUID
    data3                       As Integer                                                  ' Next 2 bytes of the GUID
    data4(7)                    As Byte                                                     ' Last 8 bytes of the GUID
End Type
'
' Define a custom data type for storing information about a Wi-Fi interface
Private Type WLAN_INTERFACE_INFO
    ifGuid                      As GUID                                                     ' GUID of the Wi-Fi interface
    InterfaceDescription(511)   As Byte                                                     ' Description of the interface (up to 512 characters)
    IsState                     As Long                                                     ' State of the interface
End Type
'
' Define a custom data type for storing a list of Wi-Fi interface information
Private Type WLAN_INTERFACE_INFO_LIST
    dwNumberofItems             As Long                                                     ' Number of items in the list
    dwIndex                     As Long                                                     ' Index of the item
    InterfaceInfo               As WLAN_INTERFACE_INFO                                      ' Wi-Fi interface information
End Type
'
' Define a custom data type for storing a list of Wi-Fi Basic Service Set (BSS) information
Private Type WLAN_BSS_LIST
    dwTotalSize                 As Long                                                     ' Total size of the list
    dwNumberofItems             As Long                                                     ' Number of items in the list
    wlanBssEntries              As Long                                                     ' Pointer to BSS entries
End Type
'
' Define a custom data type for storing information about a Wi-Fi Basic Service Set (BSS) entry
Private Type WLAN_BSS_ENTRY
    dot11Ssid                   As DOT11_SSID                                               ' SSID of the BSS
    uPhyId                      As Long                                                     ' PHY ID of the BSS
    dot11Bssid(7)               As Byte                                                     ' BSSID (MAC address) of the BSS
    dot11BssType                As DOT11_BSS_TYPE                                           ' Type of BSS (Infrastructure, Independent, etc.)
    dot11BssPhyType             As DOT11_PHY_TYPE                                           ' PHY type of the BSS
    lRssi                       As Long                                                     ' Received Signal Strength Indicator (RSSI)
    uLinkQuality                As Long                                                     ' Link quality
    bInRegDomain                As Long                                                     ' Indicates if the BSS is in the regulatory domain
    usBeaconPeriod              As Long                                                     ' Beacon period
    ullTimestamp                As FILETIME                                                 ' Timestamp of the BSS
    ullHostTimestamp            As FILETIME                                                 ' Host timestamp
    usCapabilityInformation     As Long                                                     ' Capability information
    ulChCenterFrequency         As Long                                                     ' Center frequency of the channel
    wlanRateSet                 As WLAN_RATE_SET                                            ' Rate set supported by the BSS
    ulIeOffset                  As Long                                                     ' Information Element offset
    ulIeSize                    As Long                                                     ' Information Element size
End Type
'
Private lVersion                As Long
Private lHandle                  As LongPtr
Private udtBSSList              As WLAN_BSS_LIST
Private udtList                 As WLAN_INTERFACE_INFO_LIST

Private bRunning As Boolean
Private bShowWaitGIF As Boolean

Private Const DISPLAY_WORKSHEET As String = "Sheet1" ' <=== Change this Module level CONST to suit.

 

[johnnyL]

Changes made & successful! Thank you @Jaafar Tribak!!!

 

[Jaafar Tribak]

Changes made & successful! Thank you @Jaafar Tribak!!!

You are welcome.

And thank you for reporting those subtle errors, hence giving me the oppotunity to anticipate potential problems when writing code in VBA7 that is meant to be compatible with both, VBA6 and VBA7.

I don't have excel 2007 for testing my codes so it would have been hard for me to notice and pick up the errors you reported.

Regards.

 

[johnnyL]

It takes a village to raise a child.

 

[Prome3]

Ok I see.

Instead of changing the Enum to Private, it is preferable to leave it as is and change the declaration of the the lHandle variable from Public to Private

So you will have :

    Private Enum LongPtr
        [_]
    End Enum

And then change this :

Public lHandle                  As LongPtr

To this :

Private lHandle                  As LongPtr

Hi Jaafar,
I've seen the following code segment in many places in this forum and I've always wondered what is its purpose and how does it work. Can you give an explanation on it please?

    Private Enum LongPtr
        [_]
    End Enum

Best Regards.