Category Archives: Excel

How do I create a GUI add in for excel?

Questions: I’ve done some mucking around on Google but I still can’t find what project this would be in Visual Studio. I want to create a GUI add in for Excel, for example a dock window on a ribbon with a windows form. What project would this be in Visual Studio? I have tried Excel… Read More »

Excel C# Interop vs. Excel VBA

Questions: I am in charge of finding bottlenecks in a Excel Add-in. Some of the workbooks calculate extremely slowly. I have the experience of working with VBA code, and the older versions of our product used all VBA code to execute calculations. However, in the newer versions of the product, a lot of the calculations… Read More »

Copy Row If Cell Range Are Not Blank

Questions: I wonder whether someone may be able to help me please. I have an Excel (2003) spreadsheet called ‘Input’ with data in columns B to N. What I would like to be able to do is if there is text in any of the cells in column B, I would like to copy the… Read More »

Append or prepend to array without VBA/UDF

Questions: In Excel, is there a built-in formula/function to append or prepend a value to an array? So suppose I have my own UDF, MyFunc(…) that returns an array of values. Can I append or preprend a single value to this result using a reference to a single cell? Answers: As we discussed in the… Read More »

How to speed up RANK() in Excel

Questions: Building a tool in Excel and as part of the exercise I need to identify the top 50 securities based on market cap. This is an easy enough thing to do using RANK(), but I’ve got a universe of over 10,000 and processing of an otherwise lightening quick workbook gets sluggish once I add… Read More »

Better way to programmatically Lock/Disable multiple UI controls on Ribbon bar

Questions: I have been wondering, what would be the better way to implement Locking/Disabling a bunch of Ribbon UI controls by just a click? Currently, my approach is kinda simple and basic: private void tbtnLock_Click(object sender, RibbonControlEventArgs e) { if (tbtnLock.Checked) { control1.Enabled = false; control2.Enabled = false; control3.Enabled = false; //… //controlN.Enabled = false;… Read More »

Powershell reading Excel date as 5 digit number

Questions: I have a Powershell script that uses this Powershell module. It basically allows me to query Excel workbooks like a database. Everything looks good except it reads dates in as that wonky 5-digit date representation instead of a date. I’ve checked the column formatting in the files themselves and they are formatted as MM/DD/YYYY… Read More »