Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

That should help quite a bit.

The Application.ScreenUpdating = False prevents Excel from showing you all
of the changes happening in "real time" - keeps the screen from flickering as
you go to different sheets or cells and can speed things up by a factor of 30
or more. But since you're using ActiveSheet.Range(...) instead of first
selecting the cells and then setting their value to "", it's probably not
going to be a huge time saver (you're already doing it very efficiently) but
will still help some.

Setting the calculation to Manual while doing all of these updates may be
the big one for you - as you're changing the various values on the sheet(s),
Excel is trying to recalculate all dependent values based on the new value
you're setting. Depending on how many cells are downstream-dependent on the
ones you're clearing, this could be a big improvement. None of the interim
calculations will take place, just one final one at the end of the process
when you turn automatic calculation back on.

"Dawg House Inc" wrote:

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

Oops -- check spelling of Calculation in your Sub wclearbutton_Click sample
code above!
"Dawg House Inc" wrote:

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

Thanks...I'll test it out shortly.

One thing I noticed is that even updating a cell manually (typing over a
value or even changing the background color) takes way longer than it should.

Excel seems to be holding something in memory somewhere...my current
MemUsage in my Task Manager is almost 100MB!

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"JLatham" wrote:

Oops -- check spelling of Calculation in your Sub wclearbutton_Click sample
code above!
"Dawg House Inc" wrote:

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

100 MB for the one application/process?

All I can do is quote from Ghostbusters
....there's somethin' strange in your neighborhood...

Debra Dalgliesh has some info/routines that may help clean it up a little
over at her site:
http://www.contextures.com/xlfaqApp.html#Unused

She details out both a process and has code to make sure on that page.

I just went to a machine with Windows XP Pro/SP2 and Excel 2003 on it and
opened up a 7.3MB Excel file that is heavy with inter-sheet links, some
serious formula usage and a good bit of VBA code in it and my Task manager
said that Excel.exe was 'only' using 34MB, when I closed that file, it
dropped down to 'only' 21MB.

"Dawg House Inc" wrote:

Thanks...I'll test it out shortly.

One thing I noticed is that even updating a cell manually (typing over a
value or even changing the background color) takes way longer than it should.

Excel seems to be holding something in memory somewhere...my current
MemUsage in my Task Manager is almost 100MB!

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"JLatham" wrote:

Oops -- check spelling of Calculation in your Sub wclearbutton_Click sample
code above!
"Dawg House Inc" wrote:

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

I was in the midst of testing and notice a hidden "text box" control on my
worksheet. So I deleted it. Much to my chagrin, another was underneath
it...and another, etc. I'm assuming they were there from the origination of
the application and the attempts to use text box controls (don't ask). I've
been manually deleting text boxes for 15-20 minutes straight and there
appears to be no end.

How do I mass delete all "rectangles" and "lines" ? My delete button is on a
coffee break at the moment.

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Thanks...I'll test it out shortly.

One thing I noticed is that even updating a cell manually (typing over a
value or even changing the background color) takes way longer than it should.

Excel seems to be holding something in memory somewhere...my current
MemUsage in my Task Manager is almost 100MB!

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"JLatham" wrote:

Oops -- check spelling of Calculation in your Sub wclearbutton_Click sample
code above!
"Dawg House Inc" wrote:

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

Well...I've solved the mystery of the gigantic and molasses-like performance
file.

1. The original file set up had utilized text boxes (aka - rectangles) to
hold the text input values.
2. Each time the values were updated, they were "copying" the text box (not
just the value) and placing a copy elsewhere. This was done for the 23 input
locations per sheet (6 sheets).
3. I ran the following script to clean them out:
Dim i As Integer
i = 1
Do Until i 7
Sheets(i).Rectangles.Delete
Sheets(i).Lines.Delete
i = i + 1
Loop

My file size just dropped from 6.5MB to 485KB. The mystery is solved!
I've destroyed the StayPuff Marshmallow Man!

I should have run a count on them first to see...but as I said, I was
manually deleting the controls for 15-20 minutes (this was only one rectangle
location too).

Oh well...thanks for all your help. Love the forums!
Cheers,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"JLatham" wrote:

100 MB for the one application/process?

All I can do is quote from Ghostbusters
...there's somethin' strange in your neighborhood...

Debra Dalgliesh has some info/routines that may help clean it up a little
over at her site:
http://www.contextures.com/xlfaqApp.html#Unused

She details out both a process and has code to make sure on that page.

I just went to a machine with Windows XP Pro/SP2 and Excel 2003 on it and
opened up a 7.3MB Excel file that is heavy with inter-sheet links, some
serious formula usage and a good bit of VBA code in it and my Task manager
said that Excel.exe was 'only' using 34MB, when I closed that file, it
dropped down to 'only' 21MB.

"Dawg House Inc" wrote:

Thanks...I'll test it out shortly.

One thing I noticed is that even updating a cell manually (typing over a
value or even changing the background color) takes way longer than it should.

Excel seems to be holding something in memory somewhere...my current
MemUsage in my Task Manager is almost 100MB!

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"JLatham" wrote:

Oops -- check spelling of Calculation in your Sub wclearbutton_Click sample
code above!
"Dawg House Inc" wrote:

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

You are not alone...
I had a client one time that sent me a workbook to make some changes.
I discovered it had over 1000 button images stacked one on top of the other.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




"Dawg House Inc"

wrote in message
Well...I've solved the mystery of the gigantic and molasses-like performance
file.
1. The original file set up had utilized text boxes (aka - rectangles) to
hold the text input values.
2. Each time the values were updated, they were "copying" the text box (not
just the value) and placing a copy elsewhere. This was done for the 23 input
locations per sheet (6 sheets).
3. I ran the following script to clean them out:
Dim i As Integer
i = 1
Do Until i 7
Sheets(i).Rectangles.Delete
Sheets(i).Lines.Delete
i = i + 1
Loop

My file size just dropped from 6.5MB to 485KB. The mystery is solved!
I've destroyed the StayPuff Marshmallow Man!
I should have run a count on them first to see...but as I said, I was
manually deleting the controls for 15-20 minutes (this was only one rectangle
location too).
Oh well...thanks for all your help. Love the forums!
Cheers,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

Well, I'm not going to say that in your original posting you didn't mention
having text boxes (although you did mention command buttons). But I probably
wouldn't have keyed in on that anyhow. And yet I had much the same thing
happen years ago (Excel 97) when I designed a workbook for SW Bell that had
numerous controls (command buttons) on a sheet that could be replicated.
Eventually we hit a flat error in that there weren't enough resources to
create another sheet with all those buttons and controls on them. It took me
a while to figure out just what the problem was and how to fix it (and I've
forgotten now how I did that).

Glad the mystery is solved, and that you came up with the code solution for
getting rid of them without breaking your finger, the [Del] key or anything
else.

Yep, great forum - I keep learning from it. Not only new things completely,
but new ways to do things I've been doing, only better. I noticed just this
evening that Gord Dibben had put up a method of quickly filling 300 cells in
a column that used a method I seldom (if ever) do use, so his familiarity
with that method definitely improved on the one I'd offered, reminded me of
another way to do that, and gave a great option to the person who had the
question in the first place.

Enjoy!

"Dawg House Inc" wrote:

Well...I've solved the mystery of the gigantic and molasses-like performance
file.

1. The original file set up had utilized text boxes (aka - rectangles) to
hold the text input values.
2. Each time the values were updated, they were "copying" the text box (not
just the value) and placing a copy elsewhere. This was done for the 23 input
locations per sheet (6 sheets).
3. I ran the following script to clean them out:
Dim i As Integer
i = 1
Do Until i 7
Sheets(i).Rectangles.Delete
Sheets(i).Lines.Delete
i = i + 1
Loop

My file size just dropped from 6.5MB to 485KB. The mystery is solved!
I've destroyed the StayPuff Marshmallow Man!

I should have run a count on them first to see...but as I said, I was
manually deleting the controls for 15-20 minutes (this was only one rectangle
location too).

Oh well...thanks for all your help. Love the forums!
Cheers,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"JLatham" wrote:

100 MB for the one application/process?

All I can do is quote from Ghostbusters
...there's somethin' strange in your neighborhood...

Debra Dalgliesh has some info/routines that may help clean it up a little
over at her site:
http://www.contextures.com/xlfaqApp.html#Unused

She details out both a process and has code to make sure on that page.

I just went to a machine with Windows XP Pro/SP2 and Excel 2003 on it and
opened up a 7.3MB Excel file that is heavy with inter-sheet links, some
serious formula usage and a good bit of VBA code in it and my Task manager
said that Excel.exe was 'only' using 34MB, when I closed that file, it
dropped down to 'only' 21MB.

"Dawg House Inc" wrote:

Thanks...I'll test it out shortly.

One thing I noticed is that even updating a cell manually (typing over a
value or even changing the background color) takes way longer than it should.

Excel seems to be holding something in memory somewhere...my current
MemUsage in my Task Manager is almost 100MB!

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"JLatham" wrote:

Oops -- check spelling of Calculation in your Sub wclearbutton_Click sample
code above!
"Dawg House Inc" wrote:

Here's a sample snipet of one of the command buttons and its code:
Private Sub wclearbutton_Click()

Call Reset_Regional_Bracket

End Sub
---
Public Sub Reset_Regional_Bracket()

Application.Cursor = xlWait
ActiveSheet.Range("C2, C6, C10, C14, C18, C22, C26, C30").Value = ""
ActiveSheet.Range("D2, D6, D10, D14, D18, D22, D26, D30").Value = ""
ActiveSheet.Range("E2, E6, E10, E14, E18, E22, E26, E30").Value = ""
ActiveSheet.Range("F2, F6, F10, F14, F18, F22, F26, F30").Value = ""
ActiveSheet.Range("G4, G12, G20, G28").Value = ""
ActiveSheet.Range("H4, H12, H20, H28").Value = ""
ActiveSheet.Range("I8, I24").Value = ""
ActiveSheet.Range("J8, J24").Value = ""
ActiveSheet.Range("K16").Value = ""
ActiveSheet.Range("L16").Value = ""

'Reset Merged Cells
ActiveSheet.Range("E3, E11, E19, E27, F3,F11, F19, F27, H5, H21, I9,
J9").Value = 0


Select Case UCase(ActiveSheet.Name)
Case "WEST"
With ActiveSheet
.wopt1and16.Value = False
.wopt1and16.Value = False
.wopt2and15.Value = False
.wopt3and14.Value = False
.wopt4and13.Value = False
.wopt5and12.Value = False
.wopt6and11.Value = False
.wopt7and10.Value = False
.wopt8and9.Value = False
.wopt1and8and9and16.Value = False
.wopt2and7and10and15.Value = False
.wopt3and6and11and14.Value = False
.wopt4and5and12and13.Value = False
.wopttopelite8.Value = False
.woptbottomelite8.Value = False
End With
Case "EAST"
With ActiveSheet
.Eopt1and16.Value = False
.Eopt1and16.Value = False
.Eopt2and15.Value = False
.Eopt3and14.Value = False
.Eopt4and13.Value = False
.Eopt5and12.Value = False
.Eopt6and11.Value = False
.Eopt7and10.Value = False
.Eopt8and9.Value = False
.Eopt1and8and9and16.Value = False
.Eopt2and7and10and15.Value = False
.Eopt3and6and11and14.Value = False
.Eopt4and5and12and13.Value = False
.Eopttopelite8.Value = False
.Eoptbottomelite8.Value = False
End With
Case "MIDWEST"
With ActiveSheet
.MWopt1and16.Value = False
.MWopt1and16.Value = False
.MWopt2and15.Value = False
.MWopt3and14.Value = False
.MWopt4and13.Value = False
.MWopt5and12.Value = False
.MWopt6and11.Value = False
.MWopt7and10.Value = False
.MWopt8and9.Value = False
.MWopt1and8and9and16.Value = False
.MWopt2and7and10and15.Value = False
.MWopt3and6and11and14.Value = False
.MWopt4and5and12and13.Value = False
.MWopttopelite8.Value = False
.MWoptbottomelite8.Value = False
End With
Case "SOUTH"
With ActiveSheet
.Sopt1and16.Value = False
.Sopt1and16.Value = False
.Sopt2and15.Value = False
.Sopt3and14.Value = False
.Sopt4and13.Value = False
.sopt5and12.Value = False
.Sopt6and11.Value = False
.Sopt7and10.Value = False
.Sopt8and9.Value = False
.Sopt1and8and9and16.Value = False
.Sopt2and7and10and15.Value = False
.Sopt3and6and11and14.Value = False
.Sopt4and5and12and13.Value = False
.Sopttopelite8.Value = False
.Soptbottomelite8.Value = False
End With
Case Else
MsgBox "You have prompted an error!" & Chr(13) & _
"Press OK to continue.", vbOKOnly, ":: Error ::"
Application.Cursor = xlDefault
Exit Sub
End Select
Application.Cursor = xlDefault
End Sub

I have not turned on the screenupdating and calculation features. Not
exactly sure what those are (can guess, but wasn't aware). So...should I put
it like this:
Private Sub wclearbutton_Click()

Application.ScreenUpdating = False
Application.Calulcation = xlCalculationManual
Call Reset_Regional_Bracket
Application.Calulcation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub



Thanks in advance.
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Gary Keramidas" wrote:

i guess the obvious question is since you didn't post any code, are you turning
screenupdating and calculation off before clearing the cells and then turning it
back on after clearing the cells?

sub example
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'code in module

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

--


Gary


"Dawg House Inc" wrote in message
...
Just for more information:

Current File Size: 6.46MB
Modules: 1
Sheets: 7 (two are text only)
Command Buttons: 17 each on 4 sheets, 1 on another (each with a call to a
function in Module1
Option Buttons: 14 each on 4 sheets, 6 on another (each with a call to a
function in Module1)
Time Check: 43 seconds to "clear" values in 34 cells (called through a
routine)

--
Dawg House Inc.
"We live in it, therefore, we know it!"


"Dawg House Inc" wrote:

Good day. I have a series of worksheets that I have created an application
with and control the logic and functionality through Excel. Why? Cause I have
the software and it does the job - for now.
Well, after designing and developing the application, I noticed that it
continues to slow down in speed with every run I do. To the point now where
clearing out 15-20 cell values takes 1-2 minutes.

I read on the forums that Excel holds onto old values/references or
something, but I don't quite understand it. What I need to ensure is that
everytime I close my xls file, I need it to clear/delete/remove any and all
unused references.

I'll gladly share the file with anyone to see what I've done, simply post
something on here and we can discuss - or send me a msg on MSN Msger
(dawghouseinc).

I'm going crazy with the molasses-like performance. I've cleaned my code,
modulized all common routines in public functions, etc. Any suggestions would
be great.

Components used: Command Buttons, Option Buttons, multi-sheet references,
VBA code on worksheets and 1 module (Module1).

Thanks,
JCH
--
Dawg House Inc.
"We live in it, therefore, we know it!"

An update on the solution - the sample below will delete all Rectangles,
Lines and Pictures on all sheets in a given workbook. To delete other object
types, simply add a line within the do loop like this:
Sheets(i).<objectname.Delete

Good luck.
JCH
~~~

Sub Delete_All_Objects()
Dim i, j As Integer
j = Sheets.Count
i = 1
Do Until i j
Sheets(i).Rectangles.Delete
Sheets(i).Lines.Delete
Sheets(i).Pictures.Delete
i = i + 1
Loop

End Sub
--
Dawg House Inc.
"We live in it, therefore, we know it!"

Glad you figured out the code. And the reason for the ever growing file size.

I did something like that some years ago, but with control buttons on a
worksheet - code kept adding more on top of older ones until it finally
crashed the (Excel 97) application due to lack of system resources. It took
me some time to figure out just what the hell I'd done to it all.

"Dawg House Inc" wrote:

An update on the solution - the sample below will delete all Rectangles,
Lines and Pictures on all sheets in a given workbook. To delete other object
types, simply add a line within the do loop like this:
Sheets(i).<objectname.Delete

Good luck.
JCH
~~~

Sub Delete_All_Objects()
Dim i, j As Integer
j = Sheets.Count
i = 1
Do Until i j
Sheets(i).Rectangles.Delete
Sheets(i).Lines.Delete
Sheets(i).Pictures.Delete
i = i + 1
Loop

End Sub
--
Dawg House Inc.
"We live in it, therefore, we know it!"