• Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey

Excel & AutoHotkey-Replacing Null values in Excel

Replacing Null values in ExcelAutoHotkey Bottle 2When some programs export data they will insert #Null! into a cell to indicate there was no data (SPSS to name one).  While I would rather have something than nothing, when trying to compute averages or share data with clients the #Null! value is not ideal.  🙁  In this tutorial I demonstrate how easy it is to Replacing Null values in Excel with AutoHotkey.

The following video demonstrates Replacing Null values in Excel.  While I didn’t show the entire code in the video, it is below for your reference.

AutoHotkey Merchandise-White Stress ball

AutoHotkey Function for Replacing Null values in Excel

;***********get handle*******************
XL_Handle(XL,1) ; pointer to Application   XL_Handle(XL,2) Pointer to Workbook

;***********call function *******************
XL_Replace_Null(XL)
return

;***********replace Null*******************
XL_Replace_Null(PXL,RG:=""){
if (! RG)
    RG:=XL_Used_RG(PXL,0)
PXL.Range(RG).Replace("#NULL!","")  ;
}


;***********************Used Range********************************.
;~ XL_Used_RG(XL,Header:=1) ;Use header to include/skip first row
XL_Used_RG(PXL,Header=1){
IfEqual,Header,0,Return, XL_First_Col_Alpha(PXL) . XL_First_Row(PXL) ":" XL_Last_Col_Alpha(PXL) . XL_Last_Row(PXL)
IfEqual,Header,1,Return, XL_First_Col_Alpha(PXL) . XL_First_Row(PXL)+1 ":" XL_Last_Col_Alpha(PXL) . XL_Last_Row(PXL)
}

;***********************First row********************************.
;~ XL_First_Row(XL)
XL_First_Row(PXL){
   Return, PXL.Application.ActiveSheet.UsedRange.Rows(1).Row
}

;***********************Last Row********************************.
;~ XL_Last_Row(XL)
XL_Last_Row(PXL){
   Return, PXL.Application.ActiveSheet.UsedRange.Rows(PXL.Application.ActiveSheet.UsedRange.Rows.Count).Row
}
XL_Last_Col_Alpha(PXL){
LastCol:=XL_Last_Col_Nmb(PXL)
IfLessOrEqual,LastCol,26, Return, (Chr(64+LastCol))
	Else IfGreater,LastCol,26, return, Chr((LastCol-1)/26+64) . Chr(mod((LastCol- 1),26)+65)
}

XL_Last_Col_Nmb(PXL){
Return, PXL.Application.ActiveSheet.UsedRange.Columns(PXL.Application.ActiveSheet.UsedRange.Columns.Count).Column
}
;***********************First Column Alpha**********************************.
;~ XL_Last_Col_Alpha(XL)
XL_First_Col_Alpha(PXL){
FirstCol:=PXL.Application.ActiveSheet.UsedRange.Columns(1).Column
IfLessOrEqual,LastCol,26, Return, (Chr(64+FirstCol))
	Else IfGreater,LastCol,26, return, Chr((FirstCol-1)/26+64) . Chr(mod((FirstCol- 1),26)+65)
}

;***********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)
}

 

SPSS missing values & corresponding “missing” syntax to solve your needs

SPSS missing values

SPSS missing values

The syntax around SPSS variables with missing values is not intuitive, confusing, and poorly documented!

I know of 3 different types of commands and knowing which one to use when is not clear.  Setting SPSS missing values is a great way to simplify your analysis.  It is also a user-friendly way to remove (hide) outliers.  This video gives a short demo of how to use the three that I use frequently.

 

If you want to declare a value in a cell as missing the following syntax will give you a good start.

 if   Var1=1  Var1=$sysmis.
exe.

 

If you want to remove the values that are in a variable (define them as missing) the following syntax will be what you need.

 MISSING VALUES Var1 to Var10 (99).

 

SPSS missing values Macros

Below are two macros to help with missing data.  The first one is used when you first want to see if there is a given value present in another variable before declaring it recoding the missing a zero.  The second one will recode all variables with missing values a zero.

 
*///////////////.
DEFINE !Rep_Miss (Beg !TOKENS (1) /Prez !TOKENS (1) /End !TOKENS (1))
Do if !PREZ>0.
do repeat v=!BEG to !END.
if missing (v) v=0.
end repeat.
end if.
exe.
!ENDDEFINE.
*///////////////.

!Rep_Miss Prez=presentvariable Beg=v11 End=v19.

*///////////////.
DEFINE !Rep_Miss2 (Beg !TOKENS (1) /End !TOKENS (1))
do repeat v=!BEG to !END.
if missing (v) v=0.
end repeat.
exe.
!ENDDEFINE.
*///////////////.
/*!Rep_Miss2 Beg=v11 End=v19.
  • Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey