Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Array Range (Excel 97)

    Hi all, need some help on this one, I'm trying to figure out how to set a range of times, in A1, this is what I have, can anyone help me?

    Option Explicit
    Public Sub arrTS()
    Dim arrTSFL(1 To 10) As Range
    Dim i As Long
    For i = 1 To 5
    Set arrTS(i) = Range("A1" & i)
    Next i
    End Sub

    Public Function getarrTS() As Date()
    Dim getarrTS() As Date
    Dim i As Integer
    i = 0
    ReDim Preserve arrTS(i)
    arrTS(i) = 10:00
    i = i + 1
    ReDim Preserve arrTS(i)
    arrTS(i) = 10:15
    i = i + 1
    ReDim Preserve arrTS(i)
    arrTS(i) = 10:30
    i = i + 1
    ReDim Preserve arrTS(i)
    arrTS(i) = 10:45
    i = i + 1
    ReDim Preserve arrTS(i)
    arrTS(i) = 11:00
    i = i + 1
    getarrTS = arrTS(i)

    End Function


    Thank you

    darryl.

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

    Re: Array Range (Excel 97)

    To specify times in code, for example 10:15, use either TimeSerial(10, 15, 0) or TimeValue("10:15"). If you type TimeSerial or TimeValue in the Visual Basic Editor and press F1, you''ll get information about the function.

    I don't understand what your code is trying to do.

  3. #3
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Array Range (Excel 97)

    Thanks Hans,

    All I want to do is place those times in a range on sheet.

    Starting at A1

    Maybe I'm making this too complicated.

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

    Re: Array Range (Excel 97)

    Filling a range of cells with time values can be done like this:

    Dim i As Long
    Dim t As Date

    ' Initial time value
    t = TimeSerial(10, 0, 0)

    For i = 1 To 10
    Range("A" & i).Value = t
    ' Add 15 minutes to time
    t = DateAdd("n", 15, t)
    Next i

  5. #5
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Array Range (Excel 97)

    Thank you very very much Hans.

  6. #6
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Array Range (Excel 97)

    Hans,

    Could you please explain to me the what "N" stands for?

    Thanks
    Darryl.

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

    Re: Array Range (Excel 97)

    From the online help for DateAdd (you can get it by clicking in DateAdd in your code and pressing F1):

    DateAdd(interval, number, date)

    The interval argument has these settings:

    <table border=1 cellpadding=3><td align=center>Setting</td><td align=center>Description</td><td>yyyy</td><td>Year</td><td>q</td><td>Quarter</td><td>m</td><td>Month</td><td>y</td><td>Day of year</td><td>d</td><td>Day</td><td>w</td><td>Weekday</td><td>ww</td><td>Week</td><td>h</td><td>Hour</td><td>n</td><td>Minute</td><td>s</td><td>Second</td></table>
    As you see, the "n" stands for minutes; "m" was already used for months.

Posting Permissions

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