How to input a range object to a function and return an array in VBA?

Question

I have a procedure which generates cell range depending on certain criteria. I also have a function which takes a cell range object as input and returns an array. However, if I call the function within a procedure, I get this error. "Compile error: Can't assign to array" Is there something wrong with my syntax? Thanks.

Option Explicit

'Reads a database table from current Active worksheet and paste to output sheet

Function DBReader(DBMarker As Range) As String
    Dim arr(5) As String
    'Read data
    arr(0) = ActiveWorksheet.DBMarker.Value              
    arr(1) = ActiveWorksheet.DBMarker.Offset(1, 1).Value 
    arr(2) = ActiveWorksheet.DBMarker.Offset(2, 1).Value 
    arr(3) = ActiveWorksheet.DBMarker.Offset(0, 2).Value 
    arr(4) = ActiveWorksheet.DBMarker.Offset(1, 2).Value 
    arr(5) = ActiveWorksheet.DBMarker.Offset(2, 2).Value 
    DBReader = arr()
End Function

The procedure is kinda long, so I'll only show relevant lines

Public Sub LogSum()
    'Declare variables
    ...
    Dim DBInfo(5) As String         'Stores info from function

    ...
    Set CellDB = bookLOG.Worksheets(j).UsedRange.Find("Ref:", LookIn:=xlValues)
    ...
    DBInfo = DBReader(CellDB)       'Returns Array from function???
    ...
End Sub

DBReader = arr() doesn't really make sense since you declared DBReader() to return a string, not an array of strings. Also -- the name of arr is arr, not arr(). — John Coleman
– John Coleman, Commented Jan 29, 2018 at 3:25
How to I declare DBReader() to return an array of strings? I was basing my syntax from this example: geeksengine.com/article/vba-function-multiple-values2.html I simply changed the string type with variant type. — schrodingerscat
– schrodingerscat, Commented Jan 29, 2018 at 4:27

Subodh Tiwari sktneer · Accepted Answer · 2018-01-29 05:28:01Z

2

Give this a try...

Function DBReader(DBMarker As Range) As Variant
    Dim arr(5) As String
    'Read data
    arr(0) = DBMarker.Value
    arr(1) = DBMarker.Offset(1, 1).Value
    arr(2) = DBMarker.Offset(2, 1).Value
    arr(3) = DBMarker.Offset(0, 2).Value
    arr(4) = DBMarker.Offset(1, 2).Value
    arr(5) = DBMarker.Offset(2, 2).Value
    DBReader = arr()
End Function

Public Sub LogSum()
    'Declare variables

    Dim CellDB As Range
    Dim DBInfo() As String         'Stores info from function

    Set CellDB = bookLOG.Worksheets(j).UsedRange.Find("Ref:", LookIn:=xlValues)
    DBInfo = DBReader(CellDB)       'Returns Array from function???
End Sub

answered Jan 29, 2018 at 5:28

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

schrodingerscat Over a year ago

Thanks, @sktneer. This works as well, but I prefer declaring the DBreader using As String() as suggested by K. Davis. The reason is that I am sure that my output will be a string, so there is no need for a variant type.

K.Dᴀᴠɪs · Accepted Answer · 2018-01-29 04:42:18Z

1

So, there's a couple of issues you need to look at.

First, let's start with your subroutine.

You are declaring an array with 6 elements before you assign it. Let's leave that blank by changing Dim DBInfo(5) to Dim DBInfo()

You should also add a check in your subroutine to make sure you don't have an empty object for CellDB. After that line add something like:

If CellDB Is Nothing Then...

And create a rule for what happens when it's Nothing.

Public Sub LogSum()
    'Declare variables
    ...
    Dim DBInfo() As String

    ...
    Set CellDB = bookLOG.Worksheets(j).UsedRange.Find("Ref:", LookIn:=xlValues)
    ...
    DBInfo = DBReader(CellDB)
    ...
End Sub

Next, we can look at your function itself. At the end of your declaration As String, you are wanting to pass it off as an array so you could designate as an array by using As String().

Also, the syntax of your line DBReader = arr() is done incorrectly. You should just keep it as DBReader = arr.

Function DBReader(DBMarker As Range) As String()
    Dim arr(5) As String
    'Read data
    arr(0) = ActiveWorksheet.DBMarker.Value
    arr(1) = ActiveWorksheet.DBMarker.Offset(1, 1).Value
    arr(2) = ActiveWorksheet.DBMarker.Offset(2, 1).Value
    arr(3) = ActiveWorksheet.DBMarker.Offset(0, 2).Value
    arr(4) = ActiveWorksheet.DBMarker.Offset(1, 2).Value
    arr(5) = ActiveWorksheet.DBMarker.Offset(2, 2).Value
    DBReader = arr
End Function

Also, in general, it's not advised that you use ActiveWorksheet by instead declaring your worksheets. You can pass this off to your function by adding another argument like:
Function DBReader(DBMarker As Range, ws As WorkSheet)...

Will your function still work? Probably.
Do you want to have to debug it later on when you start using it for other worksheets? Doubtful.
Save yourself a headache now so you don't have to have unnecessary debugging later.

Lastly, I am not entirely sure of your intention with DBInfo, but that is a one-dimensional array. This means that if you were to paste this array to your worksheet that it would come out horizontal unless you were to transpose it. You can make it easier on yourself now by making it a 2-D array.

edited Jan 29, 2018 at 4:42

answered Jan 29, 2018 at 4:29

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

2 Comments

schrodingerscat Over a year ago

Thanks for the reply. Questions on several points: [1] Why is there a need to change Dim DBInfo(5) to Dim DBInfo()? I am sure that I will need only 1x6 array. I thought it was always best to preallocate arrays if possible (PS. I grew up with FORTRAN). [2] Yes, that check is added, but I did not show for brevity. In fact, I added the check because I had an error already prior to this post. [3] I would like to avoid using active worksheet as much as possible. Unfortunately, if I add another argument in the function, I get this error: Compile error: Method or data member not found

schrodingerscat Over a year ago

This is my code for adding the worksheet as argument to my function. Function DBReader(DBMarker As Range, ws As Worksheet) As String() and in the subroutine, I call the function using this line DBInfo = DBReader(CellDB, bookLOG.Worksheets(j)) I iterate across different workbooks and all their sheets, so I had to use an index to reference the worksheet. [4] For the DBInfo, my intention is to have several outputs with one function. I checked one site and the suggestion is to use arrays to make a function output several variables at once.

iHazCode · Accepted Answer · 2018-01-29 03:23:06Z

1

I suspect the problem is that you are trying to set your function equal to the array you just populated in the final line of your DBReader function.

DBReader = arr() may be failing since DBReader is a function, not an array.

answered Jan 29, 2018 at 3:23

iHazCode

7715 silver badges15 bronze badges

1 Comment

schrodingerscat Over a year ago

With that line you mentioned, I was trying to pass the array value as DBReader function output. I am basing the syntax from this example: geeksengine.com/article/vba-function-multiple-values2.html

Collectives™ on Stack Overflow

How to input a range object to a function and return an array in VBA?

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related