+ Reply to Thread
Results 1 to 4 of 4

Excel 2010 crashing when running macros....my code?

  1. #1
    Registered User
    Join Date
    05-07-2013
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Excel 2010 crashing when running macros....my code?

    Hello everyone. I'm new here, and pretty new to VBA in general (so I apologize for my clunky code). I have a fairly large workbook (@16mb) with several macros and the other day I went in to edit some of the macros to reflect streamlining and efficiency techniques I've been picking up through self-teaching and web research.....well, now for some reason each time I try to run one of the macros, Excel goes unresponsive ("Excel has stopped working.....") and I cannot for the life of me figure out what I've done to mess this up.

    The macros in question are not super complex by any means......the one I am posting below I did f8 through and it seems to be crashing upon the declaration of LastRow as a variable............not sure why. Thoughts? Help is appreciated in advance and I apologize if this duplicates another thread, but I have not been able to find a specific problem like this through search yet....The long and short of this macro is I'm trying to copy formulas down to last populated row and then paste over them as values to save processing effort......

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Excel 2010 crashing when running macros....my code?

    I have not tested it and some of the copied range my overlap. You need to use the next empty row when you paste, not A4

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-07-2013
    Location
    Vermont, USA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Excel 2010 crashing when running macros....my code?

    So I changed the variable LastRow to LR - and it seems to be working now - which brings me to a question - can I have more than one LastRow defined within the workbook - my understanding is that the assigned value for a variable clears itself at the end of each sub, so in theory you could keep redefining it, right? Do you have to explicitly clear it at the beginning of a new sub? If so, how do you do that? I'm not sure why it didn't work........anyway, thanks for your time and help!

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Excel 2010 crashing when running macros....my code?

    Diet,
    LR and LastRow are two variables. You were using the lastrow in your paste command which made the macro to crash. Do not also use absolute cell reference($).
    In VBA, unlike other many languages, you do not need to declare variables in order to use them in your code.
    You could have used LR, or Lastrow with out declaring them with the Dim statement. It is a good practice, however, to declare variable with your code for a number of reasons:
    If you do not declare variables with their data type(Like,long,integer,etc),VBA will treat them as Variant type of variables.
    For e.g., on the above code, if you had used LR with out declaring it, VBA will treat it as the default type- Variant.
    This acts like chameleon. Variant type consume lots of memory , hence one reason for declaring variables -is efficiency of running your code (speed).
    There are many other reasons for using variables, but , I got stop now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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