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]

Bit Torrent Sync & Updating/Installing Programs

Bit Torrent Sync (BT Sync) is great software which can be used for syncing files. I use it for backing up all my pictures/videos/music/files/mobile computers. Well, I did some testing and it looks like it can be used for installing and updating software also. I tested it with my Windows 8 computer over to my Windows XP computer. I created an Excel xla add-in file and I also created an executable file. [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]

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]

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]

Sheet Exists

I was looking at Siddharth Rout’s new blog (Adding/Deleting Sheets to the Excel File) and realized that I haven’t posted my version of sheet exists. Mine probably does too much per Code Complete standards, but it is nice not to have to think of multiple. If I were to refactor this code I would take out the code for the charts and probably just make it work only for checking if the sheet exists and adding a new sheet if it doesn’t. [Read More]

Creating a Settings Class in VBA

In .NET they have a nice settings class you can create using ApplicationSettingsBase base class. In VBA it takes a bit more work to get it working, but can be fairly elegant if done correctly. Some criteria that is needed for a settings class in vba are: Well formed Fast lookup Default setting Single input/output procedures. To make it well formed I used an enumeration variable type to create each setting. [Read More]

AdvancedFilter Class Part 2

Determining What Options We Want

The first thing we’ll want to do is determine what macro options we would like in the class. The options I currently have are: Range to filter (optional – can use current range instead). Filter location (optional – if you are using an add-in then you can place in there, otherwise can put in temporary sheet). Filter action (copy to new range or filter in place (default)). Get unique items from a column (will need to use different method than AdvancedFilter). [Read More]

Wrapping a Class Around the AdvancedFilter Method

Classes are a tool in object-oriented programming that make programming much simpler. One class that I have developed in VBA is a class that makes using the AdvancedFilter method extremely easy and useful. In fact, I like it so much that I stopped using the regular find method. In the coming weeks I will demonstrate how this was done.  This is how the AdvancedFilter method works:  Use a predefined range to filter. [Read More]