Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    use inbuilt vba functions in spreadsheet (2003)

    Hi there

    I'm trying to write a formula that uses the vba function month() in a spreadsheet. I'm pretty sure that I can do this by prefixing the function name, but I can't remember with what. So I'd like to write a formula something like =month(G2) in a spreadsheet that will return 1 if it is jan, 2 if it is feb etc.

    Any help with this would be much appreciated

    Amanda

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

    Re: use inbuilt vba functions in spreadsheet (2003)

    Excel has worksheet functions DAY, MONTH and YEAR, so you don't need to use VBA functions for this.
    If cell G2 contains a date, the formula =MONTH(G2) will return the month number of that date.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use inbuilt vba functions in spreadsheet (2003)

    Thanks Hans,

    If I did want to use a vba function that was not also an excel function, would I need to prefix it?

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

    Re: use inbuilt vba functions in spreadsheet (2003)

    If you want to use a built-in VBA function in a worksheet, you have to create a custom VBA function in order to use it in a worksheet formula. If you store such a function in your personal macro workbook, you must prefix the function name with Personal.xls!

    For example, there is no direct Excel equivalent of the VBA function DateAdd. You could create a custom function:

    Public Function MyDateAdd( _
    strInterval As String, _
    dblNumber As Double, _
    dtmDate As Date)
    MyDateAdd = DateAdd(strInterval, dblNumber, dtmDate)
    End Function

    If you store this function in a module in the workbook in which you use it, you can create a formula like this:

    =MyDateAdd("m",3,TODAY())

    Or

    =MyDateAdd(A1,B1,C1)

    If you store the function in your personal macro workbook, you'd use

    =Personal.xls!MyDateAdd("m",3,TODAY())

    Or

    =Personal.xls!MyDateAdd(A1,B1,C1)

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use inbuilt vba functions in spreadsheet (2003)

    Thanks Hans

Posting Permissions

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