Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Run a macro in personal.xls

  1. #1
    Join Date
    12th August 2006
    Location
    England
    Posts
    14

    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 :
    VB:
    Call InsertRows 
    
    
    and get a Compile Error: Sub or Function not defined.

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

    VB:
    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 at 07:55. Reason: additional info

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,540

    Re: Run a macro in personal.xls

    try...
    VB:
    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 at 09:48.
    _______________________________________________
    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

    _______________________________________________

  3. #3
    Join Date
    12th August 2006
    Location
    England
    Posts
    14

    Re: Run a macro in personal.xls

    Thanks ger, but it still does not work.

    The macros I am using are:

    in personal.xls:
    VB:
    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:
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    Re: Run a macro in personal.xls

    your procedure in Personal XLS reads exactley : Insertrowswithformulas

    Should then work, check again

    jiuk

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th August 2006
    Location
    England
    Posts
    14

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

  7. #7
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,540

    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....

    VB:
    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
    _______________________________________________
    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

    _______________________________________________

  8. #8
    Join Date
    12th August 2006
    Location
    England
    Posts
    14

    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:

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

    Dave
    Last edited by d4v3; August 14th, 2006 at 08:54.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: Run a macro in personal.xls

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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Import Macro To Personal.xls
    By lmerrill in forum EXCEL HELP
    Replies: 8
    Last Post: May 24th, 2007, 21:24
  2. Replies: 14
    Last Post: April 3rd, 2007, 04:57
  3. Personal Macro Book
    By farhan_babu in forum EXCEL HELP
    Replies: 2
    Last Post: November 15th, 2005, 14:46
  4. Personal Macro WB
    By Neiluk66 in forum EXCEL HELP
    Replies: 2
    Last Post: September 22nd, 2005, 18:16
  5. Running a Personal.xls Macro
    By workfrustration in forum EXCEL HELP
    Replies: 1
    Last Post: July 16th, 2004, 22:02

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
  •  
porno