Office VBA Code Repository

---

Table of Contents

• Introduction
• Getting Started
• How to use
• Excel VBA ISO-8601 Timestamp Utilities
• IP Address Splitter Macro
• IP Address Processing Macro

---

Introduction

Background: Version control for Office documents with embedded VBA is impractical due to their binary nature. This repository contains standalone VBA code modules for Microsoft Office desktop applications.

---

Getting Started

• VBA API Overview
• VBA Library Reference

---

How to use

To use this for saving your VBA code:

1. In the VBA editor in any Office app, chose file->export and save the .bas file somewhere
2. Upload/commit the .bas file

Presumably you could also commit the xlsb/dotm/... whatever file, but committing source code in binary files seems like a Very Bad Thing Indeed™

For Excel, you'd typically put VBA utility code in your PERSONAL.XLSB file, rather than putting it in individual workbooks

---

Application-Specific Personal Storage Locations

(I haven't verified that these are correct - Lynne)

Application	Storage File	Default Location	Purpose
Excel	PERSONAL.XLSB	XLSTART folder	Autoload workbook for personal macros
Word	Normal.dotm	%appdata%\Microsoft\Templates	Default template, AutoExec macros
Outlook	VbaProject.otm	%appdata%\Microsoft\Outlook	VBA project container
PowerPoint	Presentation.potx	%appdata%\Microsoft\Templates	Default presentation template
Visio	Startup.vss	%appdata%\Microsoft\Visio\Drawing	Startup stencil file
Access	Custom .accdb	User-defined	Library database reference
Publisher	Custom .pub	User-defined	Template reference

To locate storage files:

1. Launch Office application
2. Press Alt+F11 for VBA editor
3. Tools -> Options -> File Locations

---

Excel VBA ISO-8601 Timestamp Utilities

ConvertSelectedTimestamps macro

Location: personal.xlsb!modUtility

Converts ISO-8601/RFC-3339 timestamps in selected cells to Excel datetime format.

Usage

1. Select cells containing timestamps
2. Execute ConvertSelectedTimestamps
3. Timestamps convert in-place

Features

• UTC/Zulu to local time conversion
• Preserves millisecond precision
• Handles both 'T' and space separators
• Smart handling of existing datetime values
• Consistent N/A value conversion

Example

Input: 2024-12-04T16:39:04.244Z
Output: 12/4/2024 10:39:04 AM (local time)

---

ImportAndConvertTimestamps macro

**Location:** personal.xlsb!modUtility

Batch converts ISO-8601 timestamps in CSV files.

Usage

1. Run ImportAndConvertTimestamps
2. Select target CSV file
3. Backup created automatically
4. Timestamps converted throughout file

Features

• Automatic backup creation
• UTC/Zulu to local time conversion
• Millisecond precision retention
• Flexible timestamp format support
• Standardized N/A handling

Backup Format

Original: data.csv
Backup: data.backup-20241204-153022.csv

---

Technical Specifications

Supported Timestamp Formats

• YYYY-MM-DDThh:mm:ss.sssZ
• YYYY-MM-DD hh:mm:ss.sssZ

NULL Value Handling

The following inputs convert to 1/1/1900:

• Empty cells
• "n/a"
• "N/A"
• "none"
• "null"

---

---

IPv4 Address Conversion Excel VBA Macros

Overview

VBA macros for converting IPv4 address in string format to multiple columans and between IPv4 addresses and their integer (Int32) representations.
Each macro creates a new column with the converted values while preserving the original data.

ConvertIPv4ToColumns macro

Overview

VBA macro to split IP addresses into their constituent octets across multiple columns. The macro inserts new columns automatically, preserves original data, and formats the output for readability.

Usage Instructions

Basic Operation

1. Select a single column containing IP addresses
2. Execute the ConvertIPv4ToColumns macro
3. Four new columns will be created to the right
4. Original data is preserved

Input Format

• Valid IPv4 addresses (xxx.xxx.xxx.xxx)
• Each octet must be 0-255
• One IP address per cell
• First row can be header (preserved)

Output Format

Four new columns created:

• IP A: First octet
• IP B: Second octet
• IP C: Third octet
• IP D: Fourth octet

All new columns are:

• Center-aligned (horizontal and vertical)
• Auto-fitted to content
• Preserve original data types

Example

Input

IP_Address 192.168.1.1 10.0.0.1 172.16.254.1

Output

IP_Address	IP A	IP B	IP C	IP D
192.168.1.1	192	168	1	1
10.0.0.1	10	0	0	1
172.16.254.1	172	16	254	1

---

ConvertIPv4ToInt32 macro

Converts IPv4 addresses in standard dotted decimal format (a.b.c.d) to their Int32 numeric representation.

Usage

1. Select a single column containing IPv4 addresses
2. Run the ConvertIPv4ToInt32 macro
3. Results appear in a new column to the right

Examples

• Input: "192.168.1.1" → Output: 3232235777
• Input: "10.0.0.1" → Output: 167772161

Limitations

• Requires single column selection
• Assumes first row is header
• Basic IP format validation only
• Does not validate octet ranges

---

ConvertInt32ToIPv4 macro

Converts Int32 numbers back to standard IPv4 address format (a.b.c.d).

Usage

1. Select a single column containing Int32 values
2. Run the ConvertInt32ToIPv4 macro
3. Results appear in a new column to the right

Examples

• Input: 3232235777 → Output: "192.168.1.1"
• Input: 167772161 → Output: "10.0.0.1"

Limitations

• Requires single column selection
• Assumes first row is header
• Handles positive Int32 values only
• No validation of IP range validity

Formula Used for IPv4 to Int32

Int32 = (first_octet * 256³) + (second_octet * 256²) + (third_octet * 256) + (fourth_octet)

Formula Used for Int32 to IPv4

first_octet = Int(number / 16777216) Mod 256  
second_octet = Int(number / 65536) Mod 256  
third_octet = Int(number / 256) Mod 256  
fourth_octet = number Mod 256

Error Handling

ConvertIPv4ToInt32()

• Invalid IP format: Outputs "Invalid IP"
• Empty cells: Skipped
• Non-single column selection: Shows warning message

ConvertInt32ToIPv4()

• Invalid numbers: Outputs "Invalid number"
• Empty cells: Skipped
• Non-single column selection: Shows warning message

Notes

• Both macros add appropriate headers to new columns
• Original data is preserved
• Columns are auto-fitted after conversion
• Header row is skipped during conversion
• May overwrite data in adjacent column if present

Your content here