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.

Profile .Net MS Office Add-ins for FREE

Thursday, 02 August 2012 00:00 Last Updated on Sunday, 12 August 2012 10:52 Written by Steve Powell

Introduction

While developing any Microsoft Office add-in (Excel, Word, Outlook, Powerpoint, Access, etc.), you will eventually encounter performance problems and often it will be difficult to see what is causing the poor performance. One way to diagnose poor performance is to pause the code at random with Visual Studio and hopefully Visual Studio will stop inside the slow function. The odds are higher that it will stop in the slowest function because that’s what’s taking up the most time. The other more exact option is to use a profiler, even if your budget is zero you can still do it for free. SlimTune which you can get here: http://code.google.com/p/slimtune/ is fantastic for profiling C# add-ins (it supports .net 4.0). Figuring out how to use it wasn’t immediately obvious, so let me show you how to do it (I’m also writing this in case I forget).

Build an Access Form and Report with Multiple Criteria

Tuesday, 31 July 2012 07:39 Last Updated on Sunday, 12 August 2012 10:53 Written by Steve Powell

Introduction

Sometimes getting multiple controls to talk nicely to each other in a Microsoft Access form can be difficult. In this example the goal is to create a form with a list box with multiple columns which the user will be able to select a company with and a combo box that will filter it’s results based on the selection in the listbox. The two controls will be used as a filter for a report that will be opened when the user clicks on a button on the form.

Highlight Dependent Cells when a Value Changes

Sunday, 29 July 2012 16:20 Last Updated on Sunday, 12 August 2012 10:54 Written by Steve Powell

Introduction

This is a quick example of how you can use the Dependents property of the Range object to highlight the dependencies of a cell when it’s changed. The idea is that when a cell is changed the code will show the cells that whose formulas depend on the value in the cell that has just been changed.

Excel Tip: Match vs Vlookup

Tuesday, 24 July 2012 07:52 Last Updated on Thursday, 26 July 2012 21:17 Written by Steve Powell

Introduction

The time it takes to calculate a workbook is dependent on which functions you are using. Many times it’s possible to accomplish the same goal using a different function that’s faster. In this post I’ll look at the difference between using vlookup and a combination of match and index.

THZ39KZW5F6W

Domain Change

Monday, 23 July 2012 07:25 Written by Administrator

I’ve changed the domain from xlspot.com to exceladviser.com. I didn’t think the old name really worked that well and it was about time I changed it. Also I’ve updated the XLL-RTD hybrid post. It now actually works consistently! Hooray!

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!

WPF Dynamic View Model with Enums

Tuesday, 10 July 2012 14:37 Last Updated on Tuesday, 10 July 2012 19:39 Written by Steve Powell

I posted an article to codeproject that shows how to create a dynamic view model for WPF that supports enums. It makes it easy to use enums in the model and create list boxes in the UI to allow users to select the value. You can  find the article here.

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.

COM and .Net: Cleaning up

Tuesday, 06 September 2011 08:30 Last Updated on Monday, 23 July 2012 16:51 Written by Steve Powell

Introduction

Managing COM resources in an Office add-in is very important since ignoring it means Excel might not even close with your add-in installed. Ironically C++ is a bit easier this way because Visual Studio can generate wrapper classes for you that take care of releasing the COM objects as long as they are created on the stack or deleted explicitly. You might think that the .Net garbage collector takes care of the objects but it doesn’t entirely, it just frees up the proxy object not the COM resource.