Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. Loading an array in a custom user function... [modified]

Loading an array in a custom user function... [modified]

Scheduled Pinned Locked Moved Database
data-structures
1 Posts 1 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • N Offline
    N Offline
    new_phoenix 0
    wrote on last edited by
    #1

    I am working on a customized user function in MS Excel that breaks up the calendar into thirteen 28-day periods. The objective is to have as many 28 day periods in a row as possible. The standard monthly calendar consists of months in which some months have 30 days, while other months have 30 days, and occasionally there is a leap year with 28 days in February. However, for the month of December in the years 2009 and 2015, there are 35 days instead of 28 days. In a spreadsheet, place the date 12/30/2007 and then pull the drag handle down so that the last cell is 12/31/2016. To the right of each of these cells is the place where the function cell result should be placed. Now we need to create the place to add the code for a customized user function. This can be accomplished by pressing the Alt-F11 at the same time. A window opens in which code can be written for the function. Select "Insert" > "Module". Now there is a place to put the code. Here is the code for the custom user function:

    Option Explicit

    Public Function ZodiacPeriod(dteInputDate)
    Dim dteStart As Date
    Dim dteEnd As Date
    Dim intDateCount As Integer
    Dim intRow As Integer
    Dim intColumn As Integer
    Dim intX As Integer
    Dim intCounter As Integer
    Dim intYear As Integer
    Dim arrArray(3290, 1)

    dteStart = #12/30/2007#
    dteEnd = #12/31/2016#
    intDateCount = DateDiff("d", dteStart, dteEnd)
    
    For intRow = 0 To intDateCount
        arrArray(intRow, intColumn) = dteStart
        dteStart = dteStart + 1
    Next
    
    intX = 1
    intCounter = 1
    
    For intRow = 0 To 3290
        arrArray(intRow, 1) = intX
        If (Year(arrArray(intRow, 0)) = 2009) Or \_
            (Year(arrArray(intRow, 0)) = 2015) Then
            If (Month(arrArray(intRow, 0)) = 12) Then
                If intCounter <= 35 Then
                    If intX >= 14 Then
                        intX = 1
                    End If
                    intCounter = intCounter + 1
                Else
                    intX = intX + 1
                    intCounter = 1
                End If
            Else
                If intCounter <= 27 Then
                    If intX >= 14 Then
                        intX = 1
                    End If
                    intCounter = intCounter + 1
                Else
                    intX = intX + 1
                    intCounter = 1
                End If
            End If
        Else
    
    1 Reply Last reply
    0
    Reply
    • Reply as topic
    Log in to reply
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes


    • Login

    • Don't have an account? Register

    • Login or register to search.
    • First post
      Last post
    0
    • Categories
    • Recent
    • Tags
    • Popular
    • World
    • Users
    • Groups