在日常的网络管理中,经常遇到IP所属地(位置)的判断,比如出现了某些IP地址出现了问题,需要通知具体单位去找到该IP然后处理。
这样需要规划好具体的网络,划好VLAN,然后根据IP地址(或者网关地址)和子网掩码的运算找到这个IP所属的单位。
1、根据IP地址(子网掩码)和网关地址获取网络地址
Sub GeTNetInfo()
Dim SrcTable As String
Dim iFor As Integer
Dim K As Integer
Dim AllRecord As Integer
Dim SubnetMask As String
Dim SubnetMaskArr() As String
Dim GatewayAddress As String
Dim GatewayAddressArr() As String
Dim NetInfo(3) As String
Dim NetAddress As String
SrcTable = "sheet1"
AllRecord = 43
For iFor = 2 To AllRecord
Sheets(SrcTable).Activate
Range("C" + Trim(Str(iFor))).Select
GatewayAddress = Selection.FormulaR1C1
GatewayAddressArr = Split(GatewayAddress, ".")
Range("D" + Trim(Str(iFor))).Select
GatewayAddress = UCase(Selection.FormulaR1C1)
SubnetMask = Selection.FormulaR1C1
SubnetMaskArr = Split(SubnetMask, ".")
If SubnetMask <> "" Then
For K = 0 To 3
NetInfo(K) = Str(CInt(GatewayAddressArr(K)) And CInt(SubnetMaskArr(K)))
Next K
NetAddress = Join(NetInfo, ".")
'NetAddress = Str(NetInfo(0)) & "." & Str(NetInfo(1)) & "." & Str(NetInfo(2)) & "." & Str(NetInfo(3))
Range("B" + Trim(Str(iFor))).Select
Selection.FormulaR1C1 = NetAddress
End If
Next iFor
End Sub
实际的运用中,网络信息(网络地址和所属单位)可以放在另外一个Sheet中,然后通过对比就可以快速找到这个IP地址属于哪个单位了。
写一个函数就更方便了。
2、通过正则表达式获取子网掩码和网关信息
VRV中提取了网卡信息,是一个字符串,现在需要提取其中的子网掩码和网关信息,可以使用VBA的正则表达式来快速获取。
Public Function GetNetInfo(StrNetInfo As String) As Variant
Dim regex As Object
Dim matches As Object
'创建正则表达式对象
Set regex = CreateObject("VBScript.RegExp")
'设置正则表达式模式
regex.Pattern = "子网掩码:(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}),默认网关:(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})"
'执行匹配操作
Set matches = regex.Execute(StrNetInfo)
'输出匹配结果
If matches.Count > 0 Then
GetNetInfo = Array(matches(0).SubMatches(0), matches(0).SubMatches(1))
Else
GetNetInfo = Array(0, 0)
End If
End Function
测试代码:
Sub test()
Dim str As String
Dim tt As Variant
str = "网卡信息:MAC地址:F12F34A566D9,IP地址:13.92.120.123,子网掩码:255.255.255.224,默认网关:13.92.120.254,首选DNS:13.92.120..11,备用DNS:13.92.120..12"
tt = GetNetInfo(str)
MsgBox "子网掩码:" & tt(0) & "|""默认网关:" & tt(1)
End Sub