Announcement

Collapse
No announcement yet.

Run a macro in personal.xls

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Run a macro in personal.xls

    I have a macro to insert rows and add formatting in personal.xls, which will run from another worksheet when using alt+f8 with no problems.
    However, I am trying to run it from a macro in the second worksheet and have tried :
    Code:
    Call InsertRows
    and get a Compile Error: Sub or Function not defined.

    Code:
    Application.Run "insertrows"
    and get a Runtime Error '1004': the macro '...' can not be found.

    Code:
    Application.Run "Personal.xls!insertrows"
    and get no error message, but the rows are not inserted.

    If I copy the Sub code into the new worksheet, and then use Call, everything acts as it should.

    Is the third method the correct way of calling the macro, and if so, what would cause it not to do what it is supposed to do?
    Last edited by d4v3; August 12th, 2006, 07:55. Reason: additional info
    Trying to learn something new every day.

  • #2
    Re: Run a macro in personal.xls

    try...
    Code:
    Sub test()
    
    Run "Personal.xls!Macro1"
    
    End Sub
    this worked for me (after about 10 minutes playing with brackets and inverted commas )

    This worked for me with a simple msgbox in Macro1. Youre third option below is pretty similar and I would be surprised if the synthax of the run was incorrect. It is more than likely a bug in the macro that is called.

    To check this, put a break point in the first line of code in the macro being called and run again. If it stops at the breakpoint then you know the calling routine is correct and the macro isnt behaving as expected - on that note - be careful with references to activesheet and activerow in macros in Personal.xls, because it might actually be referring to the activesheet in Personal.xls (personal.xls does actually contain one sheet). Ditto for the active row. I'm not 100% sure about the last two sentences, but it is worth keeping in mind to make sure your sheet references and row references are correct and specific.

    If youre stumped post back the insert row macro and we'll have a gander at it for you.

    In the mean time I'm off to bed.

    HTH
    ger
    Last edited by Ger Plante; August 12th, 2006, 09:48.

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

    Comment


    • #3
      Re: Run a macro in personal.xls

      Thanks ger, but it still does not work.

      The macros I am using are:

      in personal.xls:
      Code:
      Option Explicit
      
      Sub InsertRowsWithFormulas_caller()
           Call InsertRowsWithFormulas
      End Sub
       
      Sub InsertRowsWithFormulas(Optional vRows As Long = 0)
      
          ActiveCell.EntireRow.Select  'So you do not have to preselect entire row
         
          If vRows = 0 Then
              vRows = Application.InputBox(prompt:="How many rows do you want to add?", _
                  Title:="Add Rows", Default:=1, Type:=1) 'Default for 1 row, type 1 is number
          End If
          If vRows = False Then Exit Sub
          
          Selection.Resize(rowsize:=2).Rows(2).EntireRow.Resize(rowsize:=vRows) _
                      .Insert Shift:=xlDown
              
          Selection.AutoFill Selection.Resize(rowsize:=vRows + 1), xlFillDefault
              
          On Error Resume Next
          Selection.Offset(1).Resize(vRows).EntireRow.SpecialCells(xlConstants) _  
                      .ClearContents
      
      End Sub
      This is a cut down version of InsertRowsAndFillFormulas found here
      http://www.mvps.org/dmcritchie/excel/insrtrow.htm (thanks to D McRitchie et al).

      The macro I use to call it from another worksheet is:
      Code:
      Option Explicit
      
      Sub test()
           Run "personal.xls!Insertrowswithformulas(1)"
      End Sub
      From ALT+F8, if I run the caller in personal.xls, the macro works as it should.
      If I run Test, the macro runs twice (? - I put a different Msgbox on each blank line and each one appeared twice) and no lines are inserted.

      Any ideas?

      Dave
      Trying to learn something new every day.

      Comment


      • #4
        Re: Run a macro in personal.xls

        your procedure in Personal XLS reads exactley : Insertrowswithformulas

        Should then work, check again

        jiuk

        Comment


        • #5
          Re: Run a macro in personal.xls

          Hi jiuk

          Thanks - I have now narrowed the problem down.

          If I call 'InsertRows...' the macro runs and asks me how many rows to insert, and does as it is told.

          However I have been calling 'InsertRows...(1)' to automatically insert one row - but this does not work, even though it does set vRows to 1 in the personal macro.

          How should I pass a constant to a macro I am calling?

          Thanks

          Dave
          Trying to learn something new every day.

          Comment


          • #6
            Re: Run a macro in personal.xls

            Record a macro running the Procedure from Excel.

            Comment


            • #7
              Re: Run a macro in personal.xls

              Hi Dave, the odd thing is, when you go to run the macro from Excel (ALT+F8), the macro doesnt exist - it doesnt show in the list at all. But when you remove the parameter list from the sub header, it will appear in the list of Macros to run. Which begs the question - can you send a constant to a SUB macro when running from Excel (sending it to a UDF function is easy enough obviously).

              Anyway... d4v3 - after a bit of playing around, the following code seems to work for me....

              Code:
              Sub test()
                  Dim result As Variant
                  result = Run("personal.xls!InsertRowsWithFormulas", "1")
              End Sub
              The run function, takes the arguements of "Macro Name", followed by Parameters for the Macro, in this case, the number 1... each parameter seperated by a comma if you want to send in more to the called sub.

              HTH
              Ger

              Check out our new reputation system. Click on the "star" under the post!
              _______________________________________________

              There are 10 types of people in the world. Those that understand Binary and those that dont.

              Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

              The BEST Lookup function of all time

              Dynamic Named Ranges are your bestest friend

              _______________________________________________

              Comment


              • #8
                Re: Run a macro in personal.xls

                Hey, thanks a lot ger, this is just what I was looking for!

                : D

                After some messing around, I found the following also works:

                Code:
                Run "Personal.xls!InsertRowsWithFormulas", 1
                Thanks again

                Dave
                Last edited by d4v3; August 14th, 2006, 08:54.
                Trying to learn something new every day.

                Comment


                • #9
                  Re: Run a macro in personal.xls

                  Thanks Ger, didn't realize there were arguments to be passed.

                  Comment

                  Working...
                  X