Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function (Excel VBA (97))

    If I have Excel perform two different procedures in a single function, how can I get Excel to output the results? For example, if I wanted a range summed or if I wanted the range divided by some number, how can I call the different results at different points in time?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function (Excel VBA (97))

    Is this a Function or a Subroutine? A function can return only one result. Depending on what you are trying to accomplish (your description is not clear on that), you would need to have two function or some flag that the function could check to see if it should sum or divide. If this is a subroutine, it can do either or both and put the results where you want them.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function (Excel VBA (97))

    It is a function and I was wanting everything done all at once or the results to be called at different times. Thanks.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function (Excel VBA (97))

    I don't understand. If you want everything done at once, where do you want the results. If the results are to end up in two different cells, then each cell would have to have a formula calling the Function or two different functions. The only way to return two results to one cell would be to return a string that contained both results.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function (Excel VBA (97))

    Thanks. I was trying to do too much at once.

    How can I enter a month's name and have Excel return the number of days in that particular month?

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function (Excel VBA (97))

    If cell A1 contains a date, then the formula below will give you the number of days in the month that date is in:

    <pre>=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))
    </pre>

    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Function (Excel VBA (97))

    Narrowing down from Legare's answer, if cell A1 only contains a valid month name, and you are ALWAYS assuming the current year, you could use:

    =DAY(DATE(YEAR(NOW()),MONTH(DATEVALUE(A1&" 1, "&YEAR(NOW())))+1,0))

    (This may require that you are using US date formats.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function (Excel VBA (97))

    Thanks.

    Is it possible to do this in VBA? For example, if I have a msgbox ask the user to input a month's name, how can I get VBA to assign the number of days in that month to a specific variable?

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Function (Excel VBA (97))

    Apparently =DAY() is not available from Application.WorksheetFunction, so this is the best I can come up with. Perhaps another Lounger can improve it. Also, I'd recommend that you use a dropdown dialog for the selection of Month and Year so that user spelling errors are avoided. You'll still need to add error handling:

    Sub getdaysinmonth()
    Dim dateEntry As Date, dateEnd As Date
    Dim intDayCount As Integer
    ' in production get the next two constants from user
    Const strMonth As String = "February"
    Const intYear As Integer = 2004

    dateEntry = DateValue(strMonth & " 1, " & intYear)
    dateEnd = DateSerial(Year(dateEntry), Month(dateEntry) + 1, 1) 'actually, the 1st of the next month
    MsgBox dateEnd - dateEntry & " days in " & strMonth & " " & intYear
    End Sub

    As a function taking the month and year as arguments and returning the days:

    Public Function getdaysinmonth(strMonth As String, intYear As Integer) As Integer
    Dim dateEntry As Date, dateEnd As Date
    dateEntry = DateValue(strMonth & " 1, " & intYear)
    dateEnd = DateSerial(Year(dateEntry), Month(dateEntry) + 1, 1)
    getdaysinmonth = dateEnd - dateEntry
    End Function
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function (Excel VBA (97))

    The code below will give you the number of days in the entered month for 2004. It will not be correct for February of non-leap years.

    <pre>Public Sub DaysInMonth()
    Dim strMonth As String
    Dim iDays As Integer
    strMonth = InputBox("Please enter a month.")
    If strMonth <> "" Then
    iDays = Day(DateSerial(Year(DateValue(strMonth & " 1, 2004")), _
    Month(DateValue(strMonth & " 1, 2004")) + 1, 0))
    MsgBox strMonth & " has " & iDays & " Days."
    End If
    End Sub
    </pre>

    Legare Coleman

  11. #11
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function (Excel VBA (97))

    You guys are the best. Thanks.

    Would it be possible to make "year" a variable entry as well? Would this eliminate the non-leap year problem? I was thinking of using something like '& strYear' and replacing everything after the one and comma. In other words,

    ...(strMonth & " 1," & strYear))...

    However, this doesn't seem to work.

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Function (Excel VBA (97))

    If the inputbox asks for month and year in this format: "February, 2004"

    DateValue("February, 2004")

    will return 2/1/2004 (in US date notation), and code will work with that change. My warnings about bad spellers and error checking still remain. You may want to test the user entry with something like:

    strEntry = InputBox
    If IsDate(DateValue(strEntry)) Then
    ... calculate the days
    Else
    MsgBox "No valid month and year were entered"
    End If
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function (Excel VBA (97))

    Try this:

    <pre>Public Sub DaysInMonth()
    Dim strMonth As String
    Dim iDays As Integer
    strMonth = InputBox("Please enter month, year (for example February, 2004).")
    If strMonth <> "" Then
    iDays = Day(DateSerial(Year(DateValue(strMonth)), _
    Month(DateValue(strMonth)) + 1, 0))
    MsgBox strMonth & " has " & iDays & " Days."
    End If
    End Sub
    </pre>

    Legare Coleman

  14. #14
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function (Excel VBA (97))

    Thanks. If I can assume that there may be date entry problems (spelling, etc.), can VBA be used to pull the date directly from the computer's internal clock?

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Function (Excel VBA (97))

    If you want the days in the month from the current system date, that's much easier. Something like:

    daysinthismonth = DateSerial(Year(Date), Month(Date)+1, 1) - DateSerial(Year(Date), Month(Date), 1)
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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