JavaScript

I’ve jumped ship from Excel programming over to JavaScript (in a functional manner). I’ve also gone the way of Linux away from Windows. I love Excel and its power. But I see the writing on the wall and am moving to where I can hopefully get some work and starting making some moolah. You can follow my new blog over at http://thisisafiller.ghoster.io/. If anyone is interested in the products I’ve made let me know how you like them and send me an e-mail. [Read More]

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. [Read More]

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. [Read More]

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. [Read More]

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. [Read More]

AdvancedFilter Wrapper Engine

for VBA Enthusiasts

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. [Read More]

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. [Read More]

Scribble Filter (For Excel)

Overview Part 4

Points discussed in video:

The scribble filter is a text wrapper for the Advanced Filter in Excel, making it easy to work with and adding new capabilities.

  • Use both {AVERAGE} function and Excel AVERAGE function @00:04
  • Click on Excel function with cursor to goto Excel’s function web page @01:18
  • Using ranges as a formula. @01:33

Scribble Filter Home Scribble Filter Help

Overview Part 1 Overview Part 2 Overview Part 3 Overview Part 4