Find the Maximum Row/Column with VBA

Friday, 10 August 2012 13:27 Last Updated on Sunday, 12 August 2012 10:52 Written by Steve Powell

Introduction

Now that Excel 2007/2010 support up to 1 million rows we can’t hard-code the column/row limits anymore and oftentimes you’ll need to know what the Workbook supports. One reason that comes to mind is that you want to quickly find the last cell in the first column that has a value in it. So you use the End method of the Range starting at the bottom row. Another might be to determine if you have enough room to place the result of a very large query that has more than 256 columns and would need the bigger limits of 2007/2010.

There isn’t a single property on the Workbook that tells you what the limit is but it’s easy to figure out. I’ll give you a couple of simple functions to use.

Custom Functions in Excel: Part V, XLL-RTDs

Wednesday, 11 July 2012 18:05 Last Updated on Tuesday, 24 July 2012 16:08 Written by Steve Powell

Introduction

Why would you  want to call an RTD from an XLL? Recall that the way to call an RTD is through the RTD function in Excel, so the user needs to somehow remember how many parameters your function takes and in what order to supply them and even what the function is called. Good luck explaining that to your users!

Custom Functions in Excel: Part IV, XLLs

Tuesday, 03 July 2012 00:00 Last Updated on Monday, 23 July 2012 17:44 Written by Steve Powell

Introduction

We’ve looked at VBA, Automation Add-ins and RTDs in previous posts. XLLs are significantly faster, and allow the developer to define the names/parameters of the functions. They’re also not that difficult to implement, although setting up the definitions of your functions can be a bit tricky. Unlike the other C++ add-ins, the XLL is not a COM server, it uses the Excel C API.

Life in the Fast Lane: Excel Buffer Class (VBA)

Monday, 05 September 2011 00:00 Last Updated on Tuesday, 06 September 2011 03:33 Written by Steve Powell

Introduction

In a previous article I wrote about creating a buffer class in C# to access cells in a worksheet. The theory behind it was that accessing Excel one cell at a time is slow while doing it in batches is significantly faster. The same theory applies to all languages that you can program Excel in so in this article I present a class for VBA projects that does the same thing.

Guide to Writing Custom Functions in Excel: Part III, C++ RTD add-in

Saturday, 03 September 2011 03:58 Last Updated on Monday, 23 July 2012 17:45 Written by Steve Powell

Introduction

In the previous article we developed a custom function in Excel using an Automation Add-in. It was fairly straightforward and didn’t take  long but performance isn’t one of it’s strengths and there is no way to properly document it within Excel for the user. The RTD is an automation add-in that implements the IRtdServer interface so creating it is similar. The RTD add-in is different from the other options in that it allows asynchronous calls which is a huge advantage when the function needs to make a call to a database or web service. Because RTDs are asynchronous, the user can continue to work in Excel while the function is calculating. The downsides of RTDs are that they do not appear in the function wizard and the user is forced to use a clunky syntax.

Asynchronous XLLs (topic of a future post)  are similar to RTDs in that they can pull data into Excel but the difference with RTD add-ins is that they can also push data into Excel. So if the RTD server provided stock prices and the value of a stock updated, the RTD could tell Excel that there is a new value and update Excel. An XLL cannot do that, the user would be forced to re-calculate the formula in order to get updated values.

There are several articles online about creating RTD servers. I find most of them overly complex, so in this post I hope to show you a more straightforward approach that takes advantage of the Visual Studio IDE and requires the minimum of code changes (more clicking, less code).

The Need for Speed: Excel Buffer Class

Friday, 02 September 2011 22:47 Last Updated on Monday, 23 July 2012 16:51 Written by Steve Powell

Introduction

There are two ways to change cell values in an Excel spreadsheet: the wrong way and the fast way. The wrong way takes the form of a loop where each cell is individually accessed in a for loop. Each time you assign a value, your add-in makes a COM call into Excel. Calling COM object methods from C# or anything for that matter, is a slow process. It might be 10ms or so (just a guess) but if you were looping through 200k cells that becomes 2000 seconds of COM calls.

Guide to Writing Custom Functions in Excel: Part II Automation Add-In

Sunday, 24 April 2011 09:38 Last Updated on Monday, 23 July 2012 17:46 Written by Steve Powell

Introduction

In the previous article we looked at writing custom functions that can be used in Excel formulas using VBA. VBA has the advantage of being easy and quick. An automation add-in is another option that is also very easy assuming you know a bit about C++ programming. It is possible to use other languages such as VB, Delphi or C# but for this article we will be using C++.

Guide to Writing Custom Functions in Excel: Part I, Using VBA

Sunday, 24 April 2011 09:25 Last Updated on Monday, 23 July 2012 17:47 Written by Steve Powell

Have you ever wanted to create your own functions in Excel? There are several ways to accomplish this goal, all have their strengths and weaknesses. The options for creating a custom function are listed in the table below. In this post we'll be looking at the easiest option that requires only Excel. The other options are more difficult and time consuming but the advantage is increased speed.

Sorting worksheets in Excel using VBA

Wednesday, 12 January 2011 09:50 Last Updated on Saturday, 03 September 2011 11:25 Written by Steve Powell

Imagine that you have a large file with 20 or more worksheets. It could be, for instance, a file with snapshot financial models for the companies in your coverage universe or a breakdown of home sales in different regions of the city.

You want to sort these worksheets into some kind of order that would allow you and your team to access your data more quickly. The SortSheets macro will help you to sort your sheet tabs into any kind of order or metric you want (e.g alphabetically, by ranking, etc.) using the qsort algorithm