Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to load function in cell (2000/XP/2003)

    I want to create a toolbar with buttons to load commonly used financial functions into cells. I can create the toolbar, but not sure of the VBA syntax to store a function in a cell and then open the window which allows entry of function parameters. I was planning to use

    Activecell.Formula = PMT(A1, B1, C1)

    to load the function, but that's as far as I got.

    Is this possible?

    Thanks!

    Larry

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to load function in cell (2000/XP/2003)

    You're close enough:

    Activecell.Formula="=PMT(A1,B1,C1)"

    Then do this to show the formula wizard:

    Application.Dialogs(xlDialogFunctionWizard).Show
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to load function in cell (2000/XP/2003)

    The Formula property of a cell is a string, so you must enclose it in quotes, and the string must begin with =.
    <code>
    Activecell.Formula = "=PMT(A1,B1,C1)"
    </code>
    If the formula itself contains quotes, you must double them:
    <code>
    ActiveCell.Formula = "=IF(A1=0,""Zero"",""Not zero"")"
    </code>
    will result in
    <code>
    =IF(A1=0,"Zero","Not zero")
    </code>
    To display the function wizard, use
    <code>
    Application.Dialogs(xlDialogFunctionWizard).Show</code>

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to load function in cell (2000/XP/2003)

    Perfect - thank you very much!!!

    Larry

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to load function in cell (2000/XP/2003)

    Hans,

    As always - thank you!

    Larry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •