This is possibly a bit fundamental, but it's niggling me a bit. It has done in the past, and it caught me out again recently so I thought I would chuck it out and see if anyone has any thoughts on it.
I think perhaps a simple answer might be just that there is no worksheets active cell. That might stand up in a court of law but I don’t buy it. We are left to figure things out ourselves sometimes with Microsoft, and can sometimes, even when we are not supposed to be able to…
So I think that Excel knows about an active cell in all its worksheets, because of something like this little demo…. Take the uploaded workbook, MeActiveStuff.xls, open it and run this first macro which is in it. I am suggesting that the macro "sort of" makes the active cell of the two worksheets.
Code: Select all
' first macro https://eileenslounge.com/viewtopic.php?f=30&t=40560
Sub MesActiveCell()
ThisWorkbook.Worksheets.Item(1).Activate
ActiveSheet.Range("B2").Activate ' Make the active cell in the first worksheet B2
ThisWorkbook.Worksheets.Item(2).Activate
ActiveSheet.Range("A2").Activate ' Make the active cell in the second worksheet A2
' This next code line would error moaning that the worksheet does not have an ActiveCell Method
'Debug.Print ThisWorkbook.Worksheets.Item(1).ActiveCell.Address
Debug.Print Application.ActiveCell.Address ' Returns $A$2, the address of the active cell in the second worksheet $A$2
ThisWorkbook.Save
End Sub
Code: Select all
' second macro
Sub DoYaKnowWhatYaActiveCellsR()
ThisWorkbook.Worksheets.Item(1).Activate
Debug.Print Application.ActiveCell.Address ' Returns $B$2 (Ctrl+G from VB Editor to see Immediate window)
ThisWorkbook.Worksheets.Item(2).Activate
Debug.Print Application.ActiveCell.Address ' Returns $A$2
End Sub
What niggles me a bit, only a bit, I won't lose sleep over it, but just a personal preference is that I like to be explicit in coding as it helps me more to understand and remember what's going on.
I am thinking there is no real thing as an ActiveCell. There is an Application.ActiveCell which is taken if you are in a normal code module and are a bit un explicit and use ActiveCell. In such a case, ActiveCell defaults to the Application.Activecell cell which is equivalent to the active window active cell, in other words the one you are looking at. I am OK with that. It all ties up with what I think I know about Excel VBA.
I am thinking that if I am in a worksheets code module and use ActiveCell, then to follow the pattern of things in Excel VBA it should either error or default to the active cell of the worksheet. It does not error but it appears to default to the Application.ActiveSheet
ActiveCell is that cell that is active and can be seen, if there is one…??.
I am not too happy with that explanation that ActiveCell is generally to do with a cell actively seen to be, active, if there is one. For one reason because I never saw anything written to that effect, (well not until I just did write it). It sounds nice. I might even say it to someone like my ex-Mother in Law’s Brother sometime. (He’s mad anyway.) But I don’t think it's true. (Not that my ex-Mother in Law’s Brother is mad, he is. The statement that ActiveCell is that cell that is active and can be seen, if there is one, is not true, IMHO)
For example, consider the other uploaded workbook, Mappe1.xls
Another demo, if you feel so inclined: Open it, but be careful not to do anything at all with it, - don’t click anywhere on/ in it or move it or anything. Instead, activate the existing workbook MeActiveStuff.xls or any other workbook you happen to have open and if necessary move that workbook about or resize it etc. so that you cannot even see anything of Mappe1.xls . Make sure you activate / select any cell in MeActiveStuff.xls or any other workbook but not Mappe1.xls
Now run this third macro in MeActiveStuff.xls
Code: Select all
' third macro
Sub GetLaActiveCellThatAintAndCantBeSeen()
MsgBox prompt:=Windows("Mappe1.xls").ActiveCell.Value
End Sub
_.___________-
Not a major problem, - where it cropped up currently was where I had a coding in a worksheets code module which I was trying to tidy up a bit. Some range definitions I liked to tidy up by changing like Range("x__") to Me.Range("x__") and I wanted to similarly change some ActiveCell____ to Me.ActiveCell____
I am happy for now to define a range object variable early on with something like this
Me.Activate: Dim MeActiveCell As Range: Set MeActiveCell = ActiveCell
, then use that in place of any ActiveCells (by the way I am not a fan of doing the : colon thing to put everything on the same physical line, but I do it in some cases like this where I have niggly things I maybe did not want to, or things I think I should not have to do)
Any thoughts/ opinions on this one?
Alan