Styles in XL 2007

A

andrswal

I recently lost all my server data by stupidly downloading a program which
reckoned it could manage xl styles. The reason I downloaded the program was
becasue I was desperate that a spreadsheet I had used for years had suddenly
got too many styles.

Eventually after getting rid of the rogue add in I manually deleted over
1,000 styles in the spreadsheet to return to the 10 styles I have always used
in office 2003.

What I have subsequently discovered is that everytime you copy and paste
from another spreadsheet in 2007 the styles comes with it so for instance I
use standard styles. spreadsheet 1 might have currency (0). If I copy a
cell from this spreadsheet to spreadsheet 2, then spreadsheet 2 (which has
the identical codes to spreadsheet 1) gets one of more new styles called
Currency (0) 1, Currency (0) 2, and so on. in my damaged spreadsheet I had
to get rid of 99 styles per standard style.

The only way I now get round this is to always copy/special/values to
prevent styles copying.

Is there a simpler way to prevent this bug?
 
J

Jan Karel Pieterse

Hi Andrswal,
Eventually after getting rid of the rogue add in I manually deleted over
1,000 styles in the spreadsheet to return to the 10 styles I have always used
in office 2003.

What I have subsequently discovered is that everytime you copy and paste
from another spreadsheet in 2007 the styles comes with it so for instance I
use standard styles. spreadsheet 1 might have currency (0). If I copy a
cell from this spreadsheet to spreadsheet 2, then spreadsheet 2 (which has
the identical codes to spreadsheet 1) gets one of more new styles called
Currency (0) 1, Currency (0) 2, and so on. in my damaged spreadsheet I had
to get rid of 99 styles per standard style.

The only way I now get round this is to always copy/special/values to
prevent styles copying.

Is there a simpler way to prevent this bug?
Click to expand...

You can get rid of unused styles using these routines:


Sub RemoveUnUsedStyles()
Dim oSt As Style
Dim colUsedStyles As Collection
Dim bSuccess As Boolean
Dim lCount As Long
On Error Resume Next
Set colUsedStyles = GetUsedStyles(ActiveWorkBook)
For Each oSt In ActiveWorkBook.Styles
lCount = lCount + 1
If Not oSt.BuiltIn Then
If Not IsIn(colUsedStyles, oSt.Name) Then
oSt.Delete
End If
End If
Next
On Error GoTo 0
End Sub

Function GetUsedStyles(ByRef oBook As Workbook) As Collection
'-------------------------------------------------------------------------
' Procedure : GetUsedStyles Created by Jan Karel Pieterse
' Company : JKP Application Development Services (c) 2006
' Author : Jan Karel Pieterse
' Created : 5-5-2006
' Purpose : Returns a collection of the names of the used styles in the
workbook (oBook)
'-------------------------------------------------------------------------
Dim oSh As Worksheet
Dim oCell As Range
Dim colUsedStyles As Collection
On Error Resume Next
Err.Clear
Set colUsedStyles = New Collection
For Each oSh In oBook.Worksheets
If TypeName(oSh) <> "Module" Then
For Each oCell In oSh.UsedRange.Cells
If Not IsIn(colUsedStyles, oCell.Style.Name) Then
colUsedStyles.Add oCell.Style.Name
End If
Next
End If
Next
If Not colUsedStyles.Count = 0 Then
Set GetUsedStyles = colUsedStyles
End If
bSuccess = (Err.Number = 0)
On Error GoTo 0
End Function

Function IsIn(colCollection As Object, ByVal sName As String) As Boolean
'-------------------------------------------------------------------------
' Procedure : IsIn Function Created by Jan Karel Pieterse
' Company : JKP Application Development Services (c) 2006
' Author : Jan Karel Pieterse
' Created : 05-05-2006
' Purpose : Determines if object/item is in collection
'-------------------------------------------------------------------------
Dim vMember As Variant
On Error Resume Next
For Each vMember In colCollection
Err.Clear
If vMember = sName Then
If Err.Number = 0 Then
IsIn = True
Exit Function
End If
End If
Err.Clear
If vMember.Name = sName Then
If Err.Number = 0 Then
IsIn = True
Exit Function
End If
End If
Next
End Function

