Expected array when calling function

Question

I'm trying to learn a little VBA an I'm in the process of saving ranges as CSV files.

However, I'm having trouble with this line of code

Call SaveRangeAsCSV(SelectDown("B5:D5"), "C:\Test\test.csv", True)

And here is a snippet of the SelectDown function.

Private Function SelectDown(Range As String) As Range
    SelectDown = Range(Range, ActiveCell.End(xlDown)).Select 
End Function

I get the error: Expected array. I cannot seem to understand what the problem is. Any help would be appreciated :-)

change `Range As String` to `RangeAddress As String` and `SelectDown = Range(Range, ActiveCell.End(xlDown)).Select` to `Set SelectDown = Range(RangeAddress, ActiveCell.End(xlDown))` — Dmitry Pavliv, Mar 24 '14 at 08:23
So far so good, that cleared up the expected array, now I get a Runtime Error 424: object required instead — Nicholas Magnussen, Mar 24 '14 at 08:27
have you removed `.Select` keyword in line `...ActiveCell.End(xlDown)).Select`? — Dmitry Pavliv, Mar 24 '14 at 08:31

score 0 · Answer 1 · answered Mar 24 '14 at 08:25

0

It sounds like the function SaveRangeAsCSV is expecting an array, but you are passing in a Range. This function is not a built-in Excel function, so I can't check it. Maybe you could check what arguments it is expecting?

answered Mar 24 '14 at 08:25

Mark Nash

184
10

Problem is that it's the first argument of that function call that fails, so it's the SelectDown function, and not SaveRangeAsCSV :-) – Nicholas Magnussen Mar 24 '14 at 08:28

score 0 · Answer 2 · answered Mar 24 '14 at 08:34

0

My function now looks like this and is working perfectly.

Private Function SelectDown(RangeAddress As String) As Range
    Set SelectDown = Range(RangeAddress, ActiveCell.End(xlDown))
End Function

answered Mar 24 '14 at 08:34

Nicholas Magnussen

769
2
9
26

1

two posts may be helpful for you: [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) and [How to determine last used row/column](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920). Using `xlDown` is unreliable in some cases – Dmitry Pavliv Mar 24 '14 at 08:35

Expected array when calling function

2 Answers2