Excel: Find address of selection using COM Topic is solved

[submeg]

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

[boiler]

Code: Select all

Range_address := % ComObjActive("Excel.Application").Selection.Address("xlA1", 0, 0)

[submeg]

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?

[teadrinker]

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.

[submeg]

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
 
}

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

[boiler]

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.

[boiler]

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
 
}

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

[Datapoint]

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]

Sorry about that.

No problem.

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)

[submeg]

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!

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!

[flyingDman]

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 
}