If you need help with workbook problems, get in touch. (see my contact page on
my website as listed below.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com
 
A

andrswal

Thanks Jan but the point is the styles were not unused. they were created on
a copy and paste and applied to the cell which was pasted into the
spreadsheet so many of the surplus styles were technically in use. as I copy
and paste often however your macro could be very useful for cells which have
been overwritten and indeed have left their style there.

One further point when I ran your macro it deleted all styles in use or not
other than the basic 2007 inbuiilt macros so I dont think that is quite what
I was hoping for!
 
J

Jan Karel Pieterse

Hi Andrswal,
One further point when I ran your macro it deleted all styles in use or not
other than the basic 2007 inbuiilt macros so I dont think that is quite what
I was hoping for!
Click to expand...

Hmm, sounds not too good does it <g>. It should leave used styles alone. Are
you sure the removed styles were actually used?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com
 
A

andrswal

O yes, a page full of currency turned instantly to basic numbers! I would
add the new 2007 I am sure handles styles differently than 2003 xl did and as
you macro was written in 2006, that could be the reason. I also had some
difficult getting the macro to run so changed it a bit..(yes I fiddled!) and
that is far more likely to have been the culprit! The amended version whicb
worked (if you want to call it worked) was:

Sub RemoveUnUsedStyles()
Dim oSt As Style
Dim colUsedStyles As Collection
Dim bSuccess As Boolean
Dim lCount As Long
On Error Resume Next
Set colUsedStyles = GetUsedStyles(ActiveWorkbook)
For Each oSt In ActiveWorkbook.Styles
lCount = lCount + 1
If Not oSt.BuiltIn Then
If Not IsIn(colUsedStyles, oSt.Name) Then
oSt.Delete
End If
End If
Next
On Error GoTo 0
End Sub
Function GetUsedStyles()
Dim oSh As Worksheet
Dim oCell As Range
Dim colUsedStyles As Collection
On Error Resume Next
Err.Clear
Set colUsedStyles = New Collection
For Each oSh In oBook.Worksheets
If TypeName(oSh) <> "Module" Then
For Each oCell In oSh.UsedRange.Cells
If Not IsIn(colUsedStyles, oCell.Style.Name) Then
colUsedStyles.Add oCell.Style.Name
End If
Next
End If
Next
If Not colUsedStyles.Count = 0 Then
Set GetUsedStyles = colUsedStyles
End If
bSuccess = (Err.Number = 0)
On Error GoTo 0
End Function
Function IsIn(colCollection As Object, ByVal sName As String) As Boolean
Dim vMember As Variant
On Error Resume Next
For Each vMember In colCollection
Err.Clear
If vMember = sName Then
If Err.Number = 0 Then
IsIn = True
Exit Function
End If
End If
Err.Clear
If vMember.Name = sName Then
If Err.Number = 0 Then
IsIn = True
Exit Function
End If
End If
Next

End Function
 
J

Jan Karel Pieterse

Hi Andrswal,
The amended version whicb
worked (if you want to call it worked) was:
Click to expand...

You made a change that caused the trouble: you removed the argument to the
function that lists the used styles but did not redirect the function to use
the active workbook. This is the correct code:

Option Explicit

Sub RemoveUnUsedStyles()
'-------------------------------------------------------------------------
' Procedure : RemoveUnUsedStyles
' Company : JKP Application Development Services (c)
' Author : Jan Karel Pieterse (www.jkp-ads.com)
' Created : 2-6-2008
' Purpose : Sub to remove unused styles from a workbook
'-------------------------------------------------------------------------
Dim oSt As Style
Dim colUsedStyles As Collection
Dim bSuccess As Boolean
Dim lCount As Long
On Error Resume Next
Set colUsedStyles = GetUsedStyles(ActiveWorkbook)
For Each oSt In ActiveWorkbook.Styles
lCount = lCount + 1
If Not oSt.BuiltIn Then
If Not IsIn(colUsedStyles, oSt.Name) Then
oSt.Delete
End If
End If
Next
On Error GoTo 0
End Sub

Function GetUsedStyles(ByRef oBook As Workbook) As Collection
Dim oSh As Worksheet
Dim oCell As Range
Dim colUsedStyles As Collection
On Error Resume Next
Err.Clear
Set colUsedStyles = New Collection
For Each oSh In oBook.Worksheets
If TypeName(oSh) <> "Module" Then
For Each oCell In oSh.UsedRange.Cells
If Not IsIn(colUsedStyles, oCell.Style.Name) Then
colUsedStyles.Add oCell.Style.Name
End If
Next
End If
Next
If Not colUsedStyles.Count = 0 Then
Set GetUsedStyles = colUsedStyles
End If
On Error GoTo 0
End Function

Function IsIn(colCollection As Object, ByVal sName As String) As Boolean
Dim vMember As Variant
On Error Resume Next
For Each vMember In colCollection
Err.Clear
If vMember = sName Then
If Err.Number = 0 Then
IsIn = True
Exit Function
End If
End If
Err.Clear
If vMember.Name = sName Then
If Err.Number = 0 Then
IsIn = True
Exit Function
End If
End If
Next
End Function


Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com
 
A

andrswal

Firstly thank you for taking the time toi review this Jan

Last time I got errors (hence my reason for deleting the arguments which
were throwing up the errors), this time it ran great but still deleted all
the styles. As I deleted the old routine and simply copied and pasted your
new version I dont think I caused any problems along the way....
 
J

Jan Karel Pieterse

Hi Andrswal,
Last time I got errors (hence my reason for deleting the arguments which
were throwing up the errors), this time it ran great but still deleted all
the styles. As I deleted the old routine and simply copied and pasted your
new version I dont think I caused any problems along the way....
Click to expand...

In Excel 2007 or in 2003 (I tested in 2003 and it left any used styles intact)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com
 
A

andrswal

In 2007. I am just about to re-install xp and go back to that as all I get
from 2007 is useless graphics and poor functionality.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with MS Word paragraph and character styles: format getting messed up 2
Missing default cell styles on some large existing spreadsheets 0
VBA - (re-) Set a Table Style 2
Styles Across Multiple Documents 1
Toggle styles on/off in restricted Word 2007 template 4
R1C1 reference style as default while opening Excel 3
"Excel found unreadable content" 5
style sheets 1

Top