Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Waltham, Massachusetts, USA
    Thanked 0 Times in 0 Posts

    Getting cells in other worksheets (Excel / VBA 2003)

    I have a workbook with 33 sheets. I want to show row 76 of each of sheets 3 through 33 on a "summary page" on sheet 2. I can get the name of the sheets via:

    Public Function WSName(i As Integer) As String
    WSName = Worksheets(i).Name
    End Function

    but I can't find an Excel worksheet function which will take this string and allow me to access cells on a worksheet given the name of the worksheet as a string.

    So, I grabbed this from somewhere ...

    Public Function WSContents(i As Integer, r)
    WSContents = Worksheets(i).Range(r.Address).Value
    End Function

    which allows me to refer to cells on other worksheets as (e.g.) WSContents(5, D76), but I have to call this for every cell I want, which is a lot.

    This seems like an awfully inefficient hammer to crack this particular nut: once I have a few hundred of these calls on my summary sheet, recalculating after any change takes a noticeable amount of time, perhaps a few seconds. I do need the volatile, right?

    Is there a better approach??

    Graeme Williams

    p.s. As a bonus question, what's the type of the argument 'r'?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Getting cells in other worksheets (Excel / VBA 2003)

    Bonus question first: strictly speaking, r is a Variant since its type has not been declared. From the code, it becomes clear that the code expects a Range.

    You can use the INDIRECT function to process a string that evaluates to a cell address:


Posting Permissions

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