How to return 2 values and rounding them? Excel VBA

Question

I am working on a code that should calculate simple foundations, and in order to do that I have to return 2 values with my function -preferably in two different columns.

Function FundacaoSimples(b, l, carga) As Variant

tensao = Sheets("Tabelas e Constantes").Range("tensao").Value

Dim area As Double
Dim Bs As Single
Dim Ls As Single
Dim Resultado(1 To 2) As String

If b = l Then
    area = (1.1 * carga) / tensao
    Bs = Sqr(area)
    Ls = Bs
ElseIf b <> l Then
    area = (1.1 * carga) / tensao
    Bs = Sqr((2 * area) / 3)
    Ls = (3 * Bs) / 2
End If

Resultado(1) = Round(Bs, 2)
Resultado(2) = Round(Ls, 2)

FundacaoSimples = (Resultado(1) & " / " & Resultado(2))

End Function

This rounding I am using it just to get a value rounded with 2 decimals, e.g: 2,73 to 2,75; 0,89 to 0,90. I tried working with ActiveCells.Offset(0,1), but the statement isn't valid. Is it possible to to just jump one column to the right?

You can't. A UDF takes inputs and *returns a value* that becomes the invoking cell's `Value`. UDFs can't alter other cells. — Mathieu Guindon, Jul 04 '17 at 15:47

Rik Sportel · Accepted Answer · 2017-07-04T14:36:30.027

You could use ActiveCell.Offset(0, 1).value = SomeValue, however - That's when writing a regular Sub. You're writing a Function / User Defined Function.

Within a UDF it is not possible to alter different cells.

However, a workaround is to have the UDF and when it's entered in a cell, you can then use the Worksheet_Change event to alter the cell next to the Target parameter of that event.

Edit: Some sample code:

In a regular module:

Public Function MyUDF(param1 as integer, param2 as integer) as Integer
    MyUDF = param1 + param2
End Function

In the Worksheet where you want the offset:

Private Sub Worksheet_Change(Byval Target as Range)
    If Left(Target.Formula, 6) = "=MyUDF" Then
        Target.Offset(0, 1).value = "somevalue at the offset cells"
    End If
End Sub

Yes, you're absolutely right. However, for the UDF to update in the first place it'll need `Application.Volatile` as well and there's some more concerns with taking such an approach - However, it is the solution to having the "cell next to the cell" be filled with another formula. I agree that it's better to use native Excel functions in this scenario. — Rik Sportel, Jul 04 '17 at 15:54

score 0 · Answer 2 · answered Jul 04 '17 at 14:16

In general, functions should not be writing values or accessing values from a spreadsheet. They should access their parameters and return result. Try like this, an oversimplified version of what you need:

Option Explicit

Public Sub TestMe()

    ActiveCell = FundacaoSimples(0)
    ActiveCell.Offset(0, 1) = FundacaoSimples(1)

End Sub

Function FundacaoSimples() As Variant

    ReDim varResult(1)

    varResult(0) = 55
    varResult(1) = 100

    FundacaoSimples = varResult

End Function

Then you can edit the function a bit with your own parameters and use it further.

How to return 2 values and rounding them? Excel VBA

2 Answers2