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?

Answer 1

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