excel – Convert a string sheet to an object sheet VBA

excel – Convert a string sheet to an object sheet VBA

This is one way to convert a array of strings into an array of worksheet objects:

Sub WhatsInAName()
    arr = Array(Sheet1, Sheet2, Sheet3)
    Dim oArr(1 To 3) As Worksheet
    i = 1
    For Each a In arr
        Set oArr(i) = Sheets(a)
        i = i + 1
    Next a
End Sub

Worksheets are part of a Workbook. Every workbook has a collection of worksheets that you can access either by name of by number. If you write Worksheets(Data), it accesses the worksheet Data of the current active workbook.

So the simple answer would be

Set SheCurrent = worksheets(arr(i))

But I would advice to read a little bit about how to deal with Workbooks and Worksheets in VBA, for example 10 ways to reference Excel workbooks and sheets using VBA. As a general advice, you should (nearly) never assume a specific workbook to be active.

excel – Convert a string sheet to an object sheet VBA

Leave a Reply

Your email address will not be published. Required fields are marked *