Excel: Find address of selection using COM Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
User avatar
submeg
Posts: 139
Joined: 14 Apr 2017, 20:39
GitHub: submeg

Excel: Find address of selection using COM

26 Jul 2020, 04:54

Hello all,

Over the years, I've built up my PERSONAL.XLSB at work so that I can use subroutines over and over in various workbooks without having to re-code them each time. However, now that I am working remote, I don't have access to my workbook, and so I'm looking to create a more permanent solution by rebuilding my VBA using COM. In many of my scripts, I reference either the active cell or the active selection.

I've found the COM equivalent to pull the address of the activecell so it can be used later:

Code: Select all

Cell_address := % ComObjActive("Excel.Application").ActiveCell.Address[1,1]
But I'm struggling to determine how to do the same for a selected range of cells?

The VBA equivalent is:

Code: Select all

Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)
Any ideas on how to convert this to COM so I can use it in AHK?

submeg
____________________________________
Check out my site, submeg.com
Connect with me on LinkedIn
Courses on AutoHotkey :ugeek:
User avatar
boiler
Posts: 6868
Joined: 21 Dec 2014, 02:44

Re: Excel: Find address of selection using COM

26 Jul 2020, 06:22

Code: Select all

Range_address := % ComObjActive("Excel.Application").Selection.Address("xlA1", 0, 0)
User avatar
submeg
Posts: 139
Joined: 14 Apr 2017, 20:39
GitHub: submeg

Re: Excel: Find address of selection using COM

26 Jul 2020, 06:29

boiler wrote:
26 Jul 2020, 06:22

Code: Select all

Range_address := % ComObjActive("Excel.Application").Selection.Address("xlA1", 0, 0)
Hi @boiler, that syntax works, but it returns RxC[y]:RcC[d], where x, y, c and d are the numbers (as per cells formatting) rather than in the format A1:C4?
____________________________________
Check out my site, submeg.com
Connect with me on LinkedIn
Courses on AutoHotkey :ugeek:
teadrinker
Posts: 2182
Joined: 29 Mar 2015, 09:41
Contact:

Re: Excel: Find address of selection using COM  Topic is solved

26 Jul 2020, 07:23

Code: Select all

MsgBox, % ComObjActive("Excel.Application").Selection.Address(0, 0)
Guys, don't use := % to assign a value to a variable, it looks weird. Use var := value.
User avatar
submeg
Posts: 139
Joined: 14 Apr 2017, 20:39
GitHub: submeg

Re: Excel: Find address of selection using COM

26 Jul 2020, 07:55

Thanks @teadrinker, you got it. COM can be touchy; I thought it would need the "xlA1", but I guess this is the default, so isn't needed?

Some background:

I've been going through @Joe Glines' posts, and there was one that talks about setting the horizontal and vertical alignment. The only issue I had was that you always had to define the range first, when I would rather have it so I can select a cell(s), and it will apply it to whatever is selected. The code is as per below:

Code: Select all

;-------------------------------------------------------------

Pause & z::

XL_Handle(XL_App,1)		;Connect to the active instance of Excel.

;These work
;Cell_address = % XL_App.ActiveCell.Address[1,1]
;Cell_value = % XL_App.ActiveCell.value

Cell_address := % ComObjActive("Excel.Application").ActiveCell.Address[1,1]
Range_address := % ComObjActive("Excel.Application").Selection.Address(0, 0)

;;DEBUG
;msgbox % Cell_address
;msgbox % Range_address

ExcelActions_HAlign(XL_App, RG:=Range_address, h:=2)

Return 

;-------------------------------------------------------------

I modified Joe's XL_Format_HAlign so that it will take whatever cell(s) are selected, instead of having to define the range first:

Code: Select all

;-------------------------------------------------------------

ExcelActions_HAlign(ByRef XL, RG="", h="2"){

Cell_address = % XL.ActiveCell.Address[1,1]
Range_address := % ComObjActive("Excel.Application").Selection.Address(0, 0)	;This works for one or more cells

IfEqual,h,1,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4131  ;Left
IfEqual,h,2,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4108  ;Centre
IfEqual,h,3,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4152  ;Right
 
}

;-------------------------------------------------------------
____________________________________
Check out my site, submeg.com
Connect with me on LinkedIn
Courses on AutoHotkey :ugeek:
User avatar
boiler
Posts: 6868
Joined: 21 Dec 2014, 02:44

Re: Excel: Find address of selection using COM

26 Jul 2020, 08:17

teadrinker wrote:
26 Jul 2020, 07:23

Code: Select all

MsgBox, % ComObjActive("Excel.Application").Selection.Address(0, 0)
Guys, don't use := % to assign a value to a variable, it looks weird. Use var := value.
I was using a MsgBox, so it was sloppy editing on my part when I pasted it in here and changed it to an assignment and didn’t remove the %. Thanks for catching it. Sorry about that.
User avatar
boiler
Posts: 6868
Joined: 21 Dec 2014, 02:44

Re: Excel: Find address of selection using COM

26 Jul 2020, 08:35

See the comments in the code below (one correction as reminded by teadrinker):

Code: Select all

;-------------------------------------------------------------

Pause & z::

XL_Handle(XL_App,1)		;Connect to the active instance of Excel.

;These work
;Cell_address = % XL_App.ActiveCell.Address[1,1] ; use := and don’t use %
;Cell_value = % XL_App.ActiveCell.value ; use := and don’t use %

Cell_address := % ComObjActive("Excel.Application").ActiveCell.Address[1,1] ; don’t use %
Range_address := % ComObjActive("Excel.Application").Selection.Address(0, 0) ; don’t use %

;;DEBUG
;msgbox % Cell_address
;msgbox % Range_address

ExcelActions_HAlign(XL_App, RG:=Range_address, h:=2) ; don’t assign parameters to variables
; it looks like you think it is necessary because they are named parameters but they’re not
; in the function definition, that is how default values are defined
; all you are doing here is assigning them to new global variables and then not using them
; should be: ExcelActions_HAlign(XL_App, Range_address, 2)

Return 

;-------------------------------------------------------------

And also remove the % preceding assignment values here as well:

Code: Select all

;-------------------------------------------------------------

ExcelActions_HAlign(ByRef XL, RG="", h="2"){

Cell_address = % XL.ActiveCell.Address[1,1]
Range_address := % ComObjActive("Excel.Application").Selection.Address(0, 0)	;This works for one or more cells

IfEqual,h,1,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4131  ;Left
IfEqual,h,2,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4108  ;Centre
IfEqual,h,3,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4152  ;Right
 
}

;-------------------------------------------------------------
User avatar
Datapoint
Posts: 76
Joined: 18 Mar 2018, 17:06

Re: Excel: Find address of selection using COM

26 Jul 2020, 08:40

submeg wrote:
26 Jul 2020, 07:55
I modified Joe's XL_Format_HAlign so that it will take whatever cell(s) are selected, instead of having to define the range first:

Code: Select all

;-------------------------------------------------------------

ExcelActions_HAlign(ByRef XL, RG="", h="2"){

Cell_address = % XL.ActiveCell.Address[1,1]
Range_address := % ComObjActive("Excel.Application").Selection.Address(0, 0)	;This works for one or more cells

IfEqual,h,1,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4131  ;Left
IfEqual,h,2,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4108  ;Centre
IfEqual,h,3,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4152  ;Right
 
}

;-------------------------------------------------------------
I think it could be simplified to this, but untested:

Code: Select all

ExcelActions_HAlign(h=2){
    static XlHAlign := [xlHAlignLeft := -4131               ; 1 Left
                      , xlHAlignCenter := -4108             ; 2 Center
                      , xlHAlignRight := -4152              ; 3 Right
                      , xlHAlignCenterAcrossSelection := 7  ; 4 Center across selection
                      , xlHAlignDistributed := -4117        ; 5 Distribute
                      , xlHAlignFill := 5                   ; 6 Fill
                      , xlHAlignGeneral := 1                ; 7 Align according to data type
                      , xlHAlignJustify := -4130]           ; 8 Justify
    ComObjActive("Excel.Application").Selection.HorizontalAlignment := XlHAlign[h]
}
teadrinker
Posts: 2182
Joined: 29 Mar 2015, 09:41
Contact:

Re: Excel: Find address of selection using COM

26 Jul 2020, 09:15

boiler wrote: Sorry about that.
No problem. :)
submeg wrote: I thought it would need the "xlA1", but I guess this is the default, so isn't needed?
According to this topic it's the default. However, if you want to specify it explicity, xlA1 is not a string, it's a constant. So it could be MsgBox, % ComObjActive("Excel.Application").Selection.Address(0, 0, xlA1 := 1)
User avatar
submeg
Posts: 139
Joined: 14 Apr 2017, 20:39
GitHub: submeg

Re: Excel: Find address of selection using COM

26 Jul 2020, 15:08

teadrinker wrote:
26 Jul 2020, 09:15
According to this topic it's the default. However, if you want to specify it explicitly, xlA1 is not a string, it's a constant. So it could be MsgBox, % ComObjActive("Excel.Application").Selection.Address(0, 0, xlA1 := 1)
Of course, has to be different, doesn't it! :roll:
remove the % preceding assignment values
Thanks @boiler and @teadrinker; I went and updated the code, removing the % and working 100%!

Thank you all for the input, helps me wrap my head around it. Now I can update all the other scripts that I would prefer to use the selected range with!
____________________________________
Check out my site, submeg.com
Connect with me on LinkedIn
Courses on AutoHotkey :ugeek:
User avatar
flyingDman
Posts: 883
Joined: 29 Sep 2013, 19:01

Re: Excel: Find address of selection using COM

26 Jul 2020, 17:50

If you ended up with this:

Code: Select all

ExcelActions_HAlign(ByRef XL, RG="", h="2"){

Cell_address := XL.ActiveCell.Address[1,1]
Range_address := ComObjActive("Excel.Application").Selection.Address(0, 0)	;This works for one or more cells

IfEqual,h,1,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4131  ;Left
IfEqual,h,2,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4108  ;Centre
IfEqual,h,3,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4152  ;Right
 
}
You still have a clunky script with deprecated code (see https://www.autohotkey.com/docs/commands/IfEqual.htm). RG should not be part of it. I believe Address[1,1] is the same as Address, the structure IfEqual,h,1,Return, XL.Application.ActiveSheet.Range(Range_address).HorizontalAlignment:=-4131 ;Left is not pretty, ifequal is deprecated, and you do not need to use the address(just use Selection). @Datapoint provides a much more elegant solution.
If you want to keep the Byref Xl and limit your choices to left, middle and right. you can write it like this:

Code: Select all

ExcelActions_HAlign(ByRef XL, h="2"){
XL.selection.HorizontalAlignment := [-4131,-4108,-4152][h]          ;4131 Left;-4108 Center;-4152 Right 
}

Return to “Ask For Help”

Who is online

Users browsing this forum: lwalls, Plikis, ToJaRedi, vmech, Xeo786, XMCQCX and 73 guests