Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Overflow issue (clear worksheet that has Worksheet_Change action)
Hi,
I've got an overflow error that I don't know how to deal with. High level: I'm clearing all contents and formats from a sheet, and then copying/pasting all (updated) contents and formats from another workbook/sheet (first section of code below) The Target worksheet (the one being cleared and pasted into) has Worksheet_Change code that I've just added (second bit of code below). When the cells on the Target sheet are cleared, it triggers the Worksheet_Change event, and I get the overflow error. Any thoughts as to why, and more importantly, how to deal with this? Thanks! With wksSource wksTarget.Cells.ClearContents wksTarget.Cells.Interior.Pattern = xlNone .Cells.Copy wksTarget.Cells(1) End With 'wksSource Private Sub Worksheet_Change(ByVal Target As Range) Dim iCol As Long Dim iRow As Long Dim myNotes As String If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub [more code here, but it dies at the first line] Frank |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Overflow issue (clear worksheet that has Worksheet_Change action)
Try...
With wksTarget .UsedRange.Clear wksSource.UsedRange.Copy .Cells(1) End With 'wksSource -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Overflow issue (clear worksheet that has Worksheet_Change action)
Oops.., forgot most important part...
With Application .EnableEvents = False: ScreenUpdating = False wksTarget.UsedRange.Clear wksSource.UsedRange.Copy wksTarget.Cells(1) .EnableEvents = True: ScreenUpdating = True End With 'Application -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Overflow issue (clear worksheet that has Worksheet_Change action)
Hi Garry,
Thanks for this! I plugged it into the Worksheet_Change module, and I'm getting a Run Timer Error 424 'Object required' error message at the wksTarget.UsedRange.Clear line. Should this code go somewhere else? Private Sub Worksheet_Change(ByVal Target As Range) Dim iCol As Long Dim iRow As Long Dim myNotes As String With Application .EnableEvents = False: ScreenUpdating = False wksTarget.UsedRange.Clear wksSource.UsedRange.Copy wksTarget.Cells(1) .EnableEvents = True: ScreenUpdating = True End With 'Application On Sun, 05 Jun 2016 11:13:03 -0400, GS wrote: Oops.., forgot most important part... With Application .EnableEvents = False: ScreenUpdating = False wksTarget.UsedRange.Clear wksSource.UsedRange.Copy wksTarget.Cells(1) .EnableEvents = True: ScreenUpdating = True End With 'Application |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Overflow issue (clear worksheet that has Worksheet_Change action)
Hi Garry,
Thanks for this! I plugged it into the Worksheet_Change module, and I'm getting a Run Timer Error 424 'Object required' error message at the wksTarget.UsedRange.Clear line. Should this code go somewhere else? Private Sub Worksheet_Change(ByVal Target As Range) Dim iCol As Long Dim iRow As Long Dim myNotes As String With Application .EnableEvents = False: ScreenUpdating = False wksTarget.UsedRange.Clear wksSource.UsedRange.Copy wksTarget.Cells(1) .EnableEvents = True: ScreenUpdating = True End With 'Application This is based on your original code where I assume you have 'Set' a ref to wksSource and wksTarget in a standard module procedure. That's where this code goes, NOT in your Worksheet_Change event! The point of this is so that sheet event code doesn't fire while you're updating the contents of the worksheet. Since you didn't post ALL the code, it's difficult to determine what it's doing. Obviously, you are running a procedure that clears any content/format in the target sheet before populating it with content/format of the source sheet. What the event code does is not clear, nor is why it exists. In a standard module... Sub TransferData() Dim wksSource As Worksheet, wksTarget As Worksheet Set wksSource = Workbooks("Filename").Sheets("Sheetname") Set wksTarget = ThisWorkbook.Sheets("Sheetname") With Application .EnableEvents = False: ScreenUpdating = False wksTarget.UsedRange.Clear wksSource.UsedRange.Copy wksTarget.Cells(1) .EnableEvents = True: ScreenUpdating = True End With 'Application End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Overflow issue (clear worksheet that has Worksheet_Change action)
On Mon, 06 Jun 2016 00:19:12 -0400, GS wrote:
Hi Garry, Thanks for this! I plugged it into the Worksheet_Change module, and I'm getting a Run Timer Error 424 'Object required' error message at the wksTarget.UsedRange.Clear line. Should this code go somewhere else? Private Sub Worksheet_Change(ByVal Target As Range) Dim iCol As Long Dim iRow As Long Dim myNotes As String With Application .EnableEvents = False: ScreenUpdating = False wksTarget.UsedRange.Clear wksSource.UsedRange.Copy wksTarget.Cells(1) .EnableEvents = True: ScreenUpdating = True End With 'Application This is based on your original code where I assume you have 'Set' a ref to wksSource and wksTarget in a standard module procedure. That's where this code goes, NOT in your Worksheet_Change event! The point of this is so that sheet event code doesn't fire while you're updating the contents of the worksheet. Since you didn't post ALL the code, it's difficult to determine what it's doing. Obviously, you are running a procedure that clears any content/format in the target sheet before populating it with content/format of the source sheet. What the event code does is not clear, nor is why it exists. In a standard module... Sub TransferData() Dim wksSource As Worksheet, wksTarget As Worksheet Set wksSource = Workbooks("Filename").Sheets("Sheetname") Set wksTarget = ThisWorkbook.Sheets("Sheetname") With Application .EnableEvents = False: ScreenUpdating = False wksTarget.UsedRange.Clear wksSource.UsedRange.Copy wksTarget.Cells(1) .EnableEvents = True: ScreenUpdating = True End With 'Application End Sub Hi Garry. Thanks for the explanation. This worked beautifully. And I now understand what you are saying, and I see this is a replacement fo rmy other copy cells code. I've also got a few lines of code that run at the beginning and end of my larger modules that turn off screen updating and calculations, and when I saw the EnableEvents and ScreenUpdating line, I just thought this needed to go there. I'm good to go for this piece of my puzzle. Thank you!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Overflow issue (clear worksheet that has Worksheet_Change action)
Hi Garry. Thanks for the explanation. This worked beautifully. And
I now understand what you are saying, and I see this is a replacement fo rmy other copy cells code. I've also got a few lines of code that run at the beginning and end of my larger modules that turn off screen updating and calculations, and when I saw the EnableEvents and ScreenUpdating line, I just thought this needed to go there. I'm good to go for this piece of my puzzle. Thank you!! You're very welcome! FWIW Here's how I handle enabling/disabling fast code. It uses a global Type to manage which procedures turn things off/on so you don't run into unexpected issues... '-------------------------------------------------------------------------------------- ' **Note: EnableFastCode requires the following declarations be in a standard module. '-------------------------------------------------------------------------------------- 'Type udtAppModes ' 'Default types ' Events As Boolean: CalcMode As XlCalculation: Display As Boolean: CallerID As String ' 'Project-specific types 'End Type 'Public AppMode As udtAppModes '-------------------------------------------------------------------------------------- Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True) ' **Note: Requires 'Type udtAppModes' and 'Public AppMode As udtAppModes' declarations 'The following will make sure only the Caller has control, 'and allows any Caller to take control when not in use. If AppMode.CallerID < Caller Then _ If AppMode.CallerID < "" Then Exit Sub With Application If SetFast Then AppMode.Display = .ScreenUpdating: .ScreenUpdating = False AppMode.CalcMode = .Calculation: .Calculation = xlCalculationManual AppMode.Events = .EnableEvents: .EnableEvents = False AppMode.CallerID = Caller Else .ScreenUpdating = AppMode.Display .Calculation = AppMode.CalcMode .EnableEvents = AppMode.Events AppMode.CallerID = "" End If End With End Sub ...and here's how to set your projects up to use it... Sub DoTask() Const sSource$ = "DoTask" EnableFastCode sSource 'do intensive stuff EnableFastCode sSource, False End Sub ...where sSource holds the name of the procedure (Sub or Function) that would use this. If already in use by another procedure, EnableFastCode will not get toggled. Note: The const sSource is also used for error logging and so is multi-purpose in my projects! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Overflow issue (clear worksheet that has Worksheet_Change action)
On Tue, 07 Jun 2016 11:40:23 -0400, GS wrote:
Hi Garry. Thanks for the explanation. This worked beautifully. And I now understand what you are saying, and I see this is a replacement fo rmy other copy cells code. I've also got a few lines of code that run at the beginning and end of my larger modules that turn off screen updating and calculations, and when I saw the EnableEvents and ScreenUpdating line, I just thought this needed to go there. I'm good to go for this piece of my puzzle. Thank you!! You're very welcome! FWIW Here's how I handle enabling/disabling fast code. It uses a global Type to manage which procedures turn things off/on so you don't run into unexpected issues... '-------------------------------------------------------------------------------------- ' **Note: EnableFastCode requires the following declarations be in a standard module. '-------------------------------------------------------------------------------------- 'Type udtAppModes ' 'Default types ' Events As Boolean: CalcMode As XlCalculation: Display As Boolean: CallerID As String ' 'Project-specific types 'End Type 'Public AppMode As udtAppModes '-------------------------------------------------------------------------------------- Sub EnableFastCode(Caller$, Optional SetFast As Boolean = True) ' **Note: Requires 'Type udtAppModes' and 'Public AppMode As udtAppModes' declarations 'The following will make sure only the Caller has control, 'and allows any Caller to take control when not in use. If AppMode.CallerID < Caller Then _ If AppMode.CallerID < "" Then Exit Sub With Application If SetFast Then AppMode.Display = .ScreenUpdating: .ScreenUpdating = False AppMode.CalcMode = .Calculation: .Calculation = xlCalculationManual AppMode.Events = .EnableEvents: .EnableEvents = False AppMode.CallerID = Caller Else .ScreenUpdating = AppMode.Display .Calculation = AppMode.CalcMode .EnableEvents = AppMode.Events AppMode.CallerID = "" End If End With End Sub ..and here's how to set your projects up to use it... Sub DoTask() Const sSource$ = "DoTask" EnableFastCode sSource 'do intensive stuff EnableFastCode sSource, False End Sub ..where sSource holds the name of the procedure (Sub or Function) that would use this. If already in use by another procedure, EnableFastCode will not get toggled. Note: The const sSource is also used for error logging and so is multi-purpose in my projects! I am DEFINITELY going to plug that in mine and see how it improves the timing. And there's another guy I collaborate with at work that I'm sure will find this extremely useful. Thank you VERY much for taking that additional time to add that and help educate me. ...people like you on these forums never cease to amaze me with their (your) generosity of time and patience - I was a technical training instructor in the US Air Force for several years, and I know it takes patience to teach. :) Thank you! Frank |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory Overflow issue (clear worksheet that has Worksheet_Change action)
I am DEFINITELY going to plug that in mine and see how it improves
the timing. And there's another guy I collaborate with at work that I'm sure will find this extremely useful. Thank you VERY much for taking that additional time to add that and help educate me. ...people like you on these forums never cease to amaze me with their (your) generosity of time and patience - I was a technical training instructor in the US Air Force for several years, and I know it takes patience to teach. :) Thank you! My Pleasure! FWIW, I taught apprenticeship training (retired now) for our Ministry of Labour in the Transportation & Industrial Power fields at the technical division of a community college in my hometown. I know how easy it is to get frustrated with people who really shouldn't be where they're messing about. Excel is daunting enough to get folks into trouble very easily so it's nice to hear/know I'm helping others to grow their skills!!! (Just reciprocating what was given<g) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion --- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to clear clipboard memory? | Excel Discussion (Misc queries) | |||
Clear Cells and Worksheet_Change Q | Excel Programming | |||
Excel ODBC query tables memory overflow | Excel Programming | |||
Do you wish to clear clipboard memory? | Excel Programming |