Creating a Custom Worksheet Object

[ExcelMonkey]

I know I can iterate through a worksheet collection in Excel. For Example I
can loop through the formulas in a given sheet as follows below. This
collection is already provided by Excel.

For each sht in Thisworkbook.Worksheets
For Each rng1 In sht.UsedRange.SpecialCells(xlFormulas)

Next
Next

1) Is it possible to create custom properties to add to the SpecialCells
property using a class module (i.e. create a custom worksheet class with
custom properties)?

2) In order to create these properties I will have to loop through the
worksheet to build the custom collection (asuming I cannot use a dictionary
object). This can be time consuming depending on what it is you want the
collection to look like. Would it be quicker to build this custom worksheet
Object and its custom collection via C++ using a dll? Or are you forced to
do this within Excel via VBA?

Thanks

EM

 

[JLGWhiz]

See if Chip's blurb helps any:

http://www.cpearson.com/excel/docprop.aspx

 

[Peter T]

I don't follow what you have in mind by "custom worksheet object". What do
you have and what's the overall objective.

Regards,
Peter T

 

[ExcelMonkey]

I am thinking out loud at this point Peter. The title of my post says
"Custom Worksheet Object" but I might need to be restated as "Custom Range
Object"

I know Excel has collections which I can access via code. I am looking at
the idea of having access to other collections that I have to build myself.
As per my example the SpecialCells property give you access to cells that
have formulas. Likewise it also give you access to cells as follows:

xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range. Note this XlCellType
will include empty cells that have had any of cells default format changed.
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells

But lets say I want to have constant access a collection of cells that can't
be identified using the properties above. Lets say I wanted to have a
collection showing which cells had a Fill color. Now I know I can loop
through all the cells in the spreadshet using a For Next Loop and using this
function:

Private Function CellHasColour(rg As Range)
Dim rn As Range

If Not rn.Interior.ColorIndex = xlNone Then
CellHasColour = True
End If
End Function

I could add the cell addresses of the TRUE matched to either a
collection/dictionary. I could then elsewhere in my code draw on this
collection for whatever purpose I see fit.

Dim cltn as New Collection

For each sht in Thisworkbook.Worksheets
For each rng in sht.UsedRange
If CellHasColour(rng) = True Then
cltn.Add(rng.Address)
End if
Next
Next


The question I posed was whether I could create a custom worksheet object.
So that I could create something that looked like this below. In effect I
created a custom range object with a new custom property (I think I said
custom worksheet in my original post). I would obviosly have to loop through
all the cells to create the custom collection upfront. If the definition of
the collection was based on a time consuming function this might take some
time. Or if I had multiple collectiosn that I wanted to create, this might
take time as well. So my final question was based on what would be the
quickest way to loop through an Excel file in this scenario. I know I can do
it in VBA. But is faster doing it via C++ using a DLL. I am not familiar
with C++ or dlls.

For each sht in Thisworkbook.Worksheets
For each rng in sht.SpecialCells(xlcolouredbackground) 'new property

Next
Next

Hope that makes sense. Reallly I am just looking for a way to create
collections and am wondering if there is any merity in do so via a custom
range object and any further merit incorporating the help of something
outside of VBA (C++/dll).

Thanks

EM

 

[Peter T]

OK I see what you are getting at, as it happens I've done a lot of work
along similar lines, at least for cell interior & font colours.

The bad news is there is no direct way other than looping. You can store the
results as string address though I simply built a range object (ie your
"custom object", actually an array of range objects per unique property
value).

Looping cells is painfully slow, with XL10+ you can use the Find method with
cell properties but I haven't found that useful. C++ is great for
calculation but AFAIK it doesn't particularly help in terms of speed when it
comes to reading cell properties.

However there is a lot you can do to speed up things by checking large areas
for the same property or excluding areas with no related format, starting
with the whole sheet.

The other thing that's potentially slow is building large multi-area range
objects, which soon becomes exponentially slower; again there are ways to
significantly speed up the process. Unfortunately it's all a lot of work, I
have perhaps 2k lines to do what could be done in about 20, purely for the
sake of speed.

I have an app that's been in long term development that might be of
assistance to you, depending on what you are after. Eg, select all same
colour cells and/or Name same for future use (up to 3,600 multi-areas per
name). Also select same colour CF cells, and a whole load more. In effect an
interface along the lines of special cells for colour. The code is not
accessible and not (yet) for XL2007, but contact if interested.

Regards,
Peter T

 

[ExcelMonkey]

Peter, thanks for the offer for your tool. I don't need it at this point.
I will keep plugging away at this as keeps me thinking!

Thanks

EM