none
Recording Macro (Procedure to long) RRS feed

  • Question

  • I am new to VBA... I am trying to create a macro, but as i do, i get a warning that says procedure is to big. upon viewing the code, it appears that the macro is duplicating same procedure countless times. 

    is this something that i am doing? Or could there be something wrong with excel? please help me.

    Friday, June 26, 2015 5:56 PM

Answers

  • According to your description, you will get that error if your procedure is more than 64kb. These are some of the things that you can to compact your code

    1) Get rid of repetitive code.

    2) If you generated the code via the macro then you may get some useless code. Get rid of the useless code.

    3) Declare you Objects so that you don't have to keep on repeating them.

    4) Break Up your procedure if need be, and call the 2nd procedure from the 1st.

    5) Avoid using .Select and .Activate they not only make your code slow but also take a lot of space in your code if used extensively.

    Wednesday, July 1, 2015 5:36 AM

All replies

  • Hi lisa,

    Would you inform us what you did while recording macro?  e.g. select [File] - open xx file - save... like this.
    and would you share your macro using OneDrive, DropBox or other online storage?

    Regards,
    Friday, June 26, 2015 9:57 PM
  • What!!??  I've never heard of such a thing, and I've been using VBA for almost 15 years.  Can you reboot your machine and open a new Excel file and retry your procedure?  See if that works for you.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, June 27, 2015 3:45 AM
  • According to your description, you will get that error if your procedure is more than 64kb. These are some of the things that you can to compact your code

    1) Get rid of repetitive code.

    2) If you generated the code via the macro then you may get some useless code. Get rid of the useless code.

    3) Declare you Objects so that you don't have to keep on repeating them.

    4) Break Up your procedure if need be, and call the 2nd procedure from the 1st.

    5) Avoid using .Select and .Activate they not only make your code slow but also take a lot of space in your code if used extensively.

    Wednesday, July 1, 2015 5:36 AM