Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

How I can make a VB function that return an Array?

I need to call this function from a Excel worksheet:

Public Function PromMovil(X, N)

' PromMovil(X, N) generate a movable average

' of the numerical array X with N delays

Dim Tam, Count, jota, cumul, ret, ind

Application.Volatile

Tam = X.Rows.Count

ReDim Salida(1 To Tam)

For Count = 1 To Tam

If Count < N Then

For jota = 1 To Count

ind = Count - jota + 1

If ind < 0 Then

ret = 0

Else

ret = X(ind)

End If

cumul = cumul + ret

Next jota

Salida(Count) = cumul / Count

Else

For jota = 1 To N

cumul = cumul + X(Count - jota + 1)

Next jota

Salida(Count) = cumul / N

End If

Next Count

PromMovil = Salida

End Function

PLEASE HELP ME!!!! 10 points for the first right answers!!!

2 Answers

Relevance
  • 1 decade ago
    Favorite Answer

    Mirá lo que entiendo es que quieres llamar esta función desde una hoja de Excel y que te llene múltiples celdas ya sea alineadas en una fila o una columna. Eso no se puede hacer desde una función. Lo que te aconsejo es que coloques un control command Button en la hoja y le asignes tu función modificada:

    Private Sub CommandButton1_Click()

    Dim X as range

    Dim Y as range

    Dim N as integer

    ‘Como ejemplo

    Set X = ActiveSheet.Range("A1:E1")

    Set Y = ActiveSheet.Range("B1")

    N = 4

    Call PromMovil(X, N, Y)

    End Sub

    Y modificaría tu function de la siguiente manera:

    Le agregué el parámetro Y que es un rango cuya primera celda indica desde donde

    Se van a llenar los datos calculados en la rutina. Notá que tu función ahora es una subrutina ya que no necesita devolver un valor en su nombre.

    Public Sub PromMovil(X, N, Y)

    ' PromMovil(X, N) generate a movable average

    ' of the numerical array X with N delays

    Dim Tam, Count, jota, cumul, ret, ind

    Application.Volatile

    Tam = X.Rows.Count

    ReDim Salida(1 To Tam)

    For Count = 1 To Tam

    If Count < N Then

    For jota = 1 To Count

    ind = Count - jota + 1

    If ind < 0 Then

    ret = 0

    Else

    ret = X(ind)

    End If

    cumul = cumul + ret

    Next jota

    Salida(Count) = cumul / Count

    Else

    For jota = 1 To N

    cumul = cumul + X(Count - jota + 1)

    Next jota

    Salida(Count) = cumul / N

    End If

    Next Count

    ’Acá empieza el cambio

    ‘Voy a mostrar los valores que calculé en el vector salida en una fila de la

    ‘’hoja donde está el rango Y. Lo podés poner encolumnados si eso te conviene más.

    ‘NOTA: Esta rutina no verifica si no se pasan los límites de la hoja.

    For i =lbound(salida) to ubound(salida)

    y.Worksheet.Cells(y.Row, y.Column + i - lbound(salida)).Value2 = salida(i)

    Next i

    End Function

    Espero que esto sí resuelva finalmente tu problema.

  • Anonymous
    5 years ago

    attempt this, desire a minimum of a million will help- [a million] you may comprise argument via reference, which would be back changed to the calling function. Serving as a workaround. [2] you may make a UDT (person defined form) and return different values. Make UDT as in line with requirement, and set it in return fee.

Still have questions? Get your answers by asking now.