CODEWITHSUNDEEP
arraysexcelvba
Miguel Ortiz
Miguel Ortiz

Reputation: 87

How can I convert multi-dimensional array into a date variableand use month function?

I have included in my comments where the program fails. Your help would be greatly appreciated! Thank you!

Function sumByAcct(month As Integer, acct As String) As Long
    Dim array_length As Integer

    array_length = Application.WorksheetFunction.Count(Sheets("Transactions").Range("A:A")) + 1

    ReDim array_data(array_length, 3) As Variant
    For i = 0 To (array_length)
        array_data(i, 1) = Sheets("Transactions").Range("A" & i + 2) '=> Dates
        array_data(i, 2) = Sheets("Transactions").Range("E" & i + 2) '=> Amounts
        array_data(i, 3) = Sheets("Transactions").Range("H" & i + 2) '=> Bank Account

        Dim func_date As Date
        Dim func_month As Integer
        func_date = CDate((array_data(i, 1))) 'program doesn't run this code
        func_month = month(CDate(func_date)) 'program doesn't run this code

        MsgBox (func_date)
    Next
End Function

Upvotes: 1

Views: 51

Answers (1)

Comintern
Comintern

Reputation: 22195

The fact that month isn't capitalized is a dead give-away. You have a naming collision between your input parameter and the VBA function name.

Either change your parameter name:

Function sumByAcct(monthNum As Integer, acct As String) As Long

Or explicitly reference the Month function:

func_month = VBA.Month(CDate(func_date))

You should probably also toss an Option Explicit at the top of your module and then follow up with Dim i As Long.

The line func_date = CDate((array_data(i, 1))) is fine assuming that all of the values it finds in column A are dates. I'd either check the data itself or test the value before trying to cast it.

Upvotes: 1

Related Questions