modIPTools.bas

Attribute VB_Name = "modIPTools"
Option Explicit

'------------------------------------------------------------------------------
' Module    : modIPTools
' DateTime  : 2024-03-14
' Author    : ClaudeAI & Lynne Whitehorn
' Purpose   : Splits IP addresses into octets across multiple columns
'------------------------------------------------------------------------------
' Usage     : 1. Select a column containing IP addresses
'             2. Run the ConvertIPv4ToColumns macro
'             3. Four new columns will be created with split octets
'------------------------------------------------------------------------------
' Input     : Selected column must contain valid IP addresses in format:
'             xxx.xxx.xxx.xxx (where xxx is 0-255)
' Output    : Four new columns containing:
'             - IP A: First octet
'             - IP B: Second octet
'             - IP C: Third octet
'             - IP D: Fourth octet
'------------------------------------------------------------------------------
' Example   : Input: 192.168.1.1
'             Output:
'             IP A | IP B | IP C | IP D
'             192  | 168  |  1   |  1
'------------------------------------------------------------------------------
' Requires  : Microsoft Excel
' Version   : Excel 2016 or later
'------------------------------------------------------------------------------
' Changelog : 2024-03-14 - Initial version
'             - Basic IP splitting functionality
'             - Center alignment
'             - Error handling
'------------------------------------------------------------------------------

Public Sub ConvertIPv4ToColumns()
Attribute ConvertIPv4ToColumns.VB_Description = "Splits IP addresses into octets across multiple columns"
Attribute ConvertIPv4ToColumns.VB_ProcData.VB_Invoke_Func = " \n21"
    'Error handling to catch and display any runtime errors
    On Error GoTo ErrorHandler
    
    'Declare variables
    Dim selectedCol As Range    'The user-selected column containing IP addresses
    Dim firstCell As Range      'First cell of data (excluding header)
    Dim lastCell As Range       'Last cell containing data in column
    Dim ws As Worksheet         'Active worksheet reference
    Dim startCol As Integer     'Column number where selection starts
    Dim dataRange As Range      'Range containing all new data including headers
    
    'Initialize worksheet reference
    Set ws = ActiveSheet
    
    'Get active selection
    Set selectedCol = Selection
    
    'Validate selection - ensure only one column is selected
    If selectedCol.Columns.Count > 1 Then
        MsgBox "Please select a single column.", vbExclamation
        Exit Sub
    End If
    
    'Store the column number where processing starts
    startCol = selectedCol.Column
    
    'Insert 4 new columns to the right of selection
    'These will hold the split IP address components
    ws.Columns(startCol + 1).Resize(, 4).Insert Shift:=xlToRight
    
    'Copy original IP addresses to first new column
    'This preserves original data and provides source for splitting
    selectedCol.Copy ws.Cells(1, startCol + 1)
    
    'Add descriptive headers for each octet column
    With ws
        .Cells(1, startCol + 1).Value = "IP A"  'First octet
        .Cells(1, startCol + 2).Value = "IP B"  'Second octet
        .Cells(1, startCol + 3).Value = "IP C"  'Third octet
        .Cells(1, startCol + 4).Value = "IP D"  'Fourth octet
    End With
    
    'Determine range for processing
    Set firstCell = ws.Cells(2, startCol + 1)           'Start below header
    Set lastCell = ws.Cells(ws.Rows.Count, startCol + 1).End(xlUp) 'Find last row
    
    'Only process if there is data to split
    If firstCell.Row <= lastCell.Row Then
        'Split IP addresses using period as delimiter
        With ws.Range(firstCell, lastCell)
            .TextToColumns _
                Destination:=ws.Cells(firstCell.Row, startCol + 1), _
                DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, _
                ConsecutiveDelimiter:=False, _
                Tab:=False, _
                Semicolon:=False, _
                Comma:=False, _
                Space:=False, _
                Other:=True, _
                OtherChar:="."  'Period delimiter for IP address splitting
        End With
    End If
    
    'Set alignment for all new columns including headers
    Set dataRange = ws.Range( _
        ws.Cells(1, startCol + 1), _
        ws.Cells(lastCell.Row, startCol + 4) _
    )
    With dataRange
        .HorizontalAlignment = xlCenter  'Center text horizontally
        .VerticalAlignment = xlCenter    'Center text vertically
    End With
    
    'Auto-fit column widths to data
    ws.Columns(startCol + 1).Resize(, 4).AutoFit
    
    Exit Sub

'Error handler
ErrorHandler:
    'Display error message with description
    MsgBox "An error occurred: " & Err.Description, vbCritical
End Sub




'------------------------------------------------------------------------------
' ConvertInt32ToIPv4
' Converts Int32 values back to IPv4 addresses in format "a.b.c.d"
' Selected range must be single column
' Creates new column to the right with results
' Example: 3232235777 -> "192.168.1.1"
'------------------------------------------------------------------------------



'------------------------------------------------------------------------------
' ConvertIPv4ToInt32
' Converts IPv4 addresses in format "a.b.c.d" to Int32 values
' Selected range must be single column
' Creates new column to the right with results
' Example: "192.168.1.1" -> 3232235777
'------------------------------------------------------------------------------


Public Sub ConvertIPv4ToInt32()
    On Error GoTo ErrorHandler
    
    Dim ws As Worksheet
    Dim selectedRange As Range
    Dim cell As Range
    Dim newCol As Range
    
    Set ws = ActiveSheet
    Set selectedRange = Selection
    
    'Insert new column to the right
    selectedRange.Offset(0, 1).Insert Shift:=xlToRight
    Set newCol = selectedRange.Offset(0, 1)
    
    'Add header
    newCol.Cells(1, 1).Value = "Integer Value"
    
    'Process each cell
    For Each cell In selectedRange
        If cell.Row > 1 Then  'Skip header
            If Len(Trim(cell.Value)) > 0 Then
                newCol.Cells(cell.Row - selectedRange.Row + 1, 1).Value = _
                    IPToInt32(CStr(cell.Value))
            End If
        End If
    Next cell
    
    'Format the new column as Number
    newCol.NumberFormat = "0"
    ws.Columns.AutoFit
    Exit Sub
    
ErrorHandler:
    MsgBox "Error in row " & cell.Row & vbCrLf & _
           "IP: " & cell.Value & vbCrLf & _
           "Error: " & Err.Description, vbExclamation
End Sub

Private Function IPToInt32(ByVal ipAddress As String) As Double
    Dim octets() As String
    Dim result As Double
    
    'Clean the input string
    ipAddress = Trim(ipAddress)
    
    'Split IP address into octets
    octets = Split(ipAddress, ".")
    
    'Validate we have exactly 4 octets
    If UBound(octets) <> 3 Then
        Err.Raise vbObjectError + 1, "IPToInt32", "Invalid IP address format"
    End If
    
    'Convert octets to 32-bit integer value using doubles
    result = 0#
    result = CDbl(Val(octets(0))) * 16777216#    'First octet << 24
    result = result + (CDbl(Val(octets(1))) * 65536#)    'Second octet << 16
    result = result + (CDbl(Val(octets(2))) * 256#)      'Third octet << 8
    result = result + CDbl(Val(octets(3)))              'Fourth octet
    
    'Validate result range
    If result < 0# Or result > 4294967295# Then
        Err.Raise vbObjectError + 2, "IPToInt32", "IP address out of range"
    End If
    
    IPToInt32 = result
End Function

Public Sub ConvertInt32ToIPv4()
    On Error GoTo ErrorHandler
    
    Dim ws As Worksheet
    Dim selectedRange As Range
    Dim cell As Range
    Dim newCol As Range
    Dim cellValue As Double
    
    Set ws = ActiveSheet
    Set selectedRange = Selection
    
    'Insert new column to the right
    selectedRange.Offset(0, 1).Insert Shift:=xlToRight
    Set newCol = selectedRange.Offset(0, 1)
    
    'Add header
    newCol.Cells(1, 1).Value = "IPv4 Address"
    
    'Process each cell
    For Each cell In selectedRange
        If cell.Row > 1 Then  'Skip header
            If IsNumeric(cell.Value) Then
                cellValue = CDbl(cell.Value)
                If cellValue >= 0# And cellValue <= 4294967295# Then
                    newCol.Cells(cell.Row - selectedRange.Row + 1, 1).Value = _
                        Int32ToIP(cellValue)
                Else
                    newCol.Cells(cell.Row - selectedRange.Row + 1, 1).Value = _
                        "Value out of range"
                End If
            End If
        End If
    Next cell
    
    ws.Columns.AutoFit
    Exit Sub
    
ErrorHandler:
    MsgBox "Error in row " & cell.Row & vbCrLf & _
           "Value: " & cell.Value & vbCrLf & _
           "Error: " & Err.Description, vbExclamation
End Sub


Private Function Int32ToIP(ByVal ipDouble As Double) As String
    'Validate input range
    If ipDouble < 0# Or ipDouble > 4294967295# Then
        Err.Raise vbObjectError + 3, "Int32ToIP", "Value out of range"
    End If
    
    Dim octet1 As Long
    Dim octet2 As Long
    Dim octet3 As Long
    Dim octet4 As Long
    Dim remainder As Double
    
    'Extract each octet using division
    octet1 = Fix(ipDouble / 16777216#)
    remainder = ipDouble - (CDbl(octet1) * 16777216#)
    
    octet2 = Fix(remainder / 65536#)
    remainder = remainder - (CDbl(octet2) * 65536#)
    
    octet3 = Fix(remainder / 256#)
    octet4 = remainder - (CDbl(octet3) * 256#)
    
    'Combine octets into IP address string
    Int32ToIP = CStr(octet1) & "." & CStr(octet2) & "." & CStr(octet3) & "." & CStr(octet4)
End Function