I actually found a solution thanks to an earlier post:

Public Function WA(v As Variant, d As Variant) As Double
Dim dsum As Double, dwt As Double
If TypeOf v Is Excel.Range Then
v = v.Value2
End If
If TypeOf d Is Excel.Range Then
d = d.Value2
End If
For i = LBound(d, 1) To UBound(d, 1)
dsum = dsum + v(i, 1) * d(i, 1)
dwt = dwt + d(i, 1)
Next
If dwt < 0 Then
WA = dsum / dwt
Else
WA = CVErr(xlErrDiv0)
End If
End Function

Thanks for your help!