Problem with UDF Array Function

If we assume it's possible for weights range to be located on a different sheet object to that of Mcap then:

Function Bucket_Weights(Mcap As Range, Weights As Range, Lower_Bound As Double, Upper_Bound As Double) As Double
    With Mcap
        Bucket_Weights = .Parent.Evaluate("SUM((((" & .Address & ">" & Lower_Bound & ")*(" & .Address & "<" & Upper_Bound & "))^2)*('" & Weights.Parent.Name & "'!" & Weights.Address & "))")
    End With
End Function

If weights is on the same sheet object as Mcap then Weights.Address should suffice.