none
Macro 4.0 problems in Excel 2013 RRS feed

  • Question

  • Using an old macro moving data from one sheet to another gives problems, it a simple macro only using copy and past-special (value only) functions - it working OK in Excel 2010 and earlier.

    When called from the sheet where data are inserted it works ok, when the same macro is called from any other sheet it does not work. i.e. the macro is called from the front page during start up to initiate the system it does not work.

    As a test the initiation button was copied to the active sheet and it was working OK, what can be done to make a permanent working solution?

    Thanks Sigfred

    • Moved by Steve Fan Wednesday, July 29, 2015 3:07 AM
    Tuesday, July 28, 2015 1:17 PM

All replies

  • Hi Sigfred,

    Did you get any error messages about your issue? Please provide the Macro code to us.

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs. Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, July 29, 2015 3:00 AM
  • Hi Sigfred,

    Check if your variables are declared as Public and if not try changing them and also the function/ Sub declaration. If this doesn't work then post the code here along with the error message.


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Wednesday, July 29, 2015 3:45 AM
  • Dear Zhang

    The enclosed code is a simplified example; the actual application is too large to copy, but this small example have the same symptoms.

    If the macro “Move” is activated via the macro “Calculate” started via a push-button on the sheet “Calc” the figures are moved within the sheet “Calc” from C8:C10 to E8:E10 - as they should.

    If macro “Move” is activated via the macro “Initiate” started via a push-button on the sheet “Init”- it will take the values from the sheet “Init” C8:C10 (opposed to "Calc" C8:C10) and move it to "Calc" E8:E10.

    If the lines if the instructions "Activate" and "Protect.document" in the macro are removed for testing then the Initiate function works as is should.

    The actual application is very large and not easy to migrate to VBA, but it works in Excel 2010 without this problem.

    To send an excel sheet is not accepted, so the Macro code is inserted below.

    Thank you Sigfred

    Initiate

    =FORMULA(1,Calc!C5)

    =RUN(Move)

    =HALT()

     

    Calculate

    =FORMULA(Calc!C5+1,Calc!C5)

    =RUN(Move)

    =HALT()

     

     

    Move

    =ECHO(FALSE)

    =ACTIVATE("Calc")

    =PROTECT.DOCUMENT(FALSE,FALSE,FALSE)

     

    =FORMULA.GOTO(Calc!C8:C10)

    =COPY()

    =FORMULA.GOTO(Calc!E8)

    =PASTE.SPECIAL(3,1,FALSE,FALSE)

     

    =PROTECT.DOCUMENT(TRUE,,,TRUE)

    =ECHO(TRUE)

    =RETURN()

    Wednesday, July 29, 2015 12:16 PM
  • Thank you Brian,

    I believe the declarations are  for VBA code, this is written in the old Excel 4.0 Macro langue, and I don't think there are declarations like that in the old language.

    for the code see the above message. 

    Best Regards Sigfred

    Wednesday, July 29, 2015 12:20 PM
  • Sheet protect and unprotect is slow in 2013.

    https://support.microsoft.com/en-us/kb/2996166

    If that's the reason it's not related to your Macro4 code, it'd be the same with any language, though there may be other good reasons to adapt to VBA.


    Wednesday, July 29, 2015 4:01 PM
    Moderator
  • Hi Sigfred,

    I suggest that you upgrade to VBA. It is much more flexible and very powerful!

    Good Luck.


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Wednesday, July 29, 2015 9:00 PM