Results 1 to 2 of 2

VBA Macro causes excel to crash

Threaded View

  1. #1
    Registered User
    Join Date
    10-20-2015
    Location
    Philadelphia,PA
    MS-Off Ver
    2013
    Posts
    31

    VBA Macro causes excel to crash

    The macro I am currently writing in excel VBA has the problem of causing the entire excel workbook to crash on some occasions while having no crash effect on other occasions. In between running the macro and having it crash/not crash I will make tiny changes to the code which could "cause" the macro to crash, but when I restart excel and make the changes again, the macro runs smoothly without crashing. Would anybody be able to help me out and see if there is a loop or something that I am not closing or a coding error I am doing? I am not used to coding in VBA so I'm not completely sure of all the nuances.

    
    Sub Printing_Preference()
    'Printer Preferences Macro Coding
    '==========
    
    Worksheets("Sheet1").Activate
    
    'NOTE:::::The below code does not set up every page to have the same column headers and row headers, instead it simply sets up one or the other and you must manually update the sheet information in order to have a column "copy" over vertically or a row "copy" over horizontally
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1" 'Sets the rows 1 to 1 to copy over to each page vertically, i.e. the first row on each sheet will be automatically mimicked to each sheet vertically below it and there forth
        .PrintTitleColumns = "$A:$A" 'Sets the columns A to A to copy over to each page horizontally, i.e. the first column on each sheet will be automatically mimicked to each sheet horizontally next to it and there forth
    End With
    
    
    With Worksheets("Sheet1").PageSetup 'Set the page setup below this line, all page setup values can be found here https://msdn.microsoft.com/en-us/library/office/dn254188.aspx
        .BlackAndWhite = False 'True sets the printer to print in black and white, false sets the printer to print in color
        .LeftMargin = Application.InchesToPoints(0.5) 'left margin in inches, normal size is 0.7
        .RightMargin = Application.InchesToPoints(0.5) ' right margin in inches, normal size is 0.7
        .TopMargin = Application.InchesToPoints(0.5) 'top margin in inches, normal size is 0.75
        .BottomMargin = Application.InchesToPoints(0.5) 'bottom margin in inches, normal size is 0.75
        .HeaderMargin = Application.InchesToPoints(0.2) 'header margin in inches, normal size is 0.3
        .FooterMargin = Application.InchesToPoints(0.2) 'footer margin in inches, normal size is 0.3
        .PaperSize = xlPaper11x17 'Set paper size to tabloid 11x17, other options are found at https://msdn.microsoft.com/en-us/library/office/ff839964.aspx
        .PrintArea = False 'Sets printing area to be the entire workbook, in order to set this as a specific area in the workbook, see the line below
        'ActiveSheet.PageSetup.PrintArea = "$A$1:$G$11" 'Set printing area to be in the range specified within the quotations
        .Orientation = xlLandscape 'Sets printing orientation to Landscape, use xlPortrait to set orientation to portrait
        .PrintHeadings = False 'Sets the row/column headings to print if the value is true, sets them to not print if the heading is false, i.e. Row A, Row B, Row C..etc., Column A, Column B, Column C..etc.
        
        
    End With
    
    'Potential fix for the automatic double sided printing style of these printers, takes each page into account and prints each page 1 at a time rather than the entire document at once,
    'if the "'" is removed from the code below, it will cause the entire worksheet to print out after running the macro, only remove the "'" when you are ready to print your document
    
    
    'Dim i As Long
       ' For i = 1 To ActiveSheet.HPageBreaks.Count + 1
            'ActiveWindow.SelectedSheets.PrintOut From:=i, To:=i, Copies:=1
        'Next i
    
    
    End Sub
    Also at the end of my code where I have the single sided page printing code,(The part with Dim i as long, for i=1..........) "commented", out, is there a way to fix this so that it will only print when I want it to print instead of whenever I run the code WITHOUT, having to comment out that entire portion of code whenever I just want to run the code?
    Last edited by BoundToExcel; 10-21-2015 at 10:21 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro causing excel to crash
    By bradleyandrewdavis in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-16-2015, 04:29 AM
  2. [SOLVED] Excel Crash On Second Loop in Macro
    By skywlker32 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2013, 08:32 AM
  3. Excel Crash On Second Loop in Macro
    By skywlker32 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-04-2013, 09:31 AM
  4. [SOLVED] beforesave macro causes excel to crash
    By carrob in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-13-2012, 10:06 AM
  5. Excel crash while enabling macro
    By SoftwareTester in forum Excel General
    Replies: 4
    Last Post: 08-03-2008, 10:05 AM
  6. Excel crash - bug macro?
    By al007 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-25-2005, 06:55 AM
  7. Excel VBA macro crash
    By Patrick in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-13-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1