Results 1 to 13 of 13
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA - Why Activate? (Excel97 and on?)

    I've been asked to discuss "Activate" in Excel VBA. I can describe what it is, what it does etc, but for the life of me I can't find a GOOD reason for using it EXCEPT to bring part of the worksheet to the attention of the end-user.

    The little sub below contains three sample chunks of code to populate a cell in a named workbook (the first two test essays are commented out).

    I wanted to demonstrate that one doesn't need Activate to perform operations on workbooks.

    I think, in theory, an Excel VBA application could run without ever consciously Activate-ing an element such as a Workbook, a WorkSheet, A cell or range of cells. etc.



    II feel a bit like I felt in WordVBA a couple of years back when I discovered that "selection" wasn't all it seemed cracked up to be, and that my dependence on Selection was based on examination of crudely-recorded macros i.e. Poor Coding.

    I'm beginning to think that the prescence of .Activate in my code will indicate a weakness on my aprt.

    <pre>Sub TESTActivate()
    ' Windows("erase1.xls").Activate
    ' Application.Goto Reference:="cellinErase1"
    ' ActiveCell.FormulaR1C1 = 44

    ' Application.Goto Workbooks("erase1.xls").Worksheets("sheet1").Range ("cellinErase1")

    Workbooks("erase1.xls").Worksheets("sheet1").Range ("cellinErase1") = 44
    Workbooks("erase2.xls").Worksheets("sheet1").Range ("cellinErase2") = 55
    End Sub
    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    In general, it is advisable NOT to use Activate and/or Select in order to manipulate workbooks. If you need to switch back and forth between workbooks, worksheets and ranges, you can define object variables to refer to them. Code without Activate and Select runs much faster.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    Hi CHris,

    <<I think, in theory, an Excel VBA application could run without ever consciously Activate-ing an element such as a Workbook, a WorkSheet, A cell or range of cells. etc.>>

    There are examples that selecting is actually necessary, but in 99 % of all cases, one can (and should) do without.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    The only reason I can think for Activating a worksheet is if you want to call a procedure that operates on the ActiveWorksheet.

    StuartR

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    Hans, thanks for the reply. I need that confirmation of my values.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    Jan, thanks for the reply. At times I think i must be going crazy when is ee so much .Activate in legacy code and realise I don't know what it is, or why it is there.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    Sturat, thanks for the response. You have confirmed my beliefe that .Activate is weak for manipulation of data. I'd like to explore further your statement about procedures.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    Just to complete the picture, I have pasted below my response to my client after reading the three responses to my original question:


    I mulled over this all weekend, because I figured that you wouldn't like my short answer.

    Here is the short answer "You don't need it and shouldn't be using it".

    .Activate belongs in a worse place than the "A3:B5" method of addressing.

    The proliferation of code using .Activate is almost certainly due to novice coders, such as you and I might, recording a macro and then poring over the entrails.

    MS-generated code is notoriously bad (in some cases close to wrong!) and I use entrails now only to get a quick idea of what Methods and Properties are available.

    The recorded code is so prolifigate that it's faster to record a macro and inspect the code than to read the help files.

    Try recording, in MSWord, "View, Header" (see example Macro1 at the foot of this message)

    Anyway, late last night I made three little samples of code using .Activate, and then rewrote them "properly". I still didn't have a reason for using .Activate.

    (snip)

    I can include a use of .Activate in a course, but to do so is to lead novices down a dead-end path of
    poor productivity and into a spiral of time-consuming poor practices to counter the initial poor practice.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    I thought that there are a few methods and properties that can only be used or obtained at the ActiveWindow, ActiveWorkBook or ActiveSheet levels, and therefore Activate is occasionally necessary. Maybe someone can show me how to do this without .Activate:

    Sub FreezePaneAcrossSheets()
    Application.ScreenUpdating = False
    Dim shtSheet As Worksheet
    Dim shtActive As Worksheet
    If ActiveCell.Address = "$A$1" Then
    Beep
    Else
    Set shtActive = ActiveSheet
    For Each shtSheet In ActiveWindow.SelectedSheets
    shtSheet.Activate
    If shtSheet.ProtectContents = False _
    And TypeName(Selection) = "Range" Then
    'If ActiveWindow.Split = True Then ActiveWindow.Split = False
    If ActiveWindow.FreezePanes = True Then
    ActiveWindow.FreezePanes = False
    ActiveWindow.Split = False
    Else
    ActiveWindow.FreezePanes = True
    End If
    End If
    Next shtSheet
    shtActive.Activate
    End If
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    This routine depends on the selected range in each of the selected sheets. To find out what the selected range is on a particular sheet, you must activate it, for Selection and ActiveCell only apply to the currently active window. So I think this is one of the exceptions.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    "Activate/Select method considered harmful."
    http://www.wopr.com/cgi-bin/w3t/showflat.p...&o=0&fpart=&vc=


    >and therefore Activate is occasionally necessary

    I'd agree, it IS occasionally necessary, but perhaps only when we (programmers) need to draw the user's attention to an event.

    As an example: My application records transactions to a log file. Only whern the run is ended do I need to launch NotePad.EXE and expose the log of events to the user. We wouldn't dream of firing up Notepad in the user's face at the end of each transaction, just to demonstrate that a new line has been added.

    I think likewise with (say) a spreadsheet: we would populate the sheet with data without Activate-ing the sheet, but when we were finished populating the sheet, we would then Activate the sheet to announce to the User "We're all done, here; how does it look?".


    >Activate/Select method considered harmful.

    I'll print this out and take it with me to my lunch date (grin!)

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    To your original point:

    >one doesn't need Activate to perform operations on workbooks

    Yep

    >only when we (programmers) need to draw the user's attention to an event.

    to which I would add:

    "or the programmer wishes to control or vary the appearance of a window which the user may view".
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA - Why Activate? (Excel97 and on?)

    > I thought that there are a few methods and properties that can only be used or obtained at the ActiveWindow, ActiveWorkBook or ActiveSheet levels,

    After playing with it for a very short time, I'm inclined to agree. This is one of those 1-in-99 cases.

    The Help files seem to indicate that FreezePanes can only apply to a Window, and also that there is but one Window in a six-sheet workbook, therefore we have to "slide" a sheet right beneath the nose of the FreezePanes method.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •