Macro not working as expected

[bknight]

Loads in about 1 minute for me. It has ballooned to 10 M , is there a way to "compact" it?

[HansV]

I tried the version that you sent me by e-mail. Same problem - Excel displays "Validating" and then uses 100% CPU and stops reacting.

[bknight]

Like I said it loads in about 1 minute. It has ballooned to 10 M in one week, almost triple the size of what it was. Any way to compact it?. I do have a version that is maybe a month ago and I could restore it and re-load the data, if you think that may help.

[HansV]

In each sheet, press Ctrl+End and see if you end up beyond the used range of the sheet. If so, delete the rows and/or columns from the last used one to the cell where you ended up. Then save the workbook.

[bknight]

Tired that and deleted some 6K blank rows on 1st sheet. Still at 10 M.

[HansV]

I tried to open and repair the workbook; that failed too.

What happens if you save the workbook as a macro-enabled Excel workbook (*.xlsm)?

[HansV]

I can open the latest one you sent me, and the macro runs without errors in about 5 to 10 seconds on my PC.

I recommend saving the workbook as an Excel binary workbook (.xlsb). This reduces the file size to about 1 MB.

[Doc.AElstein]

Hi Han’s
I do not have a problem opening that original File bknight uploaded.

_......

Hi bknight, ( and Hans )
Looks like Hans as just got you there . But I started so I finished !!

For me, The file you originally uploaded took a while to open, but not longer than some similar files of mine of similar size.
( I use mostly XL 2007 32Bit on an old ACER lap top ( ACER Aspire 4810TZG 32Bit 4GB RAM )
The general speed of the code ties up with what I am used to. It appears in Excel that the size of a file effects the speed at which a code works, especially when you are interacting with a spreadsheet as you are doing.

_1) What are you trying to do ?
I apologize if I have missed something, but at least some of your code appears to achieve nothing in effect.
For example here

Code: Select all

        Range(Cells(I - 2, 6), Cells(I - 2, 9)).Copy Range(Cells(I - 1, 6), Cells(I - 1, 9))

You are copying formulas from one line to the next. The formulas are already there. So you paste in formulas that are already there. Nothing changes. Nothing is achieved.
Hans offered you the “bypass Clipboard” way to Copy and Paste in one go. If you checked out the link I gave before you will see that there are several ways to Copy and Paste. If you use the “bypass Clipboard” then you “get what you get”. You cannot choose the format that is copied, as you can for example with the “Copy PasteSpecial way”. So possibly the “bypass Clipboard” is not doing what you want to do?
However, having said that, your original code did this

Code: Select all

       Range(Cells(I - 2, 6), Cells(I - 2, 9)).Copy
        Range(Cells(I - 1, 6), Cells(I - 1, 9)).PasteSpecial xlPasteAll

This also has the same effect. So it achieves nothing.


_2) The code does take quite a while when I run it. It crashes at some point


_3) Problem with the code time
I have a few unanswered Threads with a similar problem. The only conclusion so far is that in some situation Excel has a memory of memory used. One would expect Excel to clear any memory it no longer needs. However it appears not to always to do that. Therefore in codes interacting with a spreadsheet, if a lot of actions are taken, a point is reached when all of Excels memory is used up. At this point either Excel crashes or some operation does not work as it should and a code errors in an unpredictable way, or you get a situation where the “wheel” appears for ever – somehow Excel is hung up and does not have the ability to sought itself as it has run out of memory. It is a very awkward situation and very frustrating. You will get no help from Microsoft. – I doubt they would want to admit that this strange “memory of memory used” problem exist. In the unanswered Threads I had, some people tried my codes on their very new fast 64Bit Excel and 64 Bit Computers. Often the codes I got nowhere with on my older XL and older Computers worked on their Excel and Computers!!
I think you will have it hard getting a solution that does not involve updating to the “newest” – The powers that be will, of course, want you to do that !


I do have Files working with rows up to 100,000. But I do not have formulas in every row as you do.

I added a Status Bar watch thingy, in your first Loop, like

Code: Select all

    Application.StatusBar = I - intStartRow
    Loop Until Cells(I, 2) = ""

So I could see how the code was getting on.
The slow progress is what I am used to for my codes. Excel crashes or hangs up after doing about 110 rows. Or at that point it starts getting incredibly slow. This is consistent with my experience. I expect as you approach the point of using up all Excel’s memory, then things start taking a ridiculous amount of time and , in many cases, a point is reached where it hangs up, or crashes so does not get further.
Towards this point, the use of Ctrl+Pause is often also ineffective – somehow Excel is not able to react to that and just keeps trying to do what it is currently doing very slowly. The only option then is to do something drastic like kill Excel ( or pull the plug and re boot !!)
Note when you experience the strange “memory of memory” problem, then you must restart excel to clear the problem ( re set the memory )


I expect the problem is a combination of the File size and the interaction with the Spreadsheet and possibly this mysterious “memory of memory problem”


_......

_4) Speed improvement:
Here is a quick attempt at a one possible solution for you:
I am Just looking at the first loop ( which does not get past about doing 110 rows for me )

So for Worksheets("Vix")
Instead of interacting with the Worksheet at every line, you do the typical “VBA Array stuff”.

_First the Vix Range is captured in one go to an Array
_Then you Loop through the Array copying formulas from one “row” to the next “row” down in that Array.
_Finally, the Array is pasted back out to the Worksheet.

I ran this modified code section.
The code whips through that first Range in a split second, ( and does not crash ! ) ( Also you do not need the .EnableEvents, .ScreenUpdating, = False , or .Calculation = xlCalculationManual )

So the code snippet I give you replaces your first
Do

Loop Until Cells(I, 2) = ""

Code: Select all

Dim rngVix As Range ' variable to hold the entire Range you are currently considering
 Set rngVix = Worksheets("Vix").Range("F" & I - 1 & ":I" & 3261 & "")
Dim arrrngVix() As Variant 'Dynamic Array to hold all range values. Dim ed to suit the types returned by the following .Formula Property
 Let arrrngVix() = rngVix.Formula ' Formulas as strings taken into the Array
Dim Cntrw As Long, Cntclm As Long
    For Cntrw = 1 To (UBound(arrrngVix(), 1) - 1) ' at every "row" in the Array, (Up to the one before the last),  we ...
        For Cntclm = 1 To UBound(arrrngVix(), 2) ' ... go along each "column" in the Array and ...
         Let arrrngVix(Cntrw + 1, Cntclm) = arrrngVix(Cntrw, Cntclm) ' ... the next "cell" down is given the current "cell" formula
        Next Cntclm
    Next Cntrw
Let rngVix.Value = arrrngVix() ' This will paste out the Formula values back to the Worksheet

Alan

[bknight]

Wow, thanks for the time and effort.

Hi Han’s
I do not have a problem opening that original File bknight uploaded.

_......

Hi bknight, ( and Hans )
Looks like Hans as just got you there . But I started so I finished !!

For me, The file you originally uploaded took a while to open, but not longer than some similar files of mine of similar size.
( I use mostly XL 2007 32Bit on an old ACER lap top ( ACER Aspire 4810TZG 32Bit 4GB RAM )
The general speed of the code ties up with what I am used to. It appears in Excel that the size of a file effects the speed at which a code works, especially when you are interacting with a spreadsheet as you are doing.

_1) What are you trying to do ?
I apologize if I have missed something, but at least some of your code appears to achieve nothing in effect.
For example here

Code: Select all

        Range(Cells(I - 2, 6), Cells(I - 2, 9)).Copy Range(Cells(I - 1, 6), Cells(I - 1, 9))

You are copying formulas from one line to the next. The formulas are already there. So you paste in formulas that are already there. Nothing changes. Nothing is achieved.

You are exactly correct copy one row to the next row down until there is no more data (Cells(i,2)=="" The macro is nothing but a time saver as I am able to copy/paste manually. I just wanted to make the process to be automatic.

Hans offered you the “bypass Clipboard” way to Copy and Paste in one go. If you checked out the link I gave before you will see that there are several ways to Copy and Paste. If you use the “bypass Clipboard” then you “get what you get”. You cannot choose the format that is copied, as you can for example with the “Copy PasteSpecial way”. So possibly the “bypass Clipboard” is not doing what you want to do?
However, having said that, your original code did this

Code: Select all

       Range(Cells(I - 2, 6), Cells(I - 2, 9)).Copy
        Range(Cells(I - 1, 6), Cells(I - 1, 9)).PasteSpecial xlPasteAll

This also has the same effect. So it achieves nothing.

You are looking at the wrong macro, look at the next macro down. The copy procedure has been changed in that one.

_2) The code does take quite a while when I run it. It crashes at some point


_3) Problem with the code time
I have a few unanswered Threads with a similar problem. The only conclusion so far is that in some situation Excel has a memory of memory used. One would expect Excel to clear any memory it no longer needs. However it appears not to always to do that. Therefore in codes interacting with a spreadsheet, if a lot of actions are taken, a point is reached when all of Excels memory is used up. At this point either Excel crashes or some operation does not work as it should and a code errors in an unpredictable way, or you get a situation where the “wheel” appears for ever – somehow Excel is hung up and does not have the ability to sought itself as it has run out of memory. It is a very awkward situation and very frustrating. You will get no help from Microsoft. – I doubt they would want to admit that this strange “memory of memory used” problem exist. In the unanswered Threads I had, some people tried my codes on their very new fast 64Bit Excel and 64 Bit Computers. Often the codes I got nowhere with on my older XL and older Computers worked on their Excel and Computers!!
I think you will have it hard getting a solution that does not involve updating to the “newest” – The powers that be will, of course, want you to do that !


I do have Files working with rows up to 100,000. But I do not have formulas in every row as you do.

I added a Status Bar watch thingy, in your first Loop, like

Code: Select all

    Application.StatusBar = I - intStartRow
    Loop Until Cells(I, 2) = ""

How do you add this "Thingy" in the status bar?

So I could see how the code was getting on.
The slow progress is what I am used to for my codes. Excel crashes or hangs up after doing about 110 rows. Or at that point it starts getting incredibly slow. This is consistent with my experience. I expect as you approach the point of using up all Excel’s memory, then things start taking a ridiculous amount of time and , in many cases, a point is reached where it hangs up, or crashes so does not get further.
Towards this point, the use of Ctrl+Pause is often also ineffective – somehow Excel is not able to react to that and just keeps trying to do what it is currently doing very slowly. The only option then is to do something drastic like kill Excel ( or pull the plug and re boot !!)
Note when you experience the strange “memory of memory” problem, then you must restart excel to clear the problem ( re set the memory )


I expect the problem is a combination of the File size and the interaction with the Spreadsheet and possibly this mysterious “memory of memory problem”


_......

_4) Speed improvement:
Here is a quick attempt at a one possible solution for you:
I am Just looking at the first loop ( which does not get past about doing 110 rows for me )

So for Worksheets("Vix")
Instead of interacting with the Worksheet at every line, you do the typical “VBA Array stuff”.

_First the Vix Range is captured in one go to an Array
_Then you Loop through the Array copying formulas from one “row” to the next “row” down in that Array.
_Finally, the Array is pasted back out to the Worksheet.

I ran this modified code section.
The code whips through that first Range in a split second, ( and does not crash ! ) ( Also you do not need the .EnableEvents, .ScreenUpdating, = False , or .Calculation = xlCalculationManual )

So the code snippet I give you replaces your first
Do

Loop Until Cells(I, 2) = ""

Code: Select all

Dim rngVix As Range ' variable to hold the entire Range you are currently considering
 Set rngVix = Worksheets("Vix").Range("F" & I - 1 & ":I" & 3261 & "")
Dim arrrngVix() As Variant 'Dynamic Array to hold all range values. Dim ed to suit the types returned by the following .Formula Property
 Let arrrngVix() = rngVix.Formula ' Formulas as strings taken into the Array
Dim Cntrw As Long, Cntclm As Long
    For Cntrw = 1 To (UBound(arrrngVix(), 1) - 1) ' at every "row" in the Array, (Up to the one before the last),  we ...
        For Cntclm = 1 To UBound(arrrngVix(), 2) ' ... go along each "column" in the Array and ...
         Let arrrngVix(Cntrw + 1, Cntclm) = arrrngVix(Cntrw, Cntclm) ' ... the next "cell" down is given the current "cell" formula
        Next Cntclm
    Next Cntrw
Let rngVix.Value = arrrngVix() ' This will paste out the Formula values back to the Worksheet

Alan

I'll give that a try

[bknight]

The "key" to all the macros is the "PutCall Ratio" as this one not only copies one row to another, but calculates a 21 and 9 day moving average. That is where intStartRow
gets its start date, some time before 21 days from "today", update when I'm not lazy every 6 months. One trick to limit the number of iterations the macro runs is to determine what is the last date that has data (the row that doesn't have formulas) as a start date, then the intStartRow would be dynamic every day. I'm not sure how quite to do that.

It is interesting that there is a memory limiting macro execution. This behavior was never observed in either 97 or 2k, with my sheets. Newer is supposed to be better.

[Doc.AElstein]

Hi

You are looking at the wrong macro, look at the next macro down. The copy procedure has been changed in that one.

I am not quite sure what you mean there, but never mind – I get the point of what you are doing.

You can paste those formulas in, in one go, manually, or with one code line instead. See here for example:
http://www.eileenslounge.com/viewtopic. ... 52#p192980" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 15#p194481" onclick="window.open(this.href);return false;
http://powerspreadsheets.com/r1c1-formular1c1-vba/" onclick="window.open(this.href);return false;

_.............

Re Status bar thingy
( That is the Bar just below the Worksheet Tabs ( The bottom bar in the Excel Window )
You get it automatically: try this

Code: Select all

Sub StatusBarThingy()
 Let Application.DisplayStatusBar = False 'Makes the staus bar vanish
 let Application.DisplayStatusBar = True ' Puts it back to the default of showing
 Let Application.StatusBar = "Hello Mr bknight" 'Put some text there for you to see
End Sub

_.............


Just a few comments in light of Han’s solution:

I often find that an .xls File needs loads more space in my XL 2007 than a .xlsm File.
I do not know why that is. Possibly there is lots of stuff to do with compatibility stored
Your original uploaded File comes out by me as
10.048MB for .xls
and
1.756MB for .xlsm
( and
1.116MB for .xlsb

_....

For the .xlsm File:
It still takes a while to open, but not more than I am used to for a big File
I get the same problem that the code or excel crashes or gets hung up after about 110 rows in the first
Do

Loop Until Cells(I, 2) = ""



_....

Something I also forgot to mention already. When I run your original code , then when it gets towards the point of hanging up, then any other things going on on my computer also start getting very slow or crash. Especially things like WORD. So again this is suggesting that Excel ( and Office ) is starting to get into a mess with its memory.

_.......

.... One trick to limit the number of iterations the macro runs is to determine what is the last date that has data (the row that doesn't have formulas) as a start date, then the intStartRow would be dynamic every day. I'm not sure how quite to do that.....

I am not quite sure what you are asking there.
There are several ways to get the “last row”.
It depends exactly what you are looking for.
It is possible to distinguish as to whether a cell has values, formulas or is empty. See for example:
https://msdn.microsoft.com/en-us/librar ... 96157.aspx" onclick="window.open(this.href);return false;
It depends on your exact data layout

_....


I apologies again if I have missed the point, but if you want to populate a Range with formulas as you are doing, then “going down” copying from one row to the next is , I think, the worst way I can imagine to do it.
I think the single formula approach is probably the easiest
http://www.eileenslounge.com/viewtopic. ... 15#p194417" onclick="window.open(this.href);return false;


Alan



P.s. When you reply with a quote, or quotes, can you edit out some of the text. Just leaving enough to see what you are referring to. That makes the Thread a little less cluttered and easier to read, Thanks.

[bknight]

Double thread reply makes I difficult to get the right quotes.

One trick to limit the number of iterations the macro runs is to determine what is the last date that has data (the row that doesn't have formulas) as a start date, then the intStartRow would be dynamic every day. I'm not sure how quite to do that

My bolding. All the sheets are designed similarly, first column contains date, columns 2-->x has data, column x+1--> x+n all formulas. Where you see no formulas, that would be a good start date. In addition the Put Call has 21 and 9 days moving averages.

Code: Select all

Set rngVix = Worksheets("Vix").Range("F" & I - 1 & ":I" & 3261 & "")

I'm not sure why you used 3261 since the last row on Vix is 4266, but I edited your snippet and ran quickly as you observed.
Will the range copying formula work when the last row in the range has no formulas, I've never used this type of copying and am ignorant?

How does one determine the last row when the date column is always greater than or equal to the data?

[Rudi]

How does one determine the last row when the date column is always greater than or equal to the data?

ws = ActiveWorksheet
cl = Column reference to determine length of (eg: "A")
lr = Last cell of above column

lr= ws.Cells(ws.Rows.Count,cl).End(xlUp).Row
ws.Range("A2:A" & lr).Select '<Or any other action here...

[bknight]

How does one determine the last row when the date column is always greater than or equal to the data?

ws = ActiveWorksheet
cl = Column reference to determine length of (eg: "A")
lr = Last cell of above column

lr= ws.Cells(ws.Rows.Count,cl).End(xlUp).Row
ws.Range("A2:A" & lr).Select '<Or any other action here...

OK, thanks
The lr would be the number I believe I need.

[Doc.AElstein]

Hi bknight

Double thread reply makes I difficult to get the right quotes. ..

I do not follow you ? , but never mind. Not important

_......

...I'm not sure why you used 3261 since the last row on Vix is 4266, ....

This is what Vix looks like in the File I downloaded from your original link ( that link does not work anymore)
LastRowVix.JPG http://imgur.com/JNuEuNI" onclick="window.open(this.href);return false;

LastRowVix.JPG

_.....

I do not quite follow the rest of what you are asking. I have to be off for a bit now anyway. Have another go at explaining and I will take another look later.
( Rudi is showing you how to get at the last cell in a column with anything ( formula or data ) in it. Maybe that is one of the things you are asking.
The link I referenced will give you some idea of how to distinguish between formulas or values in a cell



Alan

[bknight]

Hi bknight

Double thread reply makes I difficult to get the right quotes. ..

I do not follow you ? , but never mind. Not important

_......

...I'm not sure why you used 3261 since the last row on Vix is 4266, ....

This is what Vix looks like in the File I downloaded from your original link ( that link does not work anymore)
LastRowVix.JPG http://imgur.com/JNuEuNI" onclick="window.open(this.href);return false;

LastRowVix.JPG

_.....

I do not quite follow the rest of what you are asking. I have to be off for a bit now anyway. Have another go at explaining and I will take another look later.
( Rudi is showing you how to get at the last cell in a column with anything ( formula or data ) in it. Maybe that is one of the things you are asking.
The link I referenced will give you some idea of how to distinguish between formulas or values in a cell



Alan

My bad when I copied information to the restored spreadsheet, I made an error as to the data and copied much more Vix data. You were correct.
Look at the image data does not exist Row 3262. When data is copied over everyday, there is data but no formulas on that row. I can't explain it any other way.

Will the range copying formula work when the last row in the range has no formulas, I've never used this type of copying and am ignorant?

Do you set the range containing blank rows(where formulas will go)?

[bknight]

How does one determine the last row when the date column is always greater than or equal to the data?

ws = ActiveWorksheet
cl = Column reference to determine length of (eg: "A")
lr = Last cell of above column

lr= ws.Cells(ws.Rows.Count,cl).End(xlUp).Row
ws.Range("A2:A" & lr).Select '<Or any other action here...

Code: Select all

Sub Test()
'
' Macro 12/14/2012 by Terry
' For McClellanOscillator workbook
ws = ActiveWorksheet
'cl = Column reference to determine length of (eg: "A")
'lr = Last cell of above column

lr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

End Sub

Rudi:
I get a 424 error Object required

[HansV]

Change

ws = ActiveWorksheet

to

Set ws = ActiveWorksheet

[bknight]

Results in error 13
Type Mismatch

[HansV]

How has ws been declared?