CODEWITHSUNDEEP
excelvbacompiler-errorsdatediff
user28509822
user28509822

Reputation: 21

VBA - why am i getting the following error for my code? - "compile error: expected array"

so I have a bit of code that should check the dates in columns AR and AS for each line. Then calculate the difference and populate in column AV. However I am getting a compile error on Datediff for some reason. Anyone can point to the obvious error in the below code?

Sub Button2_Click() 
Dim ws As Worksheet 
Dim lastRow As Long 
Dim i As Long 
Dim dateAR As Date 
Dim dateAS As Date 
Dim dateDiff As Long

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Report")

' Find the last row with data in column AR or AS
lastRow = ws.Cells(ws.Rows.count, "AR").End(xlUp).Row
If ws.Cells(ws.Rows.count, "AS").End(xlUp).Row > lastRow Then
    lastRow = ws.Cells(ws.Rows.count, "AS").End(xlUp).Row
End If

' Loop through each row from 10 to lastRow
For i = 10 To lastRow
    If IsDate(ws.Cells(i, "AR").Value) And IsDate(ws.Cells(i, "AS").Value) Then
        dateAR = ws.Cells(i, "AR").Value
        dateAS = ws.Cells(i, "AS").Value
        ' Calculate the difference between the dates in AR and AS
        dateDiff = dateDiff("d", dateAR, dateAS)
        ' Put the date difference in column AV
        ws.Cells(i, "AV").Value = dateDiff
    End If
Next i

End Sub

Upvotes: 1

Views: 52

Answers (1)

FunThomas
FunThomas

Reputation: 29586

As already written in the comments: Your problem is that you declare a variable dateDiff (as a single Long value) but later try to call a function with the same name.

Now when the code hits the statement dateDiff = dateDiff("d", dateAR, dateAS), the VBA runtime tries to evaluate the term dateDiff("d", dateAR, dateAS). For that, it needs to figure out what dateDiff is, and this done from "inner" to "outer" scope. To put it simple, it will

  • Look into the current routine (procedure or function) for a variable definition
  • Look into the module for a global variable or routine
  • Look into other modules of the project for a global variable or a public routine.
  • Look for anything build in (Application or VBA specific).

In your case, it finds the local variable dateDiff and will try to use it. The command continues with (. As dateDiff was identified as variable, this can only mean the following pieces specify an index of an array. But dateDiff is a single variable, not an array, thus you get the compiler error "expected array".

Just don't name your variable dateDiff so that VBA can distinguish the variable from the function you want to call. Or, if you insist to keep this name, "qualify" the function name: DateDiff is a function that is build into VBA (so it's not Excel-specific), you can write VBA.DateDiff(...):

dateDiff = VBA.DateDiff("d", dateAR, dateAS)

This will tell VBA that on the left side of the statement, you want to use the variable while on the right side, you want to access the VBA function.

Upvotes: 4

Related Questions