Posts by usacomp2k3

Re: "excel 4.0 Macro" Warning

Quote from RichardSchollar

Are you also searching in hidden workbook names? If not, then checking any such names may be worthwhile too...

Well there you go. Thanks a bunch. There was one with a get.document name.

Re: "excel 4.0 Macro" Warning

Quote from RichardSchollar

Do you have any defined names using XLM4 functions? Such names could use XLM4 like GET.CELL and GET.DOCUMENT. If you want to search in code, then you just need to search for the text "ExecuteExcel4".

All 1352 names that are in the workbook seem to be valid. (ex: "=Standard!$D$98"). I did a search through the workbook, and there were no 'get' references in either the values or forumulas. Also, a search for "execute" in the code did not return anything, neither did "excel4" or even "excel" (other than the word "excel" in 1 comment line).
Thanks for the ideas though.

Re: "excel 4.0 Macro" Warning

Quote from AAE

The function you are no longer using must be either an Excel add-in or a custom function. You will have to go into the VB Editor and manually delete any modules with code or remove the add in.

Thanks for the reply. Unfortunately, it's not an Excel add-in:
[Blocked Image: http://img49.imageshack.us/img49/9764/toolbar3ub4.jpg][Blocked Image: http://img182.imageshack.us/img182/2158/toolbar4dy2.jpg]
Re: a custom function, if it is, I have no idea which one. I have over 2500 lines of code in this workbook, so going through every line to find it is not a trivial matter, not to mention that I don't even know what to look for.

I keep getting this warning. I temporarily used a 4.0 macro, but found a better way to do it, so I no longer am using that function. How do I remove whatever it is that is calling it so that user's don't have to see this message. There is a small probability that something else somewhere is calling a 4.0 command, but I have no idea how to find out what that is.
Thanks.
[Blocked Image: http://img236.imageshack.us/img236/5021/toolbar1kr3.jpg]
I've checked the references, and excel 4.0 Macro's aren't listed, and I'm pretty sure they were before when I was using them.[Blocked Image: http://img152.imageshack.us/img152/1207/toolbar2la3.jpg]

Re: Set Page Breaks Groups For X Conditions

Quote from Dave Hawley

Then please share your working code.

I did. See the end of the first post. It would have been a separate post except double posts are merged.

Re: Manual Page Breaks Based On Groups Of Data

Quote from Dave Hawley

Save yourself the grief and create 2 Custom Views.

I know I've already solved it, but out of curiosity, what do you mean?

So basically I have a report that is generated that is broken down into 4 or 5 headers. What I want is basically a way to manually add page breaks for 2 conditions:
*That no section starts at the end of one page and ends on the next; I want to just insert a page break before the section so it is all on the next page.
* That if the first section is more than a page long, that it inserts a new header saying 'section 1 continued..' basically.

I've been able to code it and it functions how I want. However, it takes about 20-30 seconds on a dual-core computer with 2gb of memory to insert these page breaks, so I thought I'd get some expert advice on where the slow-downs are and how I could go about tightening up the code to get it to run faster. (this subprocedure is #15 of 15 when generating the report, and the rest take between 2 and 5 seconds combined).

Sub Quote_Page_Breaks()
Dim financials_start_row As Integer, financials_start_page As Integer, financials_end_row As Integer, financials_end_page As Integer
Dim recommended_start_row As Integer, recommended_start_page As Integer, recommended_end_row As Integer, recommended_end_page As Integer
Dim standard_end_row As Integer, standard_end_page As Integer
Dim i As Integer, page_break() As Integer
Dim recommended As Boolean
On Error Resume Next
'get the row numbers of the start of each section
If Quote_CE_P_Cab Then standard_end_row = Sheets("Quotation").Range("Q_Options").Row - 1 Else standard_end_row = Sheets("Quotation").Range("Q_Base_Unit").Row - 1
financials_start_row = Sheets("Quotation").Range("Q_Financials").Row
If Quote_Options_Recommendations = True Then
    recommended_start_row = Sheets("Quotation").Range("Q_Recommended").Row
    financials_end_row = recommended_start_row - 2
    recommended_end_row = ActiveCell.SpecialCells(xlLastCell).End(xlToLeft).End(xlUp).Row
Else
    financials_end_row = ActiveCell.SpecialCells(xlLastCell).End(xlToLeft).End(xlUp).Row
    recommended_start_row = 0
    recommended_end_row = 0
End If
'get the row numbers of each page break
ReDim page_break(1)
For i = 1 To ActiveSheet.UsedRange.Rows.Count
     If Rows(i).PageBreak = xlManual Then
        ReDim Preserve page_break(UBound(page_break) + 1)
        page_break(UBound(page_break)) = i
     ElseIf Rows(i).PageBreak = xlAutomatic Then
        ReDim Preserve page_break(UBound(page_break) + 1)
        page_break(UBound(page_break)) = i
     End If
Next
ReDim Preserve page_break(UBound(page_break) + 1)
page_break(UBound(page_break)) = ActiveCell.SpecialCells(xlLastCell).Row
'get the page numbers of each section
For i = 1 To UBound(page_break) - 1
    If (standard_end_row >= page_break(i) And standard_end_row < page_break(i + 1)) Then standard_end_page = i
    If (financials_start_row >= page_break(i) And financials_start_row < page_break(i + 1)) Then financials_start_page = i
    If (financials_end_row >= page_break(i) And financials_end_row < page_break(i + 1)) Then financials_end_page = i
    If Quote_Options_Recommendations = True Then
        If (recommended_start_row > page_break(i) And recommended_start_row < page_break(i + 1)) Then recommended_start_page = i
        If (recommended_end_row >= page_break(i) And recommended_end_row < page_break(i + 1)) Then recommended_end_page = i
    End If
Next
If financials_start_page = 0 Then financials_start_page = UBound(page_break) - 1
If financials_end_page = 0 Then financials_end_page = UBound(page_break) - 1
If recommended_start_page = 0 Then recommended_start_page = UBound(page_break) - 1
If recommended_end_page = 0 Then recommended_end_page = UBound(page_break) - 1
'check the values and add page breaks if needed
If standard_end_page > 1 Then
    Sheets("Quotation").Rows(page_break(2) & ":" & page_break(2)).Insert shift:=xlDown
    Call Quote_Format_Border(Sheets("Quotation").Range("a1").Offset(page_break(2) - 1, 0).Address, "Standard Specifications continued...")
End If
If financials_start_page <> financials_end_page Then
    Sheets("Quotation").Range("Q_Financials").Select
   ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Selection
End If
If Quote_Options_Recommendations = True Then
    If recommended_start_page <> recommended_end_page Then
        Sheets("Quotation").Range("Q_Recommended").Select
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Selection
    End If
End If
End Sub

Notes:
The page is broken up into the following named sections:
*Q_Standard_Specifications
*Q_Options (not always present)
*Q_Base_Unit
*Q_Options_Chosen
*Q_Financials
*Q_Recommended (not always present)
These are all variable length depending on the data used to generate the report and the type of report generated (which is why 2 of them aren't always there).

That's all that I can think of. Let me know if I left out any explanations, and thanks a bunch for the help!

UPDATE:
Well I think I got it working well enough. I replaced the page_break lookup code with the following, and it works like a champ now:

'get the row numbers of each page break
Names.Add "HPBreaks", "=GET.DOCUMENT(64)", False
page_break = [HPBreaks] 'so using evaluate to fill a variant array
ReDim Preserve page_break(UBound(page_break) + 2)
For i = UBound(page_break) - 1 To 1 Step -1
    page_break(i + 1) = page_break(i)
Next
page_break(1) = 0
page_break(UBound(page_break)) = Range("A65536").End(xlUp).Row

Hope this helps someone else maybe.