Zipping Files with VBA and USB Drives
I used Ron de Bruin’s file zipping (and sending) code with Excel. It works great with a few tweaks. Unfortunately when someone is using a USB drive with their workbook on it it fails to work. So warning: Make sure the users are using it on the main computer when trouble shooting!
SUMIFS vs MATCH & INDEX
I have one Time Card template setup with SUMIFS (used for Excel 2007 and above templates) and one set up with MATCH/INDEX (used for my 2003 and below templates). I tested the calculation speeds for each in Excel 2013 (preview) on Windows 8.
Here’s the VBA test code:
Sub test()
Dim dTimer As Double
Dim iLoops As Integer, iTotalIterations As Integer
Dim wksStage As Worksheet
Set wksStage = ThisWorkbook.Worksheets("Staging Area")
iTotalIterations = 1
dTimer = Timer()
For iLoops = 0 To iTotalIterations
wksStage.Calculate
Next iLoops
Debug.Print Timer() - dTimer
End Sub
And here’s the results (seconds):
Coming TimeCard Software Updates
Just a quick update. I should be putting out the new Excel Time Card by the end of this week or early next week. This will be a big update. Including the following:
I have more plans for the future of the time card - I’ll let you know when I get close to finishing them.
Excel Extensions
Excel extensions extend the range object to use LinqTo2dArray (see also Codeplex). It also includes other extensions.
- RowsCount: Count total rows in all areas.
- Parse: Parse congruent range into an object by row-object arrays.
- This doesn’t necessarily need to be congruent, in the future I may make it so it skips to different areas in the range.
- ToArray: Copies the elements of the 2D object array row wise to a new array of the specified element type and length.
- To2dArray: Import Data From Excel as 2D zero-based Object Array
- ToExcel: Export Data to Excel
- Returns: Range where data was exported to.
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using NetOffice.ExcelApi;
using NetOffice.OfficeApi;
using NetOffice.ExcelApi.Enums;
using LinqTo2dArray;
namespace ExcelExtensions
{
public static class RangeExtenstions
{
/// <summary>
/// Count total rows in all areas
/// </summary>
/// <param name="rng">Working Range</param>
/// <returns>Total number of rows.</returns>
/// <remarks>Jon Nyman 20120924</remarks>
public static int RowsCount(this Range rng)
{
if ((rng != null))
{
int iRowCount = 0;
foreach (Range rArea in rng.Areas)
{
iRowCount += rArea.Rows.Count;
}
return iRowCount;
}
else
{
return 0;
}
}
/// <summary>
/// Parse congruent range into an object by row-object arrays.
/// </summary>
/// <typeparam name="TSource">Source type.</typeparam>
/// <param name="rng">Target range.</param>
/// <param name="conversion">Function to convert</param>
/// <returns></returns>
public static IEnumerable<TSource> Parse<TSource>(this Range rng
, Func<object[], TSource> conversion)
{
if (rng.Areas.Count>1)
throw new ArgumentException("Congruent ranges only allow
ed.");
object[,] array = rng.Get2dArrayValue();
IEnumerable<TSource> cls = array.Parse<TSource>(conversion);
return cls;
} //End Parse
/// <summary>
/// Copies the elements of the 2D object array row wise to a new
array of the specified element type and length.
/// </summary>
/// <typeparam name="T">New array type.</typeparam>
/// <param name="rng">Target range.</param>
/// <param name="conversion">Casting function of new type.</para
m>
/// <param name="rowStart">First row index to start.</param>
/// <param name="columnStart">First column index to start.</para
m>
/// <param name="rowCount">Number of rows.</param>
/// <param name="columnCount">Number of columns</param>
/// <returns>One-dimensional array of type TSource</returns>
/// <remarks> Jon Nyman 20130205
/// Source http://msmvps.com/blogs/jon_skeet/archive/2011/01/02/
reimplementing-linq-to-objects-part-24-toarray.aspx </remarks>
public static T[] ToArray<T>(this Range rng, Func<object, T> con
version
, int rowStart, int colu
mnStart, int rowCount, int columnCount)
{
rowStart += 1; columnStart += 1; rowCount += 1; columnCount
+= 1;
object[,] array = rng.Get2dArrayValue();
//Make sure values are within range of array.
if (rowStart < 0 || columnStart < 0 || rowStart > array.GetU
pperBound(0) || columnStart > array.GetUpperBound(1) ||
rowCount < 1 || rowCount + rowStart - 1 > array.GetUpper
Bound(0) || columnCount < 1 || columnCount + columnStart - 1 > array.Get
UpperBound(1))
throw new System.IndexOutOfRangeException("Start or end
values out of range (Parse)");
return array.ToArray<T>(conversion, rowStart, columnStart, r
owCount, columnCount);
} //End ToArray
/// <summary>
/// Copies the elements of the 2D object array row wise to a new
array of the specified element type and length.
/// </summary>
/// <typeparam name="T">New array type.</typeparam>
/// <param name="rng">Target range.</param>
/// <param name="conversion">Casting function of new type.</para
m>
/// <returns>One-dimensional array of type TSource</returns>
/// <remarks> Jon Nyman 20130205
/// Source http://msmvps.com/blogs/jon_skeet/archive/2011/01/02/
reimplementing-linq-to-objects-part-24-toarray.aspx </remarks>
public static T[] ToArray<T>(this Range rng, Func<object, T> con
version)
{
object[,] array = rng.Get2dArrayValue();
return array.ToArray<T>(conversion);
} //End ToArray
/// <summary>
/// Import Data From Excel as 2D zero-based Object Array
/// </summary>
/// <param name="rng">Target range.</param>
/// <param name="AsValue">True -> .Value else .Value2 (Default)<
/param>
/// <returns>2D object zero-based array</returns>
/// <remarks>Jon Nyman 121023</remarks>
public static object[,] To2dArray(this Range rng, bool AsValue)
{
if (rng.Areas.Count > 1)
return rng.ToArrayFromAreas(AsValue);
object[,] oResult = rng.Get2dArrayValue(AsValue);
int iRowUpper = oResult.GetUpperBound(0);
int iColumnUpper = oResult.GetUpperBound(1);
int iRowLower = oResult.GetLowerBound(0);
int iColumnLower = oResult.GetLowerBound(1);
object[,] oaResult = new object[iRowUpper, iColumnUpper];
for (int i = iRowLower; i <= iRowUpper; i++) {
for (int j = iColumnLower; j <= iColumnUpper; j++) {
oaResult[i - iRowLower, j - iColumnLower] = oResult[
i, j];
}
}
return oaResult;
} //End To2dArray
/// <summary>
/// Import Data From Excel as 2D zero-based Object Array with Va
lue2
/// </summary>
/// <param name="rng">Target range.</param>
/// <returns>2D object zero-based array</returns>
/// <remarks>Jon Nyman 121023</remarks>
public static object[,] To2dArray(this Range rng)
{
return rng.To2dArray(false);
} //End To2dArray
/// <summary>
/// Loop through areas of range and return single 2d zero-based
object array.
/// </summary>
/// <param name="rng">Target range.</param>
/// <param name="AsValue">True -> .Value else .Value2 (Default)<
/param>
/// <returns>2D object zero-based array</returns>
private static object[,] ToArrayFromAreas(this Range rng, bool A
sValue)
{
Range rArea = null;
int iColumnMax = 1;
object[][,] Objects2D = new object[rng.Areas.Count - 1][,];
int iAreaCount = 0;
XlSheetVisibility xlVisible = rng.ShowWorksheet();
foreach (Range rArea_loopVariable in rng.Areas)
{
rArea = rArea_loopVariable;
Objects2D[iAreaCount] = rArea.Get2dArrayValue(AsValue);
iColumnMax = Math.Max(iColumnMax, Objects2D[iAreaCount].
GetUpperBound(1));
iAreaCount += 1;
}
object[,] oaAreaResult = new object[rng.RowsCount(), iColumn
Max];
int iRow = -1;
for (int i2DArraysIndex = 0; i2DArraysIndex <= iAreaCount -
1; i2DArraysIndex++)
{
for (int iRowArea = 1; iRowArea <= Objects2D[i2DArraysIn
dex].GetUpperBound(0); iRowArea++)
{
iRow += 1;
for (int iColumnArea = 1; iColumnArea <= Objects2D[i
2DArraysIndex].GetUpperBound(1); iColumnArea++)
{
oaAreaResult[iRow, iColumnArea - 1] = Objects2D[
i2DArraysIndex][iRowArea, iColumnArea];
}
}
}
rng.RevertWorksheetVisibility(xlVisible);
return oaAreaResult;
} //End ToArrayFromAreas
/// <summary>
/// Return 2d 0-based or 1-based object array from range
/// </summary>
/// <param name="rng">Target range</param>
/// <param name="asValue">True -> .Value else .Value2 (Default)<
/param>
/// <returns>2D object zero-based or one-based array</returns>
private static object[,] Get2dArrayValue(this Range rng, bool as
Value)
{
XlSheetVisibility xlVisible = rng.ShowWorksheet();
object resultValue = asValue ? rng.Value : rng.Value2;
rng.RevertWorksheetVisibility(xlVisible);
if (resultValue != null && resultValue.GetType().IsArray) {
return (object[,]) resultValue;
}else{
return new object[,] { { resultValue } };
}
} //End GetValueOfRange
/// <summary>
/// Return 2d 0-based or 1-based object array from range
/// </summary>
/// <param name="rng">Target range</param>
/// <returns>2D object zero-based or one-based array</returns>
private static object[,] Get2dArrayValue(this Range rng)
{
return rng.Get2dArrayValue(false);
}
/// <summary>
/// Export Data to Excel
/// </summary>
/// <param name="rng">Target Range</param>
/// <param name="data">Data to export.</param>
/// <returns>Range where data was exported to.</returns>
/// <remarks>Jon Nyman 121023
/// 20130205 Convert to C#</remarks>
public static Range ToExcel(this Range rng, object[,] data)
{
if ((rng != null)) {
//Make sure range and 2D object match in size
Range rNew = rng.Resize(data.GetUpperBound(0) + 1, data.
GetUpperBound(1) + 1);
//Send to Excel
XlSheetVisibility xlVisible = rNew.ShowWorksheet();
rNew.Value2 = data;
rNew.RevertWorksheetVisibility(xlVisible);
return rNew;
}
return null;
} // End ToExcel
/// <summary>
/// Export Data to Excel
/// </summary>
/// <param name="rng">Target Range</param>
/// <param name="data">Data to export.</param>
/// <returns>Range where data was exported to.</returns>
/// <remarks>Jon Nyman 121023
/// 20130205 Convert to C#</remarks>
public static Range ToExcel(this Range rng, object data)
{
if ((rng != null))
{
if (data.GetType().IsArray) {
if (((Array)data).Rank == 1)
{
return rng.ToExcel((object[])data);
}
else
{
return rng.ToExcel((object[,])data);
}
} //End If IsArray
//Make sure range and 2D object match in size
Range rNew = rng.Resize(1, 1);
//Send to Excel
XlSheetVisibility xlVisible = rNew.ShowWorksheet();
rNew.Value2 = data;
rNew.RevertWorksheetVisibility(xlVisible);
return rNew;
}
return null;
} // End ToExcel
} //End class RangeExtenstions
}
LinqTo2dArray
LinqTo2dArray written in C# (to allow iteration over the rows). Also found on CodePlex.
- Parse will take a 2D Array (not necessarily zero based) and load it into a class. Parse 2D object array into a class row wise.
- Compose will create 2D object array from enumerable type.
- Copies the elements of the 2D object array row wise to a new array of the specified element type and length.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace LinqTo2dArray
{
public static class Array2dExtensions
{
/// <summary>
/// Parse 2D object array into a class row wise.
/// </summary>
/// <typeparam name="TSource">New enumerable type.</typeparam>
/// <param name="array">The two-dimensional Array to loop throug
h.</param>
/// <param name="conversion">Conversion function to TSource type
.</param>
/// <param name="rowStart">First row index to start.</param>
/// <param name="columnStart">First column index to start.</para
m>
/// <param name="rowCount">Number of rows.</param>
/// <param name="columnCount">Number of columns</param>
/// <example>
/// array.Parse(Of SaleOrder)(Function(o As Object()) New SaleO
rder( _
/// If(TypeOf o(0) Is Double
, Date.FromOADate(o(0)), #1/1/1900#) _
/// , If(Not TypeOf o(1) Is
Integer, o(1).ToString, "") _
/// , If(Not TypeOf o(2) Is
Integer, o(2).ToString, "") _
/// , If(Not TypeOf o(3) Is
Integer, o(3).ToString, "") _
/// , If(IsNumeric(o(4)) And
Also Not TypeOf o(4) Is Integer, CInt(o(4)), 0) _
/// , If(IsNumeric(o(5)) And
Also Not TypeOf o(5) Is Integer, o(5), 0) _
/// , o(6) + rowOffset _
/// ))
/// Note: Last index returns row number.
/// </example>
/// <exception cref="IndexOutOfRangeException">Parameters are ou
t of range of 2D array object.</exception>
/// <returns>Enumerable of TSource</returns>
/// <remarks>Jon Nyman 130205</remarks>
public static IEnumerable<TSource> Parse<TSource>(this object[,]
array, Func<object[], TSource> conversion
, int rowStart, int columnStart, int rowCount, int columnCou
nt)
{
//Make sure values are within range of array.
if (rowStart < 0 || columnStart < 0 || rowStart > array.GetU
pperBound(0) || columnStart > array.GetUpperBound(1) ||
rowCount < 1 || rowCount + rowStart - 1 > array.GetUpper
Bound(0) || columnCount < 1 || columnCount + columnStart - 1 > array.Get
UpperBound(1))
throw new System.IndexOutOfRangeException("Start or end
values out of range (Parse)");
//Put 2D array values in a 1D array for conversion
for (int row = rowStart; row < rowCount + rowStart; row++)
{
object[] array1d = new object[columnCount + 1];
for (int column = columnStart; column < columnCount + co
lumnStart; column++)
{
array1d[column - columnStart] = array[row, column];
}
//Add row number to the end of the 1D array.
array1d[columnCount] = row;
yield return conversion(array1d);
}
} //End Parse
/// <summary>
/// Parse 2D object array into a class row wise.
/// </summary>
/// <typeparam name="TSource">New enumerable type.</typeparam>
/// <param name="array">The two-dimensional Array to loop throug
h.</param>
/// <param name="conversion">Conversion function to TSource type
.</param>
/// <example>
/// array.Parse(Of SaleOrder)(Function(o As Object()) New SaleO
rder( _
/// If(TypeOf o(0) Is Double
, Date.FromOADate(o(0)), #1/1/1900#) _
/// , If(Not TypeOf o(1) Is
Integer, o(1).ToString, "") _
/// , If(Not TypeOf o(2) Is
Integer, o(2).ToString, "") _
/// , If(Not TypeOf o(3) Is
Integer, o(3).ToString, "") _
/// , If(IsNumeric(o(4)) And
Also Not TypeOf o(4) Is Integer, CInt(o(4)), 0) _
/// , If(IsNumeric(o(5)) And
Also Not TypeOf o(5) Is Integer, o(5), 0) _
/// , o(6) + rowOffset _
/// ))
/// Note: Last index returns row number.
/// </example>
/// <exception cref="IndexOutOfRangeException">Parameters are ou
t of range of 2D array object.</exception>
/// <returns>Enumerable of TSource</returns>
/// <remarks>Jon Nyman 130205</remarks>
public static IEnumerable<TSource> Parse<TSource>(this object[,]
array, Func<object[], TSource> conversion)
{
int rowStart=array.GetLowerBound(0);
int colStart=array.GetLowerBound(1);
int rowCount=array.GetUpperBound(0)-rowStart+1;
int colCount=array.GetUpperBound(1)-colStart+1;
return array.Parse<TSource>(conversion, rowStart, colStart,
rowCount, colCount);
}
/// <summary>
/// Copies the elements of the 2D object array row wise to a new
array of the specified element type and length.
/// </summary>
/// <typeparam name="T">New array type.</typeparam>
/// <param name="array">The two-dimensional object array.</param
>
/// <param name="conversion">Casting function of new type.</para
m>
/// <param name="rowStart">First row index to start.</param>
/// <param name="columnStart">First column index to start.</para
m>
/// <param name="rowCount">Number of rows.</param>
/// <param name="columnCount">Number of columns</param>
/// <returns>One-dimensional array of type TSource</returns>
/// <remarks> Jon Nyman 20130205
/// Source http://msmvps.com/blogs/jon_skeet/archive/2011/01/02/
reimplementing-linq-to-objects-part-24-toarray.aspx </remarks>
public static T[] ToArray<T>(this object[,] array, Func<object,
T> conversion
, int rowStart, int colu
mnStart, int rowCount, int columnCount)
{
//Make sure values are within range of array.
if (rowStart < 0 || columnStart < 0 || rowStart > array.GetU
pperBound(0) || columnStart > array.GetUpperBound(1) ||
rowCount < 1 || rowCount + rowStart - 1 > array.GetUpper
Bound(0) || columnCount < 1 || columnCount + columnStart - 1 > array.Get
UpperBound(1))
throw new System.IndexOutOfRangeException("Start or end
values out of range (ToArray)");
//Put 2D array values in a 1D array
T[] array1d = new T[rowCount * columnCount];
int currentRow = -1;
for (int row = rowStart; row < rowCount + rowStart; row++)
{
for (int column = columnStart; column < columnCount + co
lumnStart; column++)
{
currentRow += 1;
array1d[currentRow] = conversion(array[row, column])
;
}
}
return array1d;
} //End ToArray
/// <summary>
/// Copies the elements of the 2D object array row wise to a new
array of the specified element type and length.
/// </summary>
/// <typeparam name="T">New array type.</typeparam>
/// <param name="array">The two-dimensional object array.</param
>
/// <param name="conversion">Casting function of new type.</para
m>
/// <returns>One-dimensional array of type TSource</returns>
/// <remarks> Jon Nyman 20130205
/// Source http://msmvps.com/blogs/jon_skeet/archive/2011/01/02/
reimplementing-linq-to-objects-part-24-toarray.aspx </remarks>
public static T[] ToArray<T>(this object[,] array, Func<object,
T> conversion)
{
int rowStart=array.GetLowerBound(0);
int colStart=array.GetLowerBound(1);
int rowCount=array.GetUpperBound(0)-rowStart+1;
int colCount=array.GetUpperBound(1)-colStart+1;
return array.ToArray<T>(conversion, rowStart, colStart, rowC
ount, colCount);
} //End ToArray
/// <summary>
/// Create 2D object array from enumerable type.
/// </summary>
/// <typeparam name="TSource">Enumerable type to convert.</typep
aram>
/// <param name="enumerator">Enumerable to convert.</param>
/// <param name="conversion">Logic to convert to object array.</
param>
/// <param name="startIndex">First index to start.</param>
/// <param name="count">Number to convert.</param>
/// <param name="columnCount">Number of columns to create.</para
m>
/// <example>If clsItems.Count > 0 Then
/// oaTimes = clsItems.Compose(Function(cls) _
/// {CType(clsItems.DateUsed.ToOADate, Object) _
/// , cls.Name _
/// , cls.ItemHoursForDay _
/// , cls.ClockInTime}, 4)
///End If</example>
/// <returns>2D Object Array</returns>
/// <remarks>Jon Nyman 121109</remarks>
public static object[,] Compose<TSource>(this IEnumerable<TSourc
e> enumerator, Func<TSource, object[]> conversion
, int columnCount)
{
try
{
int count = enumerator.Count();
int row = -1;
object[,] array2d = new object[count, columnCount];
foreach (TSource item in enumerator)
{
row += 1;
object[] array = new object[columnCount];
array = conversion(item);
for (int j = 0; j <= columnCount - 1; j++)
{
array2d[row, j] = array[j];
}
}
return array2d;
}
catch (System.IndexOutOfRangeException)
{
throw new System.IndexOutOfRangeException("Incorrect num
ber of columns. Compose.");
}
} //End Compose
}
}
Attempted to Read or Write Protected Memory
I’ve been working on switching over to a “new” machine (more on that in a new post). In the process my time card program stopped working with the following error:
Unfortunately this error doesn’t show up until well after it occurs. So you need to make some educated guesses as to where it is coming from. So what I did is I just commented out code one at a time in the procedure where it was originating from. The code that was shown to be problematic was called from two different places - so the error occurred only from one of the branches but not the other. So that was a bit confusing. It was also confusing because it didn’t happen on my Windows XP computer, just on the “new” Windows 8 computer.
RSS for Old Blog Posts
One of the nice to haves on my list is a way to read old blog posts via RSS. I’ve found that it is really easy to read someone’s old material on their blog if it is drip fed to me. Well, after a little searching on the internet I found Stream Spigot’s Feed Playback. Now I can enjoy someone’s old posts without having to remember to go back every other day or once every week.
Max Write to Excel (2003) in Array
I normally don’t deal with very long strings. But I’m working on a project that requires storing a text file in Excel that I can later access (it would be nice just to embed it but Excel doesn’t like viruses – which makes me have to just store it in a worksheet).
So what is the max string length in an array write to an Excel range?
911
Seems kind of random but that is the number. Raise that number to 912 and you’ll get an error.
AdvancedFilter Wrapper Engine
A while back ago I promised to produce my engine for the advancedfilter wrapper. Well, at the prodding of Hugo I finally put it together. I took the code from my VB.NET project and converted it to VBA. Man, that reminds of why I love .NET so much! If you ever get a chance to learn .NET of VBA definitely take the opportunity. At first it is difficult but then it gets to the point where you don’t want to go back.
List of Excel Functions - Code To Use List
I saw a few people were downloading my list of Excel functions that I created. I thought it might be useful show the code that I use to get that data to a manageable format. Although the code is written in VB.NET it should be easily converted to VBA.
[wpdm_file id=3]
I use a simple structure (type in VBA) for the data.