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

See if Chip's blurb helps any:

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

"ExcelMonkey" wrote:

> 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
>
>

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" <(E-Mail Removed)> wrote in message
news:4D955BE1-98A4-403F-8171-(E-Mail Removed)...
>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
>
>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:4D955BE1-98A4-403F-8171-(E-Mail Removed)...
> >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
> >
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:5CFFCFA3-AFA2-4839-92B7-(E-Mail Removed)...
>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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:4D955BE1-98A4-403F-8171-(E-Mail Removed)...
>> >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
>> >
>> >
>>
>>
>>

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

"Peter T" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:5CFFCFA3-AFA2-4839-92B7-(E-Mail Removed)...
> >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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:4D955BE1-98A4-403F-8171-(E-Mail Removed)...
> >> >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
> >> >
> >> >
> >>
> >>
> >>
>
>
>