UIADP: Data Manipulation: Variables, Arguments, Strings, Collections and DataTables

Core concepts

Everything a candidate manipulates is a .NET type: Int32, Double, Boolean, String, DateTime, Object plus generic collections. Prefer List(Of String) and Dictionary(Of String, Object) over legacy ArrayList; remember lists are dynamic while arrays are fixed-size, and that indices are zero-based. Strings are immutable: methods like Substring, Split, Replace, Trim, ToUpper return new values. Substring(startIndex, length) takes a length, not an end index, and Split always returns an array.

DataTables are the workhorse for tabular data: Read Range produces one, For Each Row iterates it, Add Data Row appends using an array or Object array, and dt.Select('[Status]= ''Open''') filters with SQL-like syntax. Column access is case-insensitive in practice but stay consistent. dt.Rows.Count and dt.Columns.Count are the safe way to check emptiness rather than comparing to Nothing.

Traps

• Using Substring with an end index and throwing ArgumentOutOfRange.
• Assuming Read Range on an empty sheet gives an empty table rather than Nothing.
• Dictionary lookup on a missing key throwing KeyNotFoundException instead of returning Nothing.
• Date parsing that depends on the machine's regional settings.

Rule of thumb

Never let culture decide your parsing. Use DateTime.ParseExact(strDate, 'dd/MM/yyyy', CultureInfo.InvariantCulture) and Double.TryParse for numeric input.