Use Variables/Parameters to pass Methods or Properties in COM w/AutoHotkey

pass Methods or Properties in COMI had wanted to be able to make a function where I pass Methods or Properties in COM.  (this makes it much more flexible and eases updating / maintaining of code.)  Unfortunately it was not as straight-forward as I’d hoped.

Thankfully maestrith, Author of AutoHotkey Studio, had already solved this problem and shared the solution with me!  The code which shows how to pass Methods or Properties in COM is listed below.  While in the video I demonstrate connecting to IE and perform web scraping, the solution is equally applicable to any COM object like Excel, etc.

 

Demonstration Video to pass Methods or Properties in COM

Passing methods or properties as a variable in a COM object

AutoHotkey and Excel Worksheet functions: Mean/Average, Median & Mode

Excel worksheet functionsTypically I like to use AutoHotkey to insert the formula into Excel.  This allows for the user to make changes (or have it update if the  data changes).   Sometimes I just want a given value based on the data at-hand.  This is when it is awesome to be able to call the Excel worksheet functions.  In the below code and video I demonstrate how to call three Summary variables of central tendency: Mean (Average), Median and Mode.   For normally distributed data these will be the same however, in the real world, data is often skewed / non-bell shaped (has kurtosis).  Make sure you review the data to decide which best “summarizes” the data.

In the video I show how I initially planned to create three separate functions (one each summary variable) however with some help from Maestrith (author of AHK Studio) I was able to write one function and pass the method as a parameter.  🙂

Here is the script for Excel worksheet functions

And here is the video where I walk through Excel worksheet functions

Excel & AutoHotkey-Worksheet functions: Mead, Median & Mode

Excel & AutoHotkey-Clear Excel content & formatting

Clear ExcelIn this tutorial I demonstrate an AutoHotkey function I wrote where I clear Excel: Content, Formatting, Hyperlinks, Comments, etc.  This can be particularly helpful when you’ve got a file that either has formatting you want (or don’t want) and you want to keep the cell content intact.

Here is the Video demonstrating how to Clear Excel cells

Excel & AutoHotkey-Clear Cell: Content, Format, Links, etc.

The code below is what I use to Clear Excel data/formatting.  Don’t forget to include the “handle” to connect to the current Excel file.

 

Find Column Header in Excel & return location with AutoHotkey

Column Header in ExcelOften I’ll import a file which had headers and I’ll want to determine what column specific headers are in. This AutoHotkey function shows how you can pass an Array of search terms and return the Column Header in Excel in a key-value paired object.

 

Here’s the video walking through the Column Header in Excel

Excel & AutoHotkey- Search Headers for terms & return references to columns