VBA to AHK Translator (Excel)

Post your working scripts, libraries and tools for AHK v1.1 and older
AHK_user
Posts: 515
Joined: 04 Dec 2015, 14:52
Location: Belgium

VBA to AHK Translator (Excel)

Post by AHK_user » 17 Nov 2020, 18:22

The following small but powerfull code will (try to) translate VBA code to AHK Com Object code.
The initial focus is to translate recorded VBA code, because it is more consistant and predictable generated, but it also works on cleanly written code.

This script is still work in progress, If it failes, please provide me with the VBA and correct AHK code, and we can try to improve it.

If popular, it can also be expanded to other programs like Word, Outlook, ...

Tips and advice to optimize the script are welcome.
This library is needed to run the script:
https://github.com/dmtr99/VBAtoAHK/raw/main/Acc.ahk

The file Constants.txt will be automatically downloaded when running the script for the first time in the same folder of the script to allow it to add the correct Excel constants.
https://github.com/dmtr99/VBAtoAHK/blob/main/Constants.txt

Updates:
- 2020-11-18: Added some mso Constants and renamed constant file to "Constant.txt"
- 2020-11-22: Added all MS Constants, and added Word translation and a Macro Explorer (Seems to fail for Word, no idea why...)
- 2020-12-08: Added automatic download of constants.txt and set function to work global
- 2021-10-21: Correction on some word functions

Possible future improvements:
- Improve failed translations
- Add Outlook,... translation
VBA to AHK code.png
VBA to AHK code.png (21.31 KiB) Viewed 8073 times

Code: Select all

; Created by AHK_User
; Used to convert recorded Excel VBA Code to AHK com Code
; Already working fine in a lot of cases, but a lot of improvements can be made
; 2021-10-21: Added Word translation and Macro Explorer (to make Macro Explorer work, please lower the macro security)
; ; Created by AHK_User
; Used to convert recorded Excel VBA Code to AHK com Code
; Already working fine in a lot of cases, but a lot of improvements can be made
; 2020-11-22: Added Word translation and Macro Explorer (to make Macro Explorer work, please lower the macro security)
; 2020-12-08: Added automatic download of constants.txt and set function to work global
; 2021-10-21: Correction on some word functions.
#Requires AutoHotkey v1.1.33+
#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#SingleInstance, Force
global arrMacro := {}
global arrXlsConstants := VBAtoAHK_LoadArrConstants()
global vProgram

Gui, 1: Default

Menu, FileMenu, Add, &Open Macro Explorer, VBAtoAHK_GetExcelWindows  ; See remarks below about Ctrl+O.
Menu, MyMenuBar, Add, &File, :FileMenu  ; Attach the two sub-menus that were created above.
Gui, Menu, MyMenuBar

Gui, Add, Text,, VBA Code:
Gui, Add, DropDownList, vvProgram ggVBA_Code x430 yp w80, Excel||Word ; (idea to also make it work on other programs than Excel)
Gui, Add, Edit, vvVBA_Code ggVBA_Code xm y30 r30 w500 t5, %Clipboard%
Gui, Add, Text, ym x520, AHK Code:
Gui, Add, CheckBox, vvInitiation x900 yp ggVba_Code, Show initiation.
Gui, Add, Edit, vvAHK_Code x520 y30 r30 w500  t5, % VBAtoAHK(Clipboard)
Gui, Add, Button, ggRun_AHK_Code, Run  ; The label ButtonOK (if it exists) will be run when the button is pressed.
Gui, Add, Button, ggEdit_AHK_Code x+5 yp, Edit  ; The label ButtonOK (if it exists) will be run when the button is pressed.
Gui, Show,, VBA to AHK code
GoSub, gVBA_Code
return  ; End of auto-execute section. The script is idle until the user does something.

gVBA_Code:
Gui, Submit, NoHide
AHK_Code := VBAtoAHK(vVBA_Code)
if (vInitiation and !InStr(AHK_Code, "Create a connection to an Excel Application")){
	VBAtoAHK_AddInitiation(AHK_Code)
}
Guicontrol, Text, vAHK_Code, % AHK_Code
return

gRun_AHK_Code:
Gui, Submit, NoHide
FileAHK := A_ScriptDir "\AHK_Code.ahk"
FileDelete, %FileAHK%
FileAppend,  #SingleInstance`, Force`, %FileAHK%
VBAtoAHK_AddInitiation(vAHK_Code)
FileAppend, %vAHK_Code% , %FileAHK%
Run, % FileAHK
return

gEdit_AHK_Code:
Gui, Submit, NoHide
FileAHK := A_ScriptDir "\AHK_Code.ahk"
FileDelete, %FileAHK%
FileAppend,  #SingleInstance`, Force`, %FileAHK%
VBAtoAHK_AddInitiation(vAHK_Code)
FileAppend, %vAHK_Code% , %FileAHK%
Run edit "%FileAHK%"
return

GuiClose:
Gui, Submit  ; Save the input from the user to each control's associated variable.
ExitApp

MenuHandler:
MsgBox, test
return

VBAtoAHK(VBA_Code){
	global
	; Cleans the VBA returns
	VBA_Code:= RegExReplace(VBA_Code, "_\R\s*", "")
	
	AHK_Code := ""
	
	ArrWith := {}
	ArrWith["Level"] := 0
	WithCount := 0
	
	loop, Parse, VBA_Code, `n, `r
	{
		Line := A_LoopField
		
		
		Line := RegExReplace(Line, "^(\s*.*?=\s-?\d*)\.(\d.*)$", "$1,$2")
		FirstWord := RegExReplace(Line, "^\s*(\w*).*$", "$1")
		
		if !RegExMatch(Line, "^\s*If\s"){
			Line := StringCodeReplace(Line, " =", " :=")
		}
		
		if (vProgram="Excel"){
			; Adding missing leading Object definitions
			Line := RegExReplace(Line, "([\s(:=]+|^)(Sheets)", "$1oExcel.ActiveWorkbook.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(Worksheets)", "$1oExcel.ActiveWorkbook.$2")
			
			Line := RegExReplace(Line, "([\s(:=]+|^)(Cells)", "$1oWorkSheet.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(Columns)", "$1oWorkSheet.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(Range\()", "$1oWorkSheet.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(Rows\()", "$1oWorkSheet.$2")
			
			Line := RegExReplace(Line, "([\s(:=]+|^)(ActiveCell)", "$1oExcel.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(ActiveSheet)", "$1oExcel.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(ActiveWindow)", "$1oExcel.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(Application)", "$1oExcel.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(InputBox)", "$1oExcel.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(Selection)", "$1oExcel.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(UsedRange)", "$1oExcel.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(Visible)", "$1oExcel.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(Workbooks)", "$1oExcel.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(WorksheetFunction)", "$1oExcel.$2")
			
			Line := RegExReplace(Line, "([\s(:=]+|^)(ActiveWorkbook)", "$1oExcel.Application.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(DisplayAlerts)", "$1oExcel.Application$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(EnableEvents)", "$1oExcel.Application$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(ScreenUpdating)", "$1oExcel.Application$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(Calculation)", "$1oExcel.Application$2")
			
			; For Range Object
			VBAtoAHK_ParameterCheck(Line, "AutoFilter", "Field|Criteria1|Operator|Criteria2|SubField|VisibleDropDown")
			VBAtoAHK_ParameterCheck(Line, "AutoFitt", "Destination|Type")
			
			; For Sheet Object
			VBAtoAHK_ParameterCheck(Line, "Add", "Before|After|Count|Type")
			
		}
		Else if (vProgram="Word"){
			; Adding missing leading Object definitions
			Line := RegExReplace(Line, "([\s(:=]+|^)(ActiveDocument)", "$1oWord.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(Documents)", "$1oWord.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(Selection)", "$1oWord.$2")
			Line := RegExReplace(Line, "([\s(:=]+|^)(PageSetup)", "$1oDoc.$2")
			
			Line := RegExReplace(Line, "([\s(:=]+|^)(Visible)", "$1oWord.$2")
			
			VBAtoAHK_ParameterCheck(Line, "TypeText", "Text")
			VBAtoAHK_ParameterCheck(Line, "EndKey", "Unit|Extend")
			
			Line := RegExReplace(Line, "^(\s*.*?\.MoveLeft)\s(.*)$", "$1($2)")
			Line := RegExReplace(Line, "^(\s*.*?\.MoveRight)\s(.*)$", "$1($2)")
			Line := RegExReplace(Line, "^(\s*.*?\.Tables.Add)\s(Range.*)$", "$1($2)")
			Line := RegExReplace(Line, "^(\s*.*?\.InsertRowsBelow)\s(.*)$", "$1($2)")
			Line := RegExReplace(Line, "^(\s*.*?\.InsertFile)\s\(?(.*?)\)?$", "$1($2)")
			Line := RegExReplace(Line, "^(\s*.*?\.Add)\s(.*)$", "$1($2)")
		}
		
		Line := RegExReplace(Line, "i)^(\s*)Set\s(.*)$", "$1$2")
		
		; Converting comments
		Line := RegExReplace(Line, "\s'", " `;")
		Line := RegExReplace(Line, "^'", "`;")
		Line := RegExReplace(Line, "^(\s*)Dim\s", "$1`; Dim ")
		
		if RegExMatch(Line, "^\s*With\s"){
			WithCount++
			ArrWith["Level"]++
			ArrWith[ArrWith["Level"]] := RegExReplace(Line, "(^\s*)(\w*)\s(.*)$", "$3")
			VarWith := RegExReplace(Line, "(^\s*)(\w*)\s(.*)$", "$3")
			VarWithSpaces := RegExReplace(Line, "(^\s*)(\w*)\s(.*)$", "$1")
			
			ArrWith[ArrWith["Level"], "Spaces"] := RegExReplace(Line, "(^\s*)(\w*)\s(.*)$", "$1")
			if InStr(VarWith, "."){
				Line := RegExReplace(Line, "^(\s*)With\s(.*)$", "$1oWith" WithCount " := $2")
				ArrWith[ArrWith["Level"]] := "oWith" WithCount
			}
			Else{
				ArrWith[ArrWith["Level"]] := VarWith
				Continue
			}
		}
		
		; Correcting With...
		Line := RegExReplace(Line, "(\s|\()\.", "$1" ArrWith[ArrWith["Level"]] ".")
		if (VarWith!=""){
			Line := RegExReplace(Line, "^(\s*)(.*)$", VarWithSpaces "$2")
		}
		
		; Functions
		Line := RegExReplace(Line, "^(\s*.*?\.[A-Z0-9][A-Za-z0-9]*)\s("".*"")\s*$", "$1($2)")
		
		; Handling if else statements
		Line := RegExReplace(Line, "i)^(\s*If)\s(.*)\sThen$", "$1($2){")
		Line := RegExReplace(Line, "i)^(\s*)ElseIf\s(.*)\sThen$", "}`n$1Else If($2){")
		Line := RegExReplace(Line, "i)^(\s*)End If$", "$1}")
		Line := RegExReplace(Line, "i)^(\s*)Else$", "$1}`n$1Else{")
		
		; Defining variables
		PreConstants := "xl|mso|wd|sig|rgb|pp|pb|ol|_xl|Backstage|Broadcast|cert|cipher|contverres|empty|enc|mf"
		Variable := RegExReplace(Line, "(:=\s?|\()(" PreConstants ")[A-Z0-9][A-Za-z0-9]*", "$1", RegexCount)
		LineEnd := Line
		LineStart := ""
		Loop, %RegexCount%
		{
			Variable := RegExReplace(LineEnd, "^.*?(\s?:=\s?|\()((" PreConstants ")[A-Z0-9][A-Za-z0-9]*).*$", "$2", RegexCount)
			LineStart := LineStart RegExReplace(LineEnd, "^(.*?(\s?:=\s?|\()(" PreConstants ")[A-Z0-9][A-Za-z0-9]*)(.*)$", "$1 := " arrXlsConstants[Variable] , RegexCount)
			LineEnd := RegExReplace(LineEnd, "^(.*?(\s?:=\s?|\()(" PreConstants ")[A-Z0-9][A-Za-z0-9]*)(.*)$", "$4", RegexCount)
			Line := LineStart LineEnd
		}
		
		; With statements can be skipped, but we will safe the object and the leading spaces
		
		if RegExMatch(Line, "^\s*ChDir\s"){
			Continue
		}
		if RegExMatch(Line, "^\s*End\sSub"){
			Line := "return`n}"
		}
		if RegExMatch(Line, "^\s*End\sWith"){
			ArrWith["Level"]--
			VarWith := ""
			VarWithSpaces := ""
			Continue
		}
		if RegExMatch(Line, "^\s*Sub\s"){
			if (!InStr(VBA_Code, "End Sub")){
				Continue
			}
			Line := RegExReplace(Line, "(^\s*)(\w*)\s(.*)$", "$1$3{`n$1global")
		}
		
		Line := StringCodeReplace(Line, " & ", " ")
		
		Line := RegExReplace(Line, "i)^(.*:=\s?\d*)\.(\d*)$", "$1,$2")
		AHK_Code.= Line "`n"
		if (vProgram="Excel"){
			; Connect back to activesheet when deleting or adding sheets
			if (InStr(Line, "Sheets.Delete") or InStr(Line, "Sheets.Add")){
				AHK_Code.= "oWorkSheet := oExcel.ActiveSheet`n"
			}
		}
	}
	;~ DebugWindow(AHK_Code)
	return AHK_Code
}

VBAtoAHK_LoadArrConstants(){
	if !FileExist(A_ScriptDir "\Constants.txt"){
		URLDownloadToFile, https://github.com/dmtr99/VBAtoAHK/raw/main/Constants.txt, %A_ScriptDir%\Constants.txt
	}
	FileRead, xlsInterfaceConstants, %A_ScriptDir%\Constants.txt
	
	arrXlsConstants := {}
	loop, Parse, xlsInterfaceConstants, `n, `r
	{
		Variable := RegExReplace(A_LoopField, "^([^\s]*)\s.\s([^\s]*)$", "$1")
		Value := RegExReplace(A_LoopField, "^([^\s]*)\s.\s([^\s]*)$", "$2")
		arrXlsConstants[Variable] := Value
	}
	return arrXlsConstants
}

VBAtoAHK_ParameterCheck(ByRef Line, Method, ListParameters){
	if InStr(Line, "." Method " "){
		Line1 := RegExReplace(Line, "^(.*\." Method ")\s.*", "$1(")
		Loop, Parse, ListParameters , |
		{
			Var := RegExReplace(Line, ".*\." Method "\s.*(" A_LoopField "\s?:?=[^,]*).*", "$1", RegexCount)
			if (RegexCount=1){
				Line1 .= Var ","
			}
			Else{
				Line1 .= ","
			}
		}
		Line := Line1 ")"
		Line := RegExReplace(Line, "^(.*?),*\)$", "$1)")
	}
	return Line
}

VBAtoAHK_AddInitiation(ByRef Code){
	if (!InStr(Code, "Create a connection to an Excel Application") and vProgram="Excel"){
		Code := "Try{`n`toExcel := ComObjActive(""Excel.Application"") `; Create a connection to an Excel Application object`n}`nCatch{`n`toExcel := ComObjCreate(""Excel.Application"") `; Create an Excel Application object`n`toExcel.Visible := true`n}`n`noWorkSheet := oExcel.ActiveSheet`n" Code 
	}
	else if (!InStr(Code, "Create a connection to an Word Application") and vProgram = "Word"){
		Code := "Try{`n`toWord := ComObjActive(""Word.Application"") `; Create a connection to an Word Application object`n}`nCatch{`n`toWord := ComObjCreate(""Word.Application"") `; Create an Word Application object`n`toWord.Visible := true`n}`n`noDoc := oWord.ActiveDocument`n" Code
	}
	else if (!InStr(Code, "Create a connection to an Outlook Application") and vProgram = "Outlook"){
		Code := "Try{`n`toOutlook := ComObjActive(""Outlook.Application"") `; Create a connection to an Outlook Application object`n}`nCatch{`n`toOutlook := ComObjCreate(""Outlook.Application"") `; Create an Outlook Application object`n`oOutlook.Visible := true`n}`n`n" Code
	}
	Return
}

StringCodeReplace(Haystack, SearchText, ReplaceText:=""){
	ReplacedStr := ""
	StrReplace(Haystack, """", , OutputVarCount)
	if (OutputVarCount=0){
		return StrReplace(Haystack, SearchText, ReplaceText)
	}
	Loop, Parse, Haystack, `"
	{
		if (mod(A_Index, 2) = 1){
			ReplacedStr .= StrReplace(A_LoopField, SearchText, ReplaceText)
		}
		else{
			ReplacedStr .= A_LoopField
		}
		if (A_index!=OutputVarCount+1){
			ReplacedStr .= """"
		}
	}
	return ReplacedStr
}

VBAtoAHK_GetExcelWindows(){
	global
	oExcel := excel_get()
	if IsObject(oExcel){
		list := "Excel.exe`n"
		Loop % oExcel.Workbooks.count {
			;~ MsgBox, % xl.Workbooks(a_index).name
			list .= "`t" oExcel.Workbooks(a_index).name "`n"
			FileName := oExcel.Workbooks(a_index).name
			For cmpComponent In oExcel.Workbooks(a_index).VBProject.VBComponents
			{
				szFileName := cmpComponent.Name
				list .= "`t`t" szFileName "`n"
				; Read contents
				LinesContent := cmpComponent.CodeModule.Lines(1,cmpComponent.CodeModule.CountOfLines)
				arrMacro["Excel.exe\" FileName "\" szFileName] := LinesContent
				
				LinesContent0 := StrReplace(LinesContent, "`r", "")
				loop, 
				{
					CodeContent := RegexReplace(LinesContent0, "(^.*?Sub\s[^\(]*?\(.*?End Sub).*", "$1", RegexCount)
					Name_Macro := RegexReplace(CodeContent, "^.*?Sub ([^\(\s]*?)\(.*", "$1")
					LinesContent0 := RegexReplace(LinesContent0, "^.*?Sub ([^\(\s]*?\(.*?End Sub)(.*)$", "$2")
					if (RegexCount=0){
						Break
					}
					list .= "`t`t`t" Name_Macro "`n"
					arrMacro["Excel.exe\" FileName "\" szFileName "\" Name_Macro] := CodeContent
				}
			}	
		}
	}
	oWord := ComObjActive("Word.Application")
	if IsObject(oWord){
		list .= "Word.exe`n"
		Loop % oWord.documents.count {
			oDocument := oWord.documents(A_Index)
			
			list .= "`t" oDocument.name "`n"
			FileName := oWord.documents(A_Index).name
			
			For cmpComponent In oWord.documents(A_Index).VBProject.VBComponents
			{
				szFileName := cmpComponent.Name
				list .= "`t`t" szFileName "`n"
				; Read contents
				LinesContent := ""
				try LinesContent := cmpComponent.CodeModule.Lines(1,cmpComponent.CodeModule.CountOfLines)
				if (LinesContent =""){
					LinesContent := "Does not work, seems to work differently than Excel, sorry.`n`nSuggestions are welcome..."
				}
				arrMacro["Word.exe\" FileName "\" szFileName] := LinesContent
				
				LinesContent0 := StrReplace(LinesContent, "`r", "")
				loop, 
				{
					CodeContent := RegexReplace(LinesContent0, "(^.*?Sub\s[^\(]*?\(.*?End Sub).*", "$1", RegexCount)
					Name_Macro := RegexReplace(CodeContent, "^.*?Sub ([^\(\s]*?)\(.*", "$1")
					LinesContent0 := RegexReplace(LinesContent0, "^.*?Sub ([^\(\s]*?\(.*?End Sub)(.*)$", "$2")
					if (RegexCount=0){
						Break
					}
					list .= "`t`t`t" Name_Macro "`n"
					arrMacro["Word.exe\" FileName "\" szFileName "\" Name_Macro] := CodeContent
				}
			}	
		}
	}
	
	Gui, 2:New, +HwndhGui +Resize
	
	ImageListID := IL_Create(10)
	TreeViewList =
	
	IL_Add(ImageListID, "shell32.dll", "5") ; folder
	IL_Add(ImageListID, "shell32.dll", "3")
	
	Gui 2: Add, TreeView, xm w250 h400 0x400 vvMacroExplTree ggMacroExplTree hwndHTV ImageList%ImageListID% ; AltSubmit		;0x400 single expand, 0x200 hot tracking 
	Ar_Tree := CreateTreeView(list)
	Gui 2: Add, Edit, x+10 yp w400 h400 vvMacroContent, 
	Gui 2: Add, Button, gg2Translate, Translate 
	ItemID := 0  ; Causes the loop's first iteration to start the search at the top of the tree.
	Loop
	{
		ItemID := TV_GetNext(ItemID, "Full")  ; Replace "Full" with "Checked" to find all checkmarked items.
		if not ItemID  ; No more items in tree.
			break
		ParentID := TV_GetParent(ItemID)
		
		if (ParentID = "0"){
			TV_Modify(ItemID ,"Expand")
		}
	}
	
	Gui, 2:Add, StatusBar, , 
	Gui, 2:Show, , Macro Explorer
	
	GuiName := A_DefaultGui 
	return
	
	gMacroExplTree:
	Gui +OwnDialogs
	
	SelectedItemID := TV_GetSelection()
	TV_GetText(ItemText, SelectedItemID)
	
	if (A_GuiEvent = "DoubleClick"){
		TV_GetText(DoubleClickedItemText, A_EventInfo)
		TV_GetText(SelectedText, A_EventInfo)
		ItemID := TV_GetSelection()
		Path := ItemText
		ParentID := TV_GetParent(ItemID)
		Loop, {
			TV_GetText(ParentText, ParentID)
			if (ParentText =""){
				break
			}
			Path := ParentText "\" Path
			ParentID := TV_GetParent(ParentID)
		}
		GuiControl, , vMacroContent, % arrMacro[Path]
	}
	return
	g2Translate:
	Gui, 2:Submit, NoHide
	Gui, 1:Default
	Guicontrol, Text, vVBA_Code, % vMacroContent
	GoSub gVBA_Code
	return
}
	
	Excel_Get(WinTitle="ahk_class XLMAIN") {	; by Sean and Jethrow, minor modification by Learning one
		ControlGet, hwnd, hwnd, , Excel71, %WinTitle%
		if !hwnd
			return
		Window := Acc_ObjectFromWindow(hwnd, -16)
		Loop
			try
				Application := Window.Application
		catch
			ControlSend, Excel71, {esc}, %WinTitle%
		Until !!Application
		return Application
	}	; http://www.autohotkey.com/forum/viewtopic.php?p=492448#492448
	
	;---------------------------------------------------------------------------------------------------------
	;CreateTreeView function
	;adding auto "sort" items ---append two instances of match3 with " Sort"
	;---------------------------------------------------------------------------------------------------------
	CreateTreeView(TreeViewDefinitionString) {	; by Learning one
		Ar_Tree := {}
		IDs := {}
		Loop, parse, TreeViewDefinitionString, `n, `r
		{
			if A_LoopField is space
				continue
			Item := RegExReplace(A_LoopField,"AD)(\t*[^\t]+)\t.+","$1",Count)
			
			Item := RTrim(Item, A_Space A_Tab), Item := LTrim(Item, A_Space), Level := 0
			While (SubStr(Item,1,1) = A_Tab)
				Level += 1,	Item := SubStr(Item, 2)
			RegExMatch(Item, "([^`t]*)([`t]*)([^`t]*)", match)	; match1 = ItemName, match3 = Options
			if (Level=0){
				;~ ItemID := TV_Add(match1, 0, match3 "Icon2" )
				ItemID := TV2_Add(match1, 0, match3)
				IDs["Level0"] := ItemID
			}
			else{
				ItemID := TV2_Add(match1, IDs["Level" Level-1], match3)
				;~ if !InStr(match1,"."){
				;~ ItemID := TV_Add(match1, IDs["Level" Level-1], match3 "Icon2 Sort")
				;~ }
				IDs["Level" Level] := ItemID
			}
			Ar_Tree[ItemID]	:= A_LoopField
			
		}
		return Ar_Tree
	}	; http://www.autohotkey.com/board/topic/92863-function-createtreeview/
	
	TV2_Add(ItemName, ParentID="", Options=""){
		if !InStr(ItemName,"."){
			ItemID := TV_Add(ItemName, ParentID, "Icon2 " Options)
		}
		else{
			ItemID := TV_Add(ItemName, ParentID, " " Options)
		}
		return ItemID
	}

Last edited by AHK_user on 28 Oct 2021, 12:01, edited 6 times in total.

User avatar
Joe Glines
Posts: 770
Joined: 30 Sep 2013, 20:49
Location: Dallas
Contact:

Re: VBA to AHK Translator (Excel)

Post by Joe Glines » 17 Nov 2020, 20:30

Very cool! Thanks for sharing! If y'all want to see a video of a working example, this was shared in the second hour of November's 2020 AutoHotkey webinar. https://www.the-automator.com/autohotkey-webinar-ahk-mentorship-user-survey/
Sign-up for the 🅰️HK Newsletter

ImageImageImageImage:clap:
AHK Tutorials:Web Scraping | | Webservice APIs | AHK and Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
YouTube

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!

User avatar
SirSocks
Posts: 360
Joined: 26 Oct 2018, 08:14

Re: VBA to AHK Translator (Excel)

Post by SirSocks » 17 Nov 2020, 22:19

Wow, this looks great! I'll try to test this out and provide some feedback.

daywalker
Posts: 32
Joined: 18 Jun 2019, 01:37

Re: VBA to AHK Translator (Excel)

Post by daywalker » 18 Nov 2020, 01:16

Nice work, please add also the mso constants like msoTrue

Tre4shunter
Posts: 139
Joined: 26 Jan 2016, 16:05

Re: VBA to AHK Translator (Excel)

Post by Tre4shunter » 18 Nov 2020, 10:51

Very nice!

Excel VBA to AHK is what really got me started into ahk - this would have been amazing! Though what i really like is that list of constants :shock:

Nice work!

User avatar
TheDewd
Posts: 1508
Joined: 19 Dec 2013, 11:16
Location: USA

Re: VBA to AHK Translator (Excel)

Post by TheDewd » 18 Nov 2020, 13:48

Wow! That's amazing! I performed several actions, while recording, and it replicated them perfectly!

AHK_user
Posts: 515
Joined: 04 Dec 2015, 14:52
Location: Belgium

Re: VBA to AHK Translator (Excel)

Post by AHK_user » 18 Nov 2020, 17:01

Thanks for the replies.
daywalker wrote:
18 Nov 2020, 01:16
Nice work, please add also the mso constants like msoTrue
=> This is added to the file constants.txt.

I could not find a clean list of the mso constants, so I just added the following:
- msoCTrue = 1
- msoFalse = 0
- msoTriStateMixed = -2
- msoTriStateToggle = -3
- msoTrue = -1

If you encounter a full list of these constants, we can add them all at once.

burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: VBA to AHK Translator (Excel)

Post by burque505 » 19 Nov 2020, 11:28

Thanks very much, @AHK_user, great stuff. Perhaps this thread might be useful at some point, and jeeswg's list of all Office constants on GitHub.
Regards,
burque505

hasantr
Posts: 933
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: VBA to AHK Translator (Excel)

Post by hasantr » 20 Nov 2020, 01:33

That's what I've always needed. Thanks a lot.

AHK_user
Posts: 515
Joined: 04 Dec 2015, 14:52
Location: Belgium

Re: VBA to AHK Translator (Excel)

Post by AHK_user » 22 Nov 2020, 06:47

burque505 wrote:
19 Nov 2020, 11:28
Thanks very much, @AHK_user, great stuff. Perhaps this thread might be useful at some point, and jeeswg's list of all Office constants on GitHub.
Regards,
burque505
Thanks, I have added the other MS constants, so normally every constant should work now.

Other updates:
- Moved Constants.txt file to Github (Became to large for this forum)
- Added Word translation
- Added Macro explorer (Current only working for Excel, Word seems to work differently, I'm failing to read the VBA code)
- Some translations improvements

Ahk_fan
Posts: 237
Joined: 31 Aug 2018, 14:34
Contact:

Re: VBA to AHK Translator (Excel)

Post by Ahk_fan » 26 Nov 2020, 07:19

Very helpfull! Thank you
regards,
AHK_fan :)
https://hr-anwendungen.de

AHKStudent
Posts: 1472
Joined: 05 May 2018, 12:23

Re: VBA to AHK Translator (Excel)

Post by AHKStudent » 30 Nov 2020, 14:47

wow thanks

AHK_user
Posts: 515
Joined: 04 Dec 2015, 14:52
Location: Belgium

Re: VBA to AHK Translator (Excel)

Post by AHK_user » 08 Dec 2020, 14:22

Update:
- Added the Acc.ahk library to the github files.
- The constants.txt file will be automatically downloaded if not present.
- Macro's will convert the function with the global option.

This update is made based on the feedback of Joe Glines, thanks.
https://www.youtube.com/watch?v=egaIZ9CB4LI&ab_channel=JoeGlines

burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: VBA to AHK Translator (Excel)

Post by burque505 » 27 Oct 2021, 13:14

@AHK_user, thanks again for this! I wanted to report that I was able to convert a troublesome VBA macro for inserting a Word document in (not at) a bookmark. The only change I had to make (a single line) was

Code: Select all

wd.ActiveDocument.Bookmarks.Add "bmTarget", oRngNC
to

Code: Select all

wd.ActiveDocument.Bookmarks.Add("bmTarget", oRngNC)
Regards,
burque505

AHK_user
Posts: 515
Joined: 04 Dec 2015, 14:52
Location: Belgium

Re: VBA to AHK Translator (Excel)

Post by AHK_user » 27 Oct 2021, 17:00

burque505 wrote:
27 Oct 2021, 13:14
@AHK_user, thanks again for this! I wanted to report that I was able to convert a troublesome VBA macro for inserting a Word document in (not at) a bookmark. The only change I had to make (a single line) was

Code: Select all

wd.ActiveDocument.Bookmarks.Add "bmTarget", oRngNC
to

Code: Select all

wd.ActiveDocument.Bookmarks.Add("bmTarget", oRngNC)
Regards,
burque505
Thanks, could you give me maybe the original input and the desired output? Then I will try update it.

burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: VBA to AHK Translator (Excel)

Post by burque505 » 27 Oct 2021, 17:30

@AHK_User, absolutely! I got this from one of Greg Maxey's useful pages.
VBA Code:

Code: Select all

Sub InsertFileInBookmark()
Dim oRng As Range, oRngNC As Range
  Set oRng = ActiveDocument.Bookmarks("bmInBookmark").Range
  Set oRngNC = oRng.Characters.Last
  oRng.InsertFile ("D:\FileToInsert.docm")
  oRngNC.Characters.Last.Previous.Delete
  oRngNC.End = oRngNC.End - 1
  ActiveDocument.Bookmarks.Add "bmTarget", oRngNC
lbl_Exit:
  Exit Sub
End Sub
I didn't use or need the 'lbl_Exit' stuff. Here's the present output:

Code: Select all

InsertFileInBookmark(){
global
; Dim oRng As Range, oRngNC As Range
  oRng := oWord.ActiveDocument.Bookmarks("bmInBookmark").Range
  oRngNC := oRng.Characters.Last
  oRng.InsertFile ("D:\FileToInsert.docm")
  oRngNC.Characters.Last.Previous.Delete
  oRngNC.End := oRngNC.End - 1
  oWord.ActiveDocument.Bookmarks.Add "bmTarget", oRngNC
lbl_Exit:
  Exit Sub
return
}
So much VBA adds the parameters after the method/function with no parentheses and a space between that maybe a fix for this will be a fix for lots of other code.

As a point of reference, here's what I finally came up with.

Code: Select all

InsertFileInBookmark(bmname, filepath) {
global ; required
oRng := wd.ActiveDocument.Bookmarks(bmname).Range
oRngNC := oRng.Characters.Last ; This should be the carriage return added when the file is inserted
oRng.InsertFile(filepath)
oRngNC.Characters.Last.Previous.Delete ; get rid of the extra carriage return
oRngNC.End := oRngNC.End - 1 ; delete extraneous space at end of inserted text.
wd.ActiveDocument.Bookmarks.Add("bmTarget", oRngNC)
}
Thanks again!
Regards,
burque505

AHK_user
Posts: 515
Joined: 04 Dec 2015, 14:52
Location: Belgium

Re: VBA to AHK Translator (Excel)

Post by AHK_user » 28 Oct 2021, 12:08

I have updated the script to be able to handle it.

If you work a lot with excel, maybe you find this tutorial also interesting, it adds the regexreplace function inside excel.
Quite usefull if you handle a lot of data.
https://www.spreadsheetweb.com/how-to-use-regular-expressions-in-excel/

burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: VBA to AHK Translator (Excel)

Post by burque505 » 28 Oct 2021, 12:21

@AHK_user, that's very nice, thank you! Worked great on the VBA code I posted above. I'm looking forward to trying it out with more VBA code that I find online.
And thanks for the Excel link.
Regards,
burque505

burque505
Posts: 1731
Joined: 22 Jan 2017, 19:37

Re: VBA to AHK Translator (Excel)

Post by burque505 » 28 Oct 2021, 12:47

@AHK_user, I thought I'd share some more Greg Maxey code that doesn't translate completely.
It appears a "Public Sub" will not be processed correctly.
I assume, without having tested it, that

Code: Select all

Public Sub fInsertFields(oRng As Range, Optional strText As String)
should be

Code: Select all

fInsertFields(oRng, strText := ""){ ; and of course a another bracket at the end of the function
Spoiler
Regards,
burque505

Trisolaris
Posts: 79
Joined: 10 Mar 2019, 10:28

Re: VBA to AHK Translator (Excel)

Post by Trisolaris » 01 Dec 2021, 02:50

Hi @AHK_user ,

I get following error message when I try to run the script (AHK v1.1.33.10)

Error: Call to nonexistent function.
Specifically: Acc_ObjectFromWindow(hwnd, -16)

Relates to following line:
445: Window := Acc_ObjectFromWindow(hwnd, -16)


EDIT: nevermind, I realized I had the Acc.ahk lib in the script folder and not in the lib folder. Opted to include it via #include. Seems to work fine now.

Post Reply

Return to “Scripts and Functions (v1)”