VBA dim ws as worksheets (not worksheet)

Question

OK so, I know I can do this:

Dim ws as worksheet
Set ws = thisworkbook.worksheets("Sheet1")

and then do my fancy stuff with the ws worksheet object

I also know I can Dim wss as worksheets and that using worksheets("Sheet1") returns the worksheet object. So why doesn't the following work?

Dim wss as worksheets
Dim ws as worksheet
Set wss = thisworkbook.worksheets
Set ws = wss("Sheet1")

I've also tried:

Dim wss as worksheets
Dim ws as worksheet
Set ws = thisworkbook.wss("Sheet1")

but the latter just looks like I'm trying to rename/shorten "worksheets" which seems totally wrong. I'm trying to get the worksheets of a workbook in to one worksheets object called wss. This is more about trying to understand the heirachy than anything but for functional purposes I'm trying to get wss to encompass all worksheets from workbook x so I could just do ws = wss(1) instead of saying set ws = wb.worksheets(1)

Is that even possible or am I misunderstanding the worksheets/ worksheet relationship?

Robles

It's generally easiest to find answers to questions like this by using the Object Browser and looking at the method or property definitions. – Araroba

I had a look at the members for Sheets and Worksheets and they're both the same. The help file says Worksheets returns the worksheets collection, which I thought'd be fine since I'm not interested in charts. I just wanted to harness the workbook.worksheets collection with a worksheets object. I think I'm missing something simple so thought I'd see what this forum has on offer! I see the most creative code and/or simple explanations on here (compared to official documentation). – Robles

The documentation is misleading. If you look at the Sheets and Worksheets properties you'll see that they both return a Sheets object. – Araroba

OK that's where the confusion was. I didn't quite get why Worksheets would return a Sheets collection instead of just a Worksheets collection. Thanks for clarifying. – Robles

It's a Sheets collection of Worksheets only. I think I've had enough of VBA for today :D – Robles

Answer 1

you must declare wss as a Sheets object

Dim wss As Sheets
Dim ws As Worksheet

Set wss = ThisWorkbook.Worksheets
Set ws = wss("Sheet1")

this is because Worksheets property of Workbook object returns a Sheets collection, i.e. a collection that contains both Worksheets and Charts object of the workbook

Should you need a collection of your Workbook Worksheets only (not Charts) to be called like ws = wss(1) or the likes then you could adopt the following workaround with Collection object

Option Explicit

Sub main()
    Dim wss As Collection
    Dim ws As Worksheet

    Set wss = GetWorkSheets
    Set ws = wss("Sheet1")
    Set ws = wss(1)
End Sub

Function GetWorkSheets() As Collection
    Dim wss As New Collection
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        wss.add ws, ws.Name
    Next ws
    Set GetWorkSheets = wss
End Function

Equalize

OK that gets me closer to understanding. It is still a little confusing to me overall, so one can Set wss = ThisWorkbook.Sheets to return a Sheets collection but if you do Set wss = ThisWorkbook.Worksheets that also returns a Sheets collection? – Robles

No, that would be an invalid assignment since as per Excel Object model Worksheets property of Workbook object returns a Sheets collection. Thus Set wss = ThisWorkbook.Worksheets results in a type mismatch error – Equalize

Awesome and thank you for the edit update with the function. You guys rock. – Robles

You don't need a special function to get a collection of worksheets. Dim wss As Sheets and Set wss = ThisWorkbook.Worksheets returns a Sheets collection object that only contains the worksheets (and not charts or macro sheets). And you can use Debug.Print wss(1).Name for example. – Renounce

@DickKusleika, yes you're right and jamheadart already got that (see its last comment). I'll leave GetWorkSheets function as an example of how to build and use a customized worksheets collection – Equalize

Answer 2

Sub GenerarDibujo2D() Dim ws As Worksheet Dim i As Integer, j As Integer Dim tamañoCelda As Integer Dim maxRows As Integer, maxCols As Integer

' Configuración del dibujo
tamañoCelda = 10 ' Tamaño de cada celda (en píxeles)
maxRows = 20 ' Número de filas del dibujo
maxCols = 20 ' Número de columnas del dibujo

' Crear una nueva hoja de cálculo para el dibujo
Set ws = Worksheets.Add
ws.Name = "Dibujo 2D"

' Dibujar el cuadrado de píxeles
For i = 1 To maxRows
    For j = 1 To maxCols
        ' Cambiar el color de fondo de la celda en función de una condición
        ' (en este caso, una simple condición aleatoria)
        If Rnd() < 0.5 Then
            ws.Cells(i, j).Interior.Color = RGB(0, 0, 0) ' Negro
        Else
            ws.Cells(i, j).Interior.Color = RGB(255, 255, 255) ' Blanco
        End If

        ' Ajustar el tamaño de las celdas
        ws.Cells(i, j).ColumnWidth = tamañoCelda * 0.1
        ws.Cells(i, j).RowHeight = tamañoCelda
    Next j
Next i

' Ajustar la visualización
ws.Cells.HorizontalAlignment = xlCenter
ws.Cells.VerticalAlignment = xlCenter
ws.Cells.Font.Size = 8

End Sub

Escobar