2

VBA newbie here. I am trying to pass an array (it is static, but please answer for dynamic range as well) to a function. Then assign individual array elements to unique variables and use these variables in a custom formula. I just browsed around and wrote the code but keep getting #VALUE! error. The gist of the code is below:

Public Function mytest(ByRef arr1 As Range)
Dim A As Double
Dim B As Double

A = arr1(0)
B = arr1(1)

mytest = A + B 'The actual formula is a bit more complicated than simple addition
End Function

I am not sure what am i doing wrong at all. If anyone has a solution, can you please explain why it works as well. I appreciate any and all help I can get. Many thanks !

Improve this question
2
  • 1
    A range is not an array. Do you want your function to accept worksheet ranges or VBA arrays, or both? Commented May 5, 2020 at 10:29
  • Hi John, for now I am looking to pass worksheet ranges to the function. Sorry for the confusion. BTW I also tried ByRef arr1() As Double. still got the #VALUE! error. Commented May 5, 2020 at 10:37

3 Answers 3

Reset to default
2

As Coleman pointed out a range is not an array, consider:

Public Function mytest(ByRef arr1 As Range)
    Dim A As Double
    Dim B As Double

    A = arr1(1, 1)
    B = arr1(2, 1)

    mytest = A + B 'The actual formula is a bit more complicated than simple addition
End Function

enter image description here

NOTE:

  • we treat the Range similar to an array
  • it is two dimensional
  • it is 1 based
  • if you are only dealing with the Range's value, you could create an internal array within your function that directly maps to the passed Range.
  • if the Range is truly dynamic, (like a Spill range) then all you need to pass is the anchor cell.
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

It is good to be explicit about the 1-based nature of range indexing, which trips up a lot of programmers coming to VBA from a C-like language
2

You seem to be trying to use a worksheet range as a 0-based array. That doesn't really make sense although using the Cells property of a range (which you are actually trying to do implicitly) you can come close:

Public Function mytest(arr1 As Range)
    Dim A As Double
    Dim B As Double

    A = arr1.Cells(1)
    B = arr1.Cells(2)

    mytest = A + B 'The actual formula is a bit more complicated than simple addition
End Function

In the above code, you can drop Cells() since it will function as the default property here, but most experienced VBA programmers like to make explicit what property they are using.

This will more or less work for 1-dimensional ranges but might not work as expected with 2-dimensional ranges. Cells takes up to 2 indices and in general I think that the code is clearer when you are explicit about the full indices (e.g. A = arr1.Cells(1,1) and B = arr1.Cells(2,1)).

Improve this answer

6 Comments

Thanks John, this worked as well. This will be huge help ! I think it will still take me some practice to really get the differences between arrays and ranges. Just to followup, when I use ByRef arr1() as Double, can I still use the above code in the same way? What is the essential difference?
@anirudhkaushik you cannot, Double = number, numbers don't have coordinates. when you give a range you give an array of cells, when you give a number, it's just that.
@JohnColeman I was answering to anirudh's comment asking what happens if he uses Double instead of Range as parameter.
No problem John ;)
Sorry @anirudhkaushik, you are correct. Though that could give you errors if there is text on the cell, or your values have decimals, which the Double will ignore. If you mean to give back a Double as result for your function, the syntax is: Public Function mytest(arr1 As Range) As Double
|
2

The question isn't in the code you posted but in the procedure that calls it. Here the calling procedure first assigns values to the cells in the worksheet (for testing purposes), then passes the range to the function which extracts the values into an array and then uses that array to calculate a return value.

Private Sub TestmyTest()

    Dim Rng1 As Range

    Cells(1, "A").Value = 3.14
    Cells(2, "A").Value = 3
    Set Rng1 = Range("A1:A2")

    Debug.Print myTest(Rng1)
End Sub

Function myTest(Rng1 As Range) As Double
    ' procedures are 'Public' unless declared as 'Private'
    ' Therefore only declare "Private" or nothing
    ' Arguments are passed ByRef unless they are declared as 'ByVal'
    ' Therefore I recommend to omit "ByRef"

    Dim Arr As Variant
    Dim A As Double
    Dim B As Double

    ' this creates a 1-based 3-D array of 2 row and 1 column
    Arr = Rng1.Value

    A = Arr(1, 1)
    B = Arr(2, 1)

    myTest = A + B 'The actual formula is a bit more complicated than simple addition
End Function
Improve this answer

2 Comments

Thanks Variatus ! Honestly speaking, many of the concepts/logic are still difficult for me to grasp. I have no real coding background and most of what I know is self learnt through online tutorials. But I will practice more and hopefully all of yours' answers will make more sense to me down the line.
Friendly hint to comment in function myTest(): "this creates a 1-based 3-D array of 2 row and 1 column" - this creates a 1-based 2-D array :-;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.