none
Making Formula RRS feed

  • Question

  • Good Morning Everyone!

    I have recently discovered you can make your own formula in vba that you can type in cells!

    I Was wondering why this isn't working:

    Function Sdate(I)
    Dim D As Variant
    Dim M As Variant
    Dim Y As Variant
    D = Application.WorksheetFunction.Day(I)
    M = Application.WorksheetFunction.Month(I)
    Y = Application.WorksheetFunction.Year(I)
    Sdate = "Day " & D & ", Month " & M & ", Year " & Y
    End Function
    Its more of a learning tool than a practical formula.

    Any tips on writing formula's would also me much appreciated!


    • Edited by Soliddrew Wednesday, March 2, 2016 9:28 AM
    Wednesday, March 2, 2016 9:28 AM

Answers

  • Hi, Soliddrew

    Day, Month and Year are inbuilt function. We can directly use it with only writing Day(), Month(), Year(). No need to write Application.WorksheetFunction.Day(I)

    Here I make some changes in your above mentioned code and now it is working.

    Function Sdate(val)

    Dim D As Variant

    Dim M As Variant

    Dim Y As Variant

     

    D = Day(val)

    M = Month(val)

    Y = Year(val)

    Sdate = "Day " & D & ", Month " & M & ", Year " & Y

    MsgBox Sdate

    End Function

     

    Sub demo()

    Dim val As Variant

    val = Worksheets("Sheet1").Range("A1:A1")

    Sdate (val)

    End Sub

     

    Regards

    Deepak

     

    • Marked as answer by Soliddrew Thursday, March 3, 2016 10:03 AM
    Thursday, March 3, 2016 5:17 AM
    Moderator

All replies

  • Hi again Soliddew,

    I suppose you should define an argument/parameter and returned value of your function "Sdate" like

    "Function Sdate(Byvalue I as Date) as String".

    And the name of argument would be much better, if it represent its meaning.

    Sorry, I've forgot to say...

    Please provide your code that calls function Sdate, like "XX = Sdate(something)".

    Regards.



    • Edited by Ashidacchi Wednesday, March 2, 2016 12:04 PM
    Wednesday, March 2, 2016 11:52 AM
  • Hi, Soliddrew

    Day, Month and Year are inbuilt function. We can directly use it with only writing Day(), Month(), Year(). No need to write Application.WorksheetFunction.Day(I)

    Here I make some changes in your above mentioned code and now it is working.

    Function Sdate(val)

    Dim D As Variant

    Dim M As Variant

    Dim Y As Variant

     

    D = Day(val)

    M = Month(val)

    Y = Year(val)

    Sdate = "Day " & D & ", Month " & M & ", Year " & Y

    MsgBox Sdate

    End Function

     

    Sub demo()

    Dim val As Variant

    val = Worksheets("Sheet1").Range("A1:A1")

    Sdate (val)

    End Sub

     

    Regards

    Deepak

     

    • Marked as answer by Soliddrew Thursday, March 3, 2016 10:03 AM
    Thursday, March 3, 2016 5:17 AM
    Moderator
  • Thankyou =)
    Thursday, March 3, 2016 10:03 AM