Microsoft Excel tips

Search with two values

The VLOOKUP and SUMIF functions can be used to search using a single value, but there are times when you may wish to search using two values. A generalized example is the following array formula (use Ctrl+Shift+Enter to save this array formula):

• E20 is one value to search for, within A20 through A28
• F20 is the other value to search for, within B20 through B28
• When both match, return the value within C20 through C28

=INDEX($C$20:$C$28,MATCH(1,($A$20:$A$28=$E$20)*($B$20:$B$28=$F$20),0))

The attached spreadsheet shows two problems, and both are solved with this model.

Suppress zero

If the result appears as “0″ and you would rather it was blank, you can use the following custom cell format:

General;-General;

IPv4 manipulation

Sorting by the familiar dotted decimal IPv4 format may not produce the result you want. For example, 192.168.1.12 would appear between 192.168.1.119 and 192.168.1.120.

192.168.1.11

192.168.1.119

192.168.1.12

192.168.1.120

192.168.1.121

192.168.1.13

In these cases, expressing the IPv4 number in its familiar dotted decimal notation is producing a result you did not expect. You can express the IPv4 number as a decimal number, which will sort in the manner you expect.  Assuming the IPv4 dotted decimal number is in cell A2, the following formula will produce its decimal equivalent:

=((VALUE(LEFT(A2, FIND(".", A2)-1)))*256^3)+((VALUE(MID(A2, FIND(".", A2)+1, FIND(".", A2, FIND(".", A2)+1)-FIND(".", A2)-1)))*256^2)+((VALUE(MID(A2, FIND(".", A2, FIND(".", A2)+1)+1, FIND(".", A2, FIND(".", A2, FIND(".", A2)+1)+1)-FIND(".", A2, FIND(".", A2)+1)-1)))*256)+(VALUE(RIGHT(A2, LEN(A2)-FIND(".", A2, FIND(".", A2, FIND(".", A2)+1)+1))))

Conversely, a large decimal number can be expressed in the familiar IPv4 dotted decimal notation. Assuming the decimal number is in cell A2, the following formula will produce its dotted decimal equivalent:

=IF(A2<>"", CONCATENATE(INT(A2/256^3), ".", INT(MOD(A2, (256^3))/(256^2)), ".", INT(MOD(MOD(A2, 256^3), 256^2)/256), ".", MOD(MOD(MOD(A2, 256^3), 256^2), 256)), "")

DNS lookup, PING

You can add functions named Address2Name(cell name or IP string) for DNS lookup and PingAddress(cell name or IP string) to see if the device is alive.

To perform a DNS lookup and a ping test will require macros.

To enable macros, see Create or delete a macro Record a macro steps 1 and 2:

1. If the Developer  tab is not available, do the following to display it:
   1. Click the Microsoft Office Button , and then click Excel Options.
   2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.
2. To set the security level temporarily to enable all macros, do the following:
   1. On the Developer tab, in the Code group, click Macro Security.
      
   2. Under Macro Settings, click Enable all macros (not recommended, potentially dangerous code can run), and then click OK.

   Note   To help prevent potentially dangerous code from running, we recommend that you return to any one of the settings that disable all macros after you finish working with macros. For more information about how to change the settings, see Change macro security settings in Excel.

If you need assistance with creating a module, see Create Custom Functions in Excel 2007.

Create a module named Network with the following text:

Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub GetIPs()
Dim address As String
Dim row As Integer
row = 2
address = Cells(row, 1)
Do Until Trim(address) = ""
Cells(row, 2) = PingAddress(address)
Cells(row, 3) = Now
row = row + 1
address = Cells(row, 1)
Sleep 2500
Loop
End Sub

Function PingAddress(strDomain As String) As String
Dim objShell As Object
Dim strText As String
Dim objExecObject As Object
Set objShell = CreateObject("WScript.Shell")
Dim objIP As clsIPResolve
Set objIP = New clsIPResolve
PingAddress = objIP.NameToAddress(strDomain)
If PingAddress = "" Then
  PingAddress = "Not found"
Else
  Set objExecObject = objShell.Exec("%comspec% /c ping -n 3 -w 1000 " & PingAddress)
  Do While Not objExecObject.StdOut.AtEndOfStream
  strText = objExecObject.StdOut.ReadAll()
  If InStr(strText, "unreachable") > 0 Then
    PingAddress = "Host unreachable"
  ElseIf InStr(strText, "Reply") > 0 Then
    Else
    PingAddress = "Not found"
  End If
  Loop
End If
Set objIP = Nothing
Set objExecObject = Nothing
Set objShell = Nothing
End Function

Function Address2Name(strDomain As String) As String
Dim objShell As Object
Dim strText As String
Dim objExecObject As Object
Set objShell = CreateObject("WScript.Shell")
Dim objIP As clsIPResolve
Set objIP = New clsIPResolve
Address2Name = objIP.AddressToName(strDomain)
If Address2Name = "" Then Address2Name = "No DNS available"
Set objIP = Nothing
Set objExecObject = Nothing
Set objShell = Nothing
End Function

Create a class module with the following text:

Option Explicit
Private mbInitialized As Boolean
Const WSADescription_Len = 256
Const WSASYS_Status_Len = 128
Const AF_INET = 4&

Private Type HOSTENT
hName As Long
hAliases As Long
hAddrType As Integer
hLength As Integer
hAddrList As Long
End Type

Private Type WSADATA
wversion As Integer
wHighVersion As Integer
szDescription(0 To WSADescription_Len) As Byte
szSystemStatus(0 To WSASYS_Status_Len) As Byte
iMaxSockets As Integer
iMaxUdpDg As Integer
lpszVendorInfo As Long
End Type

Private Declare Function WSAStartup _
Lib "wsock32" _
(ByVal VersionReq As Long, _
WSADataReturn As WSADATA) _
As Long

Private Declare Function WSACleanup _
Lib "wsock32" _
() _
As Long

Private Declare Function WSAGetLastError _
Lib "wsock32" _
() _
As Long

Private Declare Function gethostbyaddr _
Lib "wsock32" _
(addr As Long, _
addrLen As Long, _
addrType As Long) _
As Long

Private Declare Function gethostbyname _
Lib "wsock32" _
(ByVal hostname As String) _
As Long

Private Declare Sub RtlMoveMemory _
Lib "kernel32" _
(hpvDest As Any, _
ByVal hpvSource As Long, _
ByVal cbCopy As Long)

'checks if string is valid IP address
Private Function CheckIP(IPToCheck As String) As Boolean
Dim TempValues
Dim iLoop As Long
Dim TempByte As Byte
On Error GoTo CheckIPError
TempValues = Split(IPToCheck, ".")
If UBound(TempValues) < 3 Then
  Exit Function
End If
For iLoop = LBound(TempValues) To UBound(TempValues)
  TempByte = TempValues(iLoop)
Next iLoop
CheckIP = True
CheckIPError:
End Function

'converts IP address from string to sin_addr
Private Function MakeIP(strIP As String) As Long
Dim vTemp
Dim lngTemp As Long
Dim iLoop As Long
On Error GoTo MakeIPError
vTemp = Split(strIP, ".")
For iLoop = 0 To (UBound(vTemp) - 1)
  lngTemp = lngTemp + (vTemp(iLoop) * (256 ^ iLoop))
Next iLoop
If vTemp(UBound(vTemp)) < 128 Then
  lngTemp = lngTemp + (vTemp(UBound(vTemp)) * (256 ^ 3))
Else
  lngTemp = lngTemp + ((vTemp(UBound(vTemp)) - 256) * (256 ^ 3))
End If
MakeIP = lngTemp
MakeIPError:
End Function

'resolves IP address to host name
Private Function AddrToName(strAddr As String) As String
Dim heEntry As HOSTENT
Dim strHost As String * 255
Dim strTemp As String
Dim lngRet As Long
Dim lngIP As Long
On Error GoTo AddrToNameError
If CheckIP(strAddr) Then
  lngIP = MakeIP(strAddr)
  lngRet = gethostbyaddr(lngIP, 4, AF_INET)
  If lngRet = 0 Then
    Exit Function
  End If
  RtlMoveMemory heEntry, lngRet, Len(heEntry)
  RtlMoveMemory ByVal strHost, heEntry.hName, 255
  strTemp = TrimNull(strHost)
  AddrToName = strTemp
End If
AddrToNameError:
End Function

'resolves host name to IP address
Private Function NameToAddr(ByVal strHost As String)
Dim ip_list() As Byte
Dim heEntry As HOSTENT
Dim strIPAddr As String
Dim lp_HostEnt As Long
Dim lp_HostIP As Long
Dim iLoop As Integer
On Error GoTo NameToAddrError
lp_HostEnt = gethostbyname(strHost)
If lp_HostEnt = 0 Then
  Exit Function
End If
RtlMoveMemory heEntry, lp_HostEnt, LenB(heEntry)
RtlMoveMemory lp_HostIP, heEntry.hAddrList, 4
ReDim ip_list(1 To heEntry.hLength)
RtlMoveMemory ip_list(1), lp_HostIP, heEntry.hLength
For iLoop = 1 To heEntry.hLength
  strIPAddr = strIPAddr & ip_list(iLoop) & "."
Next
strIPAddr = Mid(strIPAddr, 1, Len(strIPAddr) - 1)
NameToAddr = strIPAddr
NameToAddrError:
End Function

Public Function AddressToName(strIP As String) As String
If mbInitialized Then
  AddressToName = AddrToName(strIP)
End If
End Function

Public Function NameToAddress(strName As String) As String
If mbInitialized Then
  NameToAddress = NameToAddr(strName)
End If
End Function

Private Function TrimNull(sTrim As String) As String
Dim iFind As Long
iFind = InStr(1, sTrim, Chr(0))
If iFind > 0 Then
  TrimNull = Left(sTrim, iFind - 1)
Else
  TrimNull = sTrim
End If
End Function

Private Sub Class_Initialize()
Dim wsa As WSADATA
mbInitialized = (WSAStartup(257, wsa) = 0)
End Sub

Private Sub Class_Terminate()
If mbInitialized Then
  WSACleanup
End If
End Sub

You now have a GetIPs function

Advertisement