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.
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.
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.
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.
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.
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. Private Structure ExcelFunctionInfo Dim Name As String Dim Category As String Dim ExcelVersion As Integer Dim Description As String Dim Syntax As String Dim URL As String End Structure I then just loop through the data in the string that was gotten from the text file.
Excel Queries
I came across some nice libraries to use while programming to query Excel. Right now I’m am using the standard COM interface for my projects, which is fine for small ranges (which is true for most users – I would assume). But for large ranges I would need something more powerful. LinqToExcel LinqToExcel appears to be a great project for using a Linq interface to query Excel (but not write to Excel).