[VBA] loop, problem of 101'st iteration

[God Itself]

hello,

i wrote such a code:

Sub All_creation()
Application.ScreenUpdating = False
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long
Dim PPObj As Object

Set PPObj = CreateObject("PowerPoint.application")

Sheets("ID").Cells(43, 11).Value = 3

a = Range("M50").Value

For i = 1 To a 'a = 140 (LICZBA ITERACJI)

Sheets("ID").Cells(50, 11).Value = i

Range("L50").Formula = "=LEFT(INDEX(etykiety!R4C7:R300C7,RC[-1],1),5)"
Range("M50").Formula =
"=ROWS(etykiety!R4C7:R300C7)-COUNTBLANK(etykiety!R4C7:R300C7)"
Range("N50").Formula =
"=IF(R50C12="""","""",INDEX(R3C2:R602C2,MATCH(MID(LEFT(R50C12,4)&""0"",1,5),R3C6:R602C6,0),1))"
Range("O50").Value = "0"
Range("P50").Formula = "=IF(R50C12="""","""",MID(R50C12,5,1))"

Sheets("ID").Cells(3, 8).Value = Sheets("ID").Cells(50, 15).Value
'dopelnienie
Sheets("ID").Cells(3, 13).Value = Sheets("ID").Cells(50, 14).Value 'main
Sheets("ID").Cells(3, 14).Value = Sheets("ID").Cells(50, 16).Value 'klasa

Calculate

'### PRN ###

With PPObj
' .presentations.Add
.presentations.Open Filename:="F:\Analizy ISI\pl\AnBrMan.ppt"
.Visible = True
.Run "AnBrMan.ppt!UpdateMode"
.presentations("AnBrMan.ppt").Save

With PPObj.ActivePresentation.PrintOptions
.PrintInBackground = msoTrue
.RangeType = ppPrintAll
.Collate = msoTrue
.PrintColorType = ppPrintColor
.ActivePrinter = "Adobe PDF"
End With

PPObj.ActivePresentation.PrintOut PrintToFile:="F:\Analizy
ISI\pl\files\F-pdf\K\" & _
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn"

.presentations("AnBrMan.ppt").Close

End With

Next i

Application.ScreenUpdating = True
End Sub


key parameter in module is 'a' (it is number of iteration in loop)
unfortunately when 'i' equals 101 a module stops with error (error concerns
module in powerpoint document):

..Run "AnBrMan.ppt!UpdateMode"

best regards

 

[Cringing Dragon]

Have you run this with a set to a smaller number? Does it run, and do
everything that you expect it to? Does it work when a is 99, 100, and 101?
I'm just confirming that it's at exactly a=101 that there is a problem, and
not just at a figure somewhere near there.

If I read your message (and the macro) right, then a PowerPoint macro gets
called inside the loop, and that's where the Excel macro is stopping. Does
the PowerPoint macro use the variable i, or the worksheet range
Sheets("ID").Cells(50, 11), or any other counter that's driven by the loops?
If it does, then does it do something with that number that might be a
problem when it hits a 3 digit number?

When Excel starts the PowerPoint macro running, does Excel wait for that to
finish before running the next code line
(.presentations("AnBrMan.ppt").Save)? If not, it could be related to that -
PowerPoint attempting to save while a PP macro is running. I'm not sure why
it would wait until loop 101 to complain, though.

If you manually set sheet ID Cell K50 (is that the one that i gets written
to?) to 101, is everything OK in the spreadsheet?
Does i=101 push any filenames or vba strings over 255 characters? In Excel
2003, there are still some issues in VBA with long strings, I don't know
about Excel 2007.

You're also printing to a pdf file each loop. There's nothing to make it
pause after sending the print job, so you might be hitting a limitation on
the print spooler when too many jobs are spooled. But I can't see why that
would make it stop at the PowerPoint macro line, so I doubt that's the
problem.

--
If a post answers your question, please mark it as the answer.

hello,

i wrote such a code:

Sub All_creation()
Application.ScreenUpdating = False
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long
Dim PPObj As Object

Set PPObj = CreateObject("PowerPoint.application")

Sheets("ID").Cells(43, 11).Value = 3

a = Range("M50").Value

For i = 1 To a 'a = 140 (LICZBA ITERACJI)

Sheets("ID").Cells(50, 11).Value = i

Range("L50").Formula = "=LEFT(INDEX(etykiety!R4C7:R300C7,RC[-1],1),5)"
Range("M50").Formula =
"=ROWS(etykiety!R4C7:R300C7)-COUNTBLANK(etykiety!R4C7:R300C7)"
Range("N50").Formula =
"=IF(R50C12="""","""",INDEX(R3C2:R602C2,MATCH(MID(LEFT(R50C12,4)&""0"",1,5),R3C6:R602C6,0),1))"
Range("O50").Value = "0"
Range("P50").Formula = "=IF(R50C12="""","""",MID(R50C12,5,1))"

Sheets("ID").Cells(3, 8).Value = Sheets("ID").Cells(50, 15).Value
'dopelnienie
Sheets("ID").Cells(3, 13).Value = Sheets("ID").Cells(50, 14).Value 'main
Sheets("ID").Cells(3, 14).Value = Sheets("ID").Cells(50, 16).Value 'klasa

Calculate

'### PRN ###

With PPObj
' .presentations.Add
.presentations.Open Filename:="F:\Analizy ISI\pl\AnBrMan.ppt"
.Visible = True
.Run "AnBrMan.ppt!UpdateMode"
.presentations("AnBrMan.ppt").Save

With PPObj.ActivePresentation.PrintOptions
.PrintInBackground = msoTrue
.RangeType = ppPrintAll
.Collate = msoTrue
.PrintColorType = ppPrintColor
.ActivePrinter = "Adobe PDF"
End With

PPObj.ActivePresentation.PrintOut PrintToFile:="F:\Analizy
ISI\pl\files\F-pdf\K\" & _
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn"

.presentations("AnBrMan.ppt").Close

End With

Next i

Application.ScreenUpdating = True
End Sub


key parameter in module is 'a' (it is number of iteration in loop)
unfortunately when 'i' equals 101 a module stops with error (error concerns
module in powerpoint document):

..Run "AnBrMan.ppt!UpdateMode"

best regards

 

[God Itself]

Hi,

1. yes, it is always after 100 loops, even i start loop from 1 to 80 and
then second loop from 1 to 50 then i get problem at 21'st in 2nd loop

2. there are no common variables. macro in powerpoint is as follows:

Sub UpdateMode()

Dim lCtrA As Integer
Dim oPres As Object 'Presentation
Dim oSld As Slide
Set oPres = ActivePresentation
With oPres
' Process shapes on the slides
For Each oSld In .Slides
Call UpdtLinks(oSld)
Next
' Process shapes on the slides masters
If Val(Application.Version) > 9 Then
'For versions 2002 and later with multiple master support
For lCtrA = 1 To .Designs.Count
If .Designs(lCtrA).HasTitleMaster Then
Call UpdtLinks(.Designs(lCtrA).TitleMaster)
Else
Call UpdtLinks(.Designs(lCtrA).SlideMaster)
End If
Next
Else
' Version 97/2000
Call UpdtLinks(.SlideMaster)
If .HasTitleMaster Then
Call UpdtLinks(.TitleMaster)
End If
End If
End With

End Sub
Sub UpdtLinks(oSlideOrMaster As Object)
Dim oShp As PowerPoint.Shape
For Each oShp In oSlideOrMaster.Shapes
If oShp.Type = msoLinkedOLEObject Then
'update links
oShp.LinkFormat.Update
End If
Next oShp
End Sub

3. any application does not wait for another. but i'm only 99% sure

4. no function returns more than 255 character result. spreadsheet seems to
be ok

i'll paste error msg today evening

regards


Użytkownik "Cringing Dragon" <[email protected]>
napisał w wiadomości

 

[God Itself]

Error (during 101 loop) sounds:

Run-time error '-2147467259 (8004005)':

Automation Error
Undefined error ' translation from Polish

and when debug is pressed following part of code (given in first message in
this topic) is marked:

PPObj.ActivePresentation.PrintOut
PrintToFile:="F:\AnalizyISI\pl\files\F-pdf\K\" & _
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn"

regards

 

[Cringing Dragon]

OK. Let's see if we can narrow it down by a process of elimination.

As it's stopping on the PrintOut line, can you do the following:
Declare a string variable (I'll use strFile).
On the line just before the PrintOut line, define that variable as
strFile = "F:\AnalizyISI\pl\files\F-pdf\K\" & _
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" & _
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn"
(ie exactly what the PrintToFile is printing).
Change the PrintToFile so it uses that variable.

I don't know how well you know the VB editor, so I apologise if I'm telling
you stuff you already know.
In the Visual Basic editor in Excel, set a Watch to break on i=101, and also
set a normal watch on the strFile. Then run the macro (if you haven't used
Watch before, see VBE help. What that will do is break the code when you get
to i = 101.
When it stops, step through the code ([F8] key). Keep an eye on the watched
string variable. Step all the way through. When you get to the line that
defines strFile, how does it change? Does it still look like a reasonable
file name, or has it been truncated or done something else odd? If it looks
OK, keep going.
When you get to the PrintOut line (where I assume it will fail), what error
does it give? It'll probably be the same "undefined error", which isn't very
helpful.

If that doesn't give you any clues, then try adding a line after the For
statement that's
If i>99 Then Application.ScreenUpdating = True
so that you can have a look at what's going on in the spreadsheet at that
point.
The re-run (with the watch), when it stops at 1=101 then have a good look at
the spreadsheet, especially at ID!H3 to P3, and step through the macro. As it
does it's calculations - do any of those cells change to unexpected values?


I'm not sure how this would make it stop at i=101 when it runs until then,
but I'm pretty sure you're right that the Excel macro will not wait for the
PowerPoint macro to finish before it continues. So the PowerPoint macro could
still be running when you try to print the presentation. That could cause a
conflict - PowerPoint is busy when the PrintOut command is sent.


Also, just confirm that your "For i = 1 to a" is in the right place in you
macro. "a" is what's in ID!M50, but then just after your "For", you redefine
M50 on the active sheet (I assume that's sheet ID, but it might not be, so if
it's not then this comment is irrelevant) to be the number of non-blank cells
in a range on another sheet. So should that definition of "a" happen BEFORE
your For statement instead of after it?

 

[Mike Middleton]

God Itself -

Excel's Help for Tools | Options | Calculation says "Unless you specify
otherwise, Excel stops after 100 iterations ..."

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[God Itself]

Thanks for help.

i tracked macro in break mode. everything seems to be ok.. strFile looks
reasonable (=101.PRN)

but there is something wrong with powerpoint application. when i try to get
into powerpoin VB editor i get msg: out of memory, what means:

Out of memory (Error 7)
More memory was required than is available, or a 64K segment boundary was
encountered. This error has the following causes and solutions:

a.. You have too many applications, documents, or source files open.
Close any unnecessary applications, documents, or source files that are
open.

b.. You have a module or procedure that's too large.
Break large modules or procedures into smaller ones. This doesn't save
memory, but it can prevent hitting 64K segment boundaries.

c.. You are running Microsoft Windows in standard mode.
Restart Microsoft Windows in enhanced mode.

d.. You are running Microsoft Windows in enhanced mode, but have run out
of virtual memory.
Increase virtual memory by freeing some disk space, or at least ensure
that some space is available.

e.. You have terminate-and-stay-resident programs running.
Eliminate terminate-and-stay-resident programs.

f.. You have many device drivers loaded.
Eliminate unnecessary device drivers.

g.. You have run out of space for Public variables.
Reduce the number of Public variables.

For additional information, select the item in question and press F1 (in
Windows) or HELP (on the Macintosh).

anyway i have also problem with closing powerpoint document and/or
application (i can only kill this process)

when i open AnBrMan.ppt again then there is no problem to open powerpoint VB
Editor

there is also one clue thatthere must be problem with powerpoint (not
excel)..

if i start loop from 20 then i get errorif i =121.. so error occurs always
after 100 loops..

Użytkownik "Cringing Dragon" <[email protected]>
napisał w wiadomości

OK. Let's see if we can narrow it down by a process of elimination.

As it's stopping on the PrintOut line, can you do the following:
Declare a string variable (I'll use strFile).
On the line just before the PrintOut line, define that variable as
strFile = "F:\AnalizyISI\pl\files\F-pdf\K\" & _
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\" &
_
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value & ".prn"
(ie exactly what the PrintToFile is printing).
Change the PrintToFile so it uses that variable.

I don't know how well you know the VB editor, so I apologise if I'm
telling
you stuff you already know.
In the Visual Basic editor in Excel, set a Watch to break on i=101, and
also
set a normal watch on the strFile. Then run the macro (if you haven't used
Watch before, see VBE help. What that will do is break the code when you
get
to i = 101.
When it stops, step through the code ([F8] key). Keep an eye on the
watched
string variable. Step all the way through. When you get to the line that
defines strFile, how does it change? Does it still look like a reasonable
file name, or has it been truncated or done something else odd? If it
looks
OK, keep going.
When you get to the PrintOut line (where I assume it will fail), what
error
does it give? It'll probably be the same "undefined error", which isn't
very
helpful.

If that doesn't give you any clues, then try adding a line after the For
statement that's
If i>99 Then Application.ScreenUpdating = True
so that you can have a look at what's going on in the spreadsheet at that
point.
The re-run (with the watch), when it stops at 1=101 then have a good look
at
the spreadsheet, especially at ID!H3 to P3, and step through the macro. As
it
does it's calculations - do any of those cells change to unexpected
values?


I'm not sure how this would make it stop at i=101 when it runs until then,
but I'm pretty sure you're right that the Excel macro will not wait for
the
PowerPoint macro to finish before it continues. So the PowerPoint macro
could
still be running when you try to print the presentation. That could cause
a
conflict - PowerPoint is busy when the PrintOut command is sent.


Also, just confirm that your "For i = 1 to a" is in the right place in you
macro. "a" is what's in ID!M50, but then just after your "For", you
redefine
M50 on the active sheet (I assume that's sheet ID, but it might not be, so
if
it's not then this comment is irrelevant) to be the number of non-blank
cells
in a range on another sheet. So should that definition of "a" happen
BEFORE
your For statement instead of after it?
--
If a post answers your question, please mark it as the answer.

Error (during 101 loop) sounds:

Run-time error '-2147467259 (8004005)':

Automation Error
Undefined error ' translation from Polish

and when debug is pressed following part of code (given in first message
in
this topic) is marked:

PPObj.ActivePresentation.PrintOut
PrintToFile:="F:\AnalizyISI\pl\files\F-pdf\K\" & _
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 16).Value & "\"
& _
Workbooks("panel_et.xls").Sheets("ID").Cells(3, 15).Value &
".prn"

regards