In this video I demonstrate a new function I added to my AHK Excel Library. VLookups are really cool! In the below video I demonstrate how to use the function. Note, near the end I tried turning off the “exact match”. This doesn’t create a “fuzzy” match, it looks for the next highest value. (Probably something you should be very careful using…)
Tag: Excel function
AutoHotkey and Excel Worksheet functions: Mean/Average, Median & Mode
Typically 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
XL_Handle(XL,1)
MsgBox % "median " XL_WF_Averages(XL,"A1:H1","Median")
MsgBox % "average " XL_WF_Averages(XL,"A1:H1","Average")
MsgBox % "mode " XL_WF_Averages(XL,"A1:H1","Mode")
;***********Delete blank columns*******************
XL_WF_Averages(PXL,RG,Summary_Var){
app:=PXL.Application.WorkSheetFunction
Try val:=app[Summary_Var](PXL.Application.ActiveSheet.Range(RG))
return val
}
;***********Handle*******************
XL_Handle(ByRef PXL,Sel){
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN ;identify the hwnd for Excel
IfEqual,Sel,1, Return, PXL:= ObjectFromWindow(hwnd,-16).application ;Handle to Excel Application
IfEqual,Sel,2, Return, PXL:= ObjectFromWindow(hwnd,-16).parent ;Handlle to active Workbook
IfEqual,Sel,3, Return, PXL:= ObjectFromWindow(hwnd,-16).activesheet ;Handle to Active Worksheet
}
;***borrowd & tweaked from Acc.ahk Standard Library*** by Sean Updated by jethrow*****************
ObjectFromWindow(hWnd, idObject = -4){
(if Not h)?h:=DllCall("LoadLibrary","Str","oleacc","Ptr")
If DllCall("oleacc\AccessibleObjectFromWindow","Ptr",hWnd,"UInt",idObject&=0xFFFFFFFF,"Ptr",-VarSetCapacity(IID,16)+NumPut(idObject==0xFFFFFFF0?0x46000000000000C0:0x719B3800AA000C81,NumPut(idObject==0xFFFFFFF0?0x0000000000020400:0x11CF3C3D618736E0,IID,"Int64"),"Int64"), "Ptr*", pacc)=0
Return ComObjEnwrap(9,pacc,1)
}And here is the video where I walk through Excel worksheet functions
AutoHotkey and Excel Object model: Application, Workbook, Worksheet
While using AutoHotkey to connect to Excel is “easy”, it is also easy to be trying to use call a function to the wrong Excel object. In this video we discuss the below diagram and explain how to use AutoHotkey and Excel Object model.
Here is the function for specifying which Excel object you want to connect to
;1=Application 2=Workbook 3=Worksheet
XL_Handle(XL_Applic,1) ;Application
XL_Handle(XL_workbook,2) ;Workbook
XL_Handle(XL_Worksheet,3) ;Worksheet
MsgBox % XL_Applic.name "`r"
. XL_workbook.name "`r"
. XL_Worksheet.name
XL_Handle(ByRef PXL,Sel){
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN ;identify the hwnd for Excel
IfEqual,Sel,1, Return, PXL:= ObjectFromWindow(hwnd,-16).application ;Handle to Excel Application
IfEqual,Sel,2, Return, PXL:= ObjectFromWindow(hwnd,-16).parent ;Handlle to active Workbook
IfEqual,Sel,3, Return, PXL:= ObjectFromWindow(hwnd,-16).activesheet ;Handle to Active Worksheet
}
;***********adapted from ACC.ahk*******************
ObjectFromWindow(hWnd, idObject = -4){
(if Not h)?h:=DllCall("LoadLibrary","Str","oleacc","Ptr")
If DllCall("oleacc\AccessibleObjectFromWindow","Ptr",hWnd,"UInt",idObject&=0xFFFFFFFF,"Ptr",-VarSetCapacity(IID,16)+NumPut(idObject==0xFFFFFFF0?0x46000000000000C0:0x719B3800AA000C81,NumPut(idObject==0xFFFFFFF0?0x0000000000020400:0x11CF3C3D618736E0,IID,"Int64"),"Int64"), "Ptr*", pacc)=0
Return ComObjEnwrap(9,pacc,1)
}
return
