Looping through all cells in a workbook

[J. Caplan]

I have a function (call it "ProcessRange") that works on a range that is
passed into it. It is called one of two ways: 1) the currently selected
cell range is passed in or 2) all cells on the sheet are passed in using
the sheet's .UsedRange property.

I want to be able to work with all cells from ALL Sheets in the workbook. I
know I can go through each sheet in the workbook and pass in that sheet's
UsedRange, however, I would like to only have to call my "ProcessRange". For
example, I have to call my method several times

Dim sheet As Worksheet

For Each sheet In Application.ActiveWorkbook.Sheets()
Debug.Print "Calculating Sheet: " & sheet.name
Call ProcessRange(sheet.UsedRange)
Next sheet

I would like to append the ranges from all cells together into ONE dynamic
range and then call ProcessRange once. Is this possible?

 

[Dave Peterson]

Ranges can only live on one worksheet.

You could pass a bunch of ranges (specifying each sheet and range) to your
function (see vba's help for Function and look for ParamArray).

Option Explicit
Function myFunc(ParamArray myRanges() As Variant) As Boolean

Dim myElement As Variant
Dim myCell As Range

For Each myElement In myRanges
If TypeOf myElement Is Range Then
For Each myCell In myElement.Cells
MsgBox myCell.Address(external:=True)
Next myCell
End If
Next myElement

myFunc = True

End Function
Sub testme()
Dim ok As Boolean
ok = myFunc(Sheet1.Range("a1"), Sheet2.UsedRange)
End Sub

But if you really want all cells, I'd do the loop you're doing.