DocAElstein
Banned user
- Joined
- May 24, 2014
- Messages
- 1,336
'Re: As many Active Cells as You like.. At once….
'---o00o---`(_)`---o00o---
'' An / To :- MrExcel VBA Excel Forum
'' Von / From :- Alan Elston
'' Hallo,
'' My first time here.
'' The following might be obvious as I’m new to Excel
'' But I thought it was a bit interesting.
'' It came out of my first question on MrExcel ( in the
'' MrExcel Forum „ Excel Questions
'' All Excel/VBA questions“
'' Link :- http://www.mrexcel.com/forum/excel-...workbooks-windows-oop-method.html#post3818451 )
'' Firstly from Books and experienced VBA Programmers I got the
'' impression that by using the "dot" ,
'' . “you always work down the hierarchy”
'' Clearly from the answers I got that is not the case.
'' The .Application Object gives at least one way of
'' going “backwards” or "going back up the hierarchy "
'' This is because .Application can be called from all over the place,
'' that is to say at least twice on the same line, which is
'' why I had the problem which led to may first question.
'' ( …….That also explains why it crops up all over the place in the
'' Excel library!- A lot of experience VBA users told me they thought
'' that was because there were lots of them depending on where they were!
'' they were wrong!!??????????...........................)
'' Basically I wanted to access and manipulate the
'' active cell in a lot of open Workbooks without having to
'' activte the workbook every time. I thought I could
'' do this with the Application.ActiveCell
'' but in fact I was always going back up to the
' .Application ( in this case the application of Excel ). By
'' "going back up", the in-between code I wrote (which gave the
'' impression I was accessing a particular Cell in a particular
'' Workbook) was ignored/ irrelevant.
'' By "going back up" I was then using the "unique" Object
'' .Application
'' The application of Excel has only one Active Cell
'' which is always that of the activated Worksheet of a workbook.
''
'' But, and this is the point I’m trying to get across,,
'' you can get around this limitation using the
'' .Windows( ) object:-
''
'' The .Windows( ) object has an Active cell property,
'' You can use this to have as
'' many Active cells as there are open Workbooks.
'' These can be for example referenced by name
Let a = Windows("Mappe1").ActiveCell
Let b = Windows("Mappe2").ActiveCell
' or in the order that the Workbooks were opened,
Let a = Windows.Item(1).ActiveCell
Let b = Windows.Item(2).ActiveCell
'' where Item 1 is always the last workbook opened, item 2 the one before, etc.
''That’s it, sorry if I rambled on a bit but it helped me to understand
'' it all a last time!!
'' Goodbye, Aufwiedersehen
'' Alan Elston
'' Baveria
'' Germany.
'' P.s. ( Of course I could be talking a lot of rubbish,- I’ve just started
'' with VBA. Feel free to put me right!!)
'---o00o---`(_)`---o00o---
'' An / To :- MrExcel VBA Excel Forum
'' Von / From :- Alan Elston
'' Hallo,
'' My first time here.
'' The following might be obvious as I’m new to Excel
'' But I thought it was a bit interesting.
'' It came out of my first question on MrExcel ( in the
'' MrExcel Forum „ Excel Questions
'' All Excel/VBA questions“
'' Link :- http://www.mrexcel.com/forum/excel-...workbooks-windows-oop-method.html#post3818451 )
'' Firstly from Books and experienced VBA Programmers I got the
'' impression that by using the "dot" ,
'' . “you always work down the hierarchy”
'' Clearly from the answers I got that is not the case.
'' The .Application Object gives at least one way of
'' going “backwards” or "going back up the hierarchy "
'' This is because .Application can be called from all over the place,
'' that is to say at least twice on the same line, which is
'' why I had the problem which led to may first question.
'' ( …….That also explains why it crops up all over the place in the
'' Excel library!- A lot of experience VBA users told me they thought
'' that was because there were lots of them depending on where they were!
'' they were wrong!!??????????...........................)
'' Basically I wanted to access and manipulate the
'' active cell in a lot of open Workbooks without having to
'' activte the workbook every time. I thought I could
'' do this with the Application.ActiveCell
'' but in fact I was always going back up to the
' .Application ( in this case the application of Excel ). By
'' "going back up", the in-between code I wrote (which gave the
'' impression I was accessing a particular Cell in a particular
'' Workbook) was ignored/ irrelevant.
'' By "going back up" I was then using the "unique" Object
'' .Application
'' The application of Excel has only one Active Cell
'' which is always that of the activated Worksheet of a workbook.
''
'' But, and this is the point I’m trying to get across,,
'' you can get around this limitation using the
'' .Windows( ) object:-
''
'' The .Windows( ) object has an Active cell property,
'' You can use this to have as
'' many Active cells as there are open Workbooks.
'' These can be for example referenced by name
Let a = Windows("Mappe1").ActiveCell
Let b = Windows("Mappe2").ActiveCell
' or in the order that the Workbooks were opened,
Let a = Windows.Item(1).ActiveCell
Let b = Windows.Item(2).ActiveCell
'' where Item 1 is always the last workbook opened, item 2 the one before, etc.
''That’s it, sorry if I rambled on a bit but it helped me to understand
'' it all a last time!!
'' Goodbye, Aufwiedersehen
'' Alan Elston
'' Baveria
'' Germany.
'' P.s. ( Of course I could be talking a lot of rubbish,- I’ve just started
'' with VBA. Feel free to put me right!!)