Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Horsham, Sussex, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Avoiding Activate (Excel 2003 VBA)

    I have been trying to modify the following procedure (which works fine) to avoid the 'Worksheets().Activate lines when switching between two worksheets. Whenever I set one or both as a range object, the 'Find' line throws up errors, usually that the Find Method is not supported by the object or the object isn't valid. I'm sure there is a way but it's defeating me. Any ideas?

    The procedure:

    Sub GetShareValues()
    '
    ' Reads share prices from FTSE100 sheet for my Portfolio

    Dim rngShare As Range
    Dim rngShareFind As Range
    Dim varShareValue(1 To 3) As Variant
    Dim strShareName(1 To 3) As String
    Dim x As Integer
    Dim rng As Range

    Application.ScreenUpdating = False
    Set rngShare = ThisWorkbook.Worksheets("Portfolio").Range("A3")

    'Get names of shares into array
    For x = 1 To 3
    strShareName(x) = rngShare.Offset(x - 1, 0).Value
    Next

    Worksheets("FTSE100").Activate 'Goto FTSE sheet
    'Look for each share name on the FTSE sheet then get the current quote
    For x = 1 To 3
    Cells.Find(What:=strShareName(x), After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    'Necessary to split the offsets into two actions as the 'Name' cell
    'is merged with the B column
    Set rngShareFind = ActiveCell.Offset(2, 0) 'Down two rows
    varShareValue(x) = rngShareFind.Offset(0, 1).Value 'Col B
    Next

    Worksheets("Portfolio").Activate

    'and put the values into col B of Portfolio sheet
    For x = 1 To 3
    rngShare.Offset(x - 1, 1) = varShareValue(x)
    Next

    Set rngShare = Nothing
    Set rngShareFind = Nothing

    Application.ScreenUpdating = True

    End Sub

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Avoiding Activate (Excel 2003 VBA)

    Is it necessary trying to avoid the code. You are using screenupdating, so it sould not affect the flicker or screen updating at all. Why do you want to avoid this line???
    Regards,
    Rudi

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    Horsham, Sussex, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Activate (Excel 2003 VBA)

    Only because all the books (from which I have learned my VBA) insist that it is bad practice to do it this way. if you say, it's OK then that's fine by me.

    Regards,

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Avoiding Activate (Excel 2003 VBA)

    In my experience, you can go about writing the code to act out on a sheet without selecting it, but it is easier to select it first and write the code to act on the ranges of that sheet. It comes down to choice and how you want to approach the code. The code is easier to modify and write if refering to a sheet! Using screen updating is a joy as the screen does not need to refresh and show the changing sheets.
    In my opinion it is fine to do this.
    Regards,
    Rudi

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    Horsham, Sussex, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Activate (Excel 2003 VBA)

    OK, I'm convinced. I'll leave well alone - it works fine. Many thanks for your interest.

    Regards,

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

    Re: Avoiding Activate (Excel 2003 VBA)

    I disagree a bit here.

    It is easier to select and then use
    Range("A1")....
    To do things to cell A1 on the active sheet.
    But IMO it is always better to specifically tell VBA what sheet in what workbook it should be talking to, rather than relying on the fact that it is indeed the active sheet that needs to be addressed.

    It also makes for much better readable code when you make a habit of being complete about this.

    By using explicit object references you always know what object is being addressed, whereever you are in your code and whatever has been selected or has made the selection (unexpectantly...) change.

    A simple way is to use Object variables with a descriptive name:

    Dim oTimesheet as Worksheet
    Set oTimesheet=Thisworkbook.Worksheets("Timesheet")

    So further down in your code you can:
    <code>
    With oTimesheet
    .Range("A1").Value="Hi there!"
    .Range("B1").Value="And hi again."
    End With
    </code>

    (note the dots before Range in this example)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Activate (Excel 2003 VBA)

    I agree with your book that says it is better to not activate a worksheet if it is not necessary. Activating and selecting are always slow and it is better not to use them if not necessary. Without your workbook, it is a little difficult to figure out exactly what you are doing here. I know that this code could be made more efficient if I did understand exactly what it is doing, however, I was able to get the code below to work and it does not do the sheet activations. Maybe it will get you started in the right direction.

    <pre>Sub GetShareValues()
    '
    ' Reads share prices from FTSE100 sheet for my Portfolio

    Dim rngShare As Range
    Dim rngShareFind As Range, rngFTSE As Range
    Dim varShareValue(1 To 3) As Variant
    Dim strShareName(1 To 3) As String
    Dim x As Integer
    Dim rng As Range

    Application.ScreenUpdating = False
    Set rngShare = ThisWorkbook.Worksheets("Portfolio").Range("A3")

    'Get names of shares into array
    For x = 1 To 3
    strShareName(x) = rngShare.Offset(x - 1, 0).Value
    Next x

    Set rngFTSE = Worksheets("FTSE100").Range("A1")
    'Look for each share name on the FTSE sheet then get the current quote
    For x = 1 To 3
    Set rngFTSE = Worksheets("FTSE100").Cells.Find(What:=strShareNam e(x), After:=rngFTSE, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    'Necessary to split the offsets into two actions as the 'Name' cell
    'is merged with the B column
    Set rngShareFind = rngFTSE.Offset(2, 0) 'Down two rows
    varShareValue(x) = rngShareFind.Offset(0, 1).Value 'Col B
    Next x


    'and put the values into col B of Portfolio sheet
    For x = 1 To 3
    rngShare.Offset(x - 1, 1) = varShareValue(x)
    Next

    Set rngShare = Nothing
    Set rngShareFind = Nothing

    Application.ScreenUpdating = True

    End Sub
    </pre>

    Legare Coleman

  8. #8
    New Lounger
    Join Date
    Sep 2002
    Location
    Horsham, Sussex, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Activate (Excel 2003 VBA)

    Many thanks for the tip. I can see the way to go now.

    All the procedure does is to find three companies' names as quoted on the London FTSE100 share index (which I have downloaded into a separate worksheet) then it copies their latest share prices (using the offsets), pastes them into my portfolio and I find out if I'm rich or continue to be poor.

    Many thanks again for your interest and help.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Activate (Excel 2003 VBA)

    Well, like I said, the code can be made more efficient. For example, there is no need to copy the stock symbols (I assume those are symbols) into the array in the VBA routine. However, I would not want to start making changes like that without seeing the worksheets.
    Legare Coleman

  10. #10
    New Lounger
    Join Date
    Sep 2002
    Location
    Horsham, Sussex, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Activate (Excel 2003 VBA)

    I have tried to attach a sawn-off version of my workbook (cut down to 256Kb) but for some reason, it won't go through. If you are interested I will try again.

    Tahnk you for your help..

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

    Re: Avoiding Activate (Excel 2003 VBA)

    There is a limit of 100 KB for attachments. Have you tried zipping the workbook?

  12. #12
    New Lounger
    Join Date
    Sep 2002
    Location
    Horsham, Sussex, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Activate (Excel 2003 VBA)

    Why did I read 100Kb as 1000Kb? Thanks for pointing it out. The cut-down file is attached. The FTSE100 worksheet is updated by a query (IQY) from http:/uk.finance.yahoo.com and the procedure called from the button on the Portfolio sheet 'Finds' in turn the cells containing the three named comanies on the FTSE100 sheet and gets their latest share price from that cell's offset (2,1) and copies the results to the relevant row in the Price column back in the Portfolio sheet. Excel's worksheet formulae do the rest.

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Activate (Excel 2003 VBA)

    I believe that the code below will do what you want a little more efficiently, and if you add more stocks it will automatically get them also:

    <pre>Option Explicit

    Sub GetShareValues()
    Dim oCell As Range, oFTSE As Range
    For Each oCell In Range(Worksheets("Portfolio").Range("A3"), Worksheets("Portfolio").Range("A65536").End(xlUp))
    Set oFTSE = Worksheets("FTSE100").Cells.Find(What:=oCell.Value , LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Offset(2, 0)
    oCell.Offset(0, 1).Value = oFTSE.Offset(0, 1).Value
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  14. #14
    New Lounger
    Join Date
    Sep 2002
    Location
    Horsham, Sussex, England
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Avoiding Activate (Excel 2003 VBA)

    Legare,

    It works brilliantly. A lesson learned indeed. Thank you for your help.

Posting Permissions

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