Simple VBA Array formula question

Salat23 · Jun 1, 2011

Hello,

I am very new to VBA and I ran into problems regarding one very simple array to array function that I need to write. The purpose of the function is to get an array as argument and return its values in function [FONT=&quot]f(x) = x^3 + 5/6x + 3 to the array of same size. After trying to find out how to do this, I developed the following simple code:
[/FONT]

Code:

Public Function TestFunction(values As Range)
Dim i As Integer
Dim res() As Variant
Dim n As Integer

n = values.Count

ReDim res(n)

For i = 1 To n
res(i) = values(i) ^ 3 + 5 / 6 * values(i) + 3
Next i

TestFunction = res()

End Function

[FONT=&quot]

My problem is that this code enters the function value of the first cell in the function argument into all cells of the given resulting array. When I tried to put "values(2)" instead of "values(i)", it takes the values of the function of the second cell into all of them etc.. (It also does the same for the "values(i+1)").

What am I doing wrong? I thought that the value of "i" changes every time the process "Next i" is triggered and will go from 1 to the number of cells in the array.

Thank you very much for any answers.
[/FONT]

xenou · Jun 1, 2011

Since you are working with a range argument (passed into your function) this is a somewhat unique type of object, which is not the same as a simple array of values.

Note that a range is always one-based, so we will dim the arrays as one-based in order to keep things in "synch".

It would probably be easiest to access the values of the range through its cells properties:

Code:

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Function[/COLOR] TestFunction1(rng [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] res() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]

    n = rng.Count
    
    [COLOR="Navy"]ReDim[/COLOR] res(1 [COLOR="Navy"]To[/COLOR] n)
    
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] n
    res(i) = (rng.Cells(i) ^ 3) + (5 / (6 * rng.Cells(i))) + (3)
    [COLOR="Navy"]Next[/COLOR] i
    
    TestFunction1 = res()

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

However, another option would be to move the range values into an array first, then your code would not change much. Again we used one-based arrays since this is how Excel will understand the range best and by default what an array of a range's values will be when we drop it into an array.

Code:

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Function[/COLOR] TestFunction2(rng [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] res() [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] values [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]

    values = rng.Value
    [COLOR="Navy"]ReDim[/COLOR] res(1 [COLOR="Navy"]To[/COLOR] UBound(values, 1))
    
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] n
    res(i) = (values(i) ^ 3) + (5 / (6 * values(i))) + (3)
    [COLOR="Navy"]Next[/COLOR] i
    
    TestFunction2 = res()

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

Salat23 · Jun 1, 2011

Thank you. Unfortunately neither of these functions seems to work. The TestFunction1 works exactly the same as TestFunction and the TestFunction2 returns an array of zeros.

Here is the file that I am trying on: http://dl.dropbox.com/u/4272906/Question.xls - collumn B is the input range, collumn C is how the result should look like (except it should be in array) and collumns D to F are results of the three functions already discussed.

As to your reply, is there any way how I can assign the input variable into normal array and not the unusual range type of object? As I am very new to VBA I do not have an idea how to do it in the way that this function would work. I tried several ways how to compute it but all of those caused me a #VALUE error when I put the function into the array result.

xenou · Jun 1, 2011

I'm not very familiar with using arrays returned from functions. It's likely that the array you return must be two-dimensional if it's being assigned directly to a range as the result of a formula. So, for instance, if you have 5 input values in one column, your return array must be 5 (rows) by 1 (column) or dimensioned to (5,1).

You could try this (I'm not sure if it will work - not at Excel right now):

Code:

Public Function TestFunction1(rng As Range)
Dim i As Integer
Dim res() As Variant
Dim n As Integer

    n = rng.Count
    
    ReDim res(1 To n, 1)
    
    For i = 1 To n
    res(i, 1) = (rng.Cells(i) ^ 3) + (5 / (6 * rng.Cells(i))) + (3)
    Next i
    
    TestFunction1 = res()

End Function

It assumes you are correctly working with array functions on the worksheet, and that we can even do this at all. This function assumes an input range of n rows all in a single column.

shg · Jun 1, 2011

Change the formula to =TRANSPOSE(TestFunction(B5:B10))

shg · Jun 1, 2011

Or you could leave the formula as it is, and change the function to

Code:

Public Function TestFunction(rInp As Range) As Variant
    Dim sAdr As String
    
    sAdr = rInp.Address(False, False)
    TestFunction = Evaluate("if(row(), " & sAdr & "^3 + 5 / (6 * " & sAdr & ") + 3)")
End Function

Note that it returns an error for an input value of 0.

Salat23 · Jun 1, 2011

shg said:
Change the formula to =TRANSPOSE(TestFunction(B5:B10))

Excelent! Now is there a way how to do this in the code so that in the sheet only the TestFunction(B5:B10) appears? I tried to do it by both "res() = Application.Transpose(res())" and "res() = WorksheetFunction.Transpose(res())" and neither option seems to be working, they both return the #VALUE error.

shg · Jun 1, 2011

One way:

Code:

Public Function TestFunction1(rInp As Range)
    Dim n         As Long
    Dim m         As Long
    Dim avdRes    As Variant
    Dim i         As Long
    Dim j         As Long
 
    With Application.Caller
        n = .Rows.Count
        m = .Columns.Count
    End With
 
    With rInp
        If .Rows.Count < n Then n = .Rows.Count
        If .Columns.Count < m Then m = Columns.Count
        ReDim avdRes(1 To n, 1 To m)
 
        For i = 1 To n
            For j = 1 To m
                If .Cells(i, j).Value2 <> 0 Then
                    avdRes(i, 1) = .Cells(i, j) ^ 3 + 5 / (6 * .Cells(i, j)) + 3
                End If
            Next j
        Next i
    End With
 
    TestFunction1 = avdRes
End Function

Salat23 · Jun 1, 2011

Thank you very much shg, that is exactly what I needed

.

Simple VBA Array formula question

Salat23

New Member

Excel Facts

xenou

MrExcel MVP

Salat23

New Member

xenou

MrExcel MVP

shg

MrExcel MVP

shg

MrExcel MVP

Salat23

New Member

shg

MrExcel MVP

Salat23

New Member

Similar threads

Forum statistics

Share this page

Simple VBA Array formula question

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock