Pasar Array como argumento a un procedimiento, es posible?

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
Hay dos ejamplos aqui - uno con "string data type", y otro con "variant" - Variant es mas flexible, o mas facil para usar. -- no hablo espanol muy bien.... :(

Code: 
Sub Uno()
Dim x(2) As String
    
    x(0) = "uno"
    x(1) = "dos"
    x(2) = "tres"
    Call Dos(x())

End Sub
'--------------------------
Sub Dos(Arg() As String)

    MsgBox Arg(0)
    MsgBox Arg(1)
    MsgBox Arg(2)

End Sub
'--------------------------
Sub Tres()
Dim x As Variant
    
    x = Array("uno", "dos", "tres")
    Call Cuatro(x)

End Sub
'--------------------------
Sub Cuatro(Arg As Variant)
    
    For x = 0 To UBound(Arg)
        MsgBox Arg(x)
    Next x

End Sub
 
Last edited:
Upvote 0
Code: 
Sub Uno()
Dim x(2) As String
 
    x(0) = "uno"
    x(1) = "dos"
    x(2) = "tres"
    Call Dos(x())
    MsgBox x(2), vbInformation, "De Uno"
End Sub
'--------------------------
Sub Dos(Arg() As String)
    MsgBox Arg(0)
    MsgBox Arg(1)
    MsgBox Arg(2)
    Arg(2) = "three"
End Sub
'--------------------------
Sub Tres()
Dim x As Variant
 
    x = Array("uno", "dos", "tres")
    Call Cuatro(x)
    MsgBox x(2), vbInformation, "De Tres"
 
End Sub
'--------------------------
Sub Cuatro(ByVal Arg As Variant)
 
    For x = 0 To UBound(Arg)
         MsgBox Arg(x)
    Next x
    Arg(2) = "three"
End Sub
'_______________________
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Sub Cinco()
    Dim x(2) As String
 
    x(0) = "uno"
    x(1) = "dos"
    x(2) = "tres"
 
    Seis "Azul", "Nicaragua", x
    Seis "Rojo", "Peru", "Agua", "Fuego", "Viento", 99
 
End Sub
'_______________________
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Sub Seis(ByVal strColor As String, _
         ByVal strPais As String, _
         ParamArray LoQueSea() As Variant)
    MsgBox "La bandera de " & strPais & " tiene " & strColor
 
End Sub

Samual, note que si uno pasa un array como Alejandro hizo en la rutina Uno tiene que pasarlo por referencia entonces cambios en la segunda rutina cambian el original. En cambio si uno pasa como variant, se puede pasa por valor y cambios en la segunda rutina no cambiaría el original. No tiene que pasar por valor, pero se puede. En cambio si pone byVal en Uno tire un error al momento de compilar.

También vale la pena usar VIEW LOCALS del menu VIEW mientras corre Cinco y Sies para ver como se estructura LoQueSea si uno usa PARAMARRAY.

Saludos,
 
Last edited:
Upvote 0
Lo tengo que revisar, gracias. Pero dejo extracto del codigo

el siguiente código al menos por ahora es funcional. Lo que pretendo es sacar del procedimiento "cuatro", los ciclos que llenan los ARRAY ( Concepto, Categoria y PrecioUnitario ) porque se repiten VEINTE veces.


Me interesa especial el paso por referencia y por valor.

Por cierto, notarán que los controles tienen nombres consecutivos (0-19), bueno, pues tampoco pude trabajarlos dinámicamente. Lo que me funcionó fué ponerlos todos y adecuar las propiedades de unos u otros según cada caso.

Nuevamente gracias. Sigo aprendiendo.

- Samuel


Code: 
Private Sub cboContrato_Change()


    lngLargoDelArrayPU = Sheets(contrato).[B65536].End(xlUp).Row - 10       '' Largo Del Array PU


    ''! determina cuantos CONTROLES mostrar, según
    ''! variable que determina la CANTIDAD DE CONCEPTOS EN CONTRATO (lngLargoDelArrayPU)
    ''! y asigna valores de cada array a etiquetas   (minimo 1 máximo 20)

	Select Case lngLargoDelArrayPU

	    Case Is = 1
        	uno

		.
		.
		.

	    Case Is = 4     
        	cuatro

		.
		.
		.

	    Case Is = 20     
        	veinte

	End Select

End Sub


'----------------------------------------------------------
'----------------------------------------------------------


Private Sub cuatro()

   
''! array CONCEPTO
    lngIndicePU = 0
    
    Dim strConcepto() As String
    
    ReDim Preserve strConcepto(lngLargoDelArrayPU)
        
    For lngFila = 11 To lngUltimaFilaOcupadaPU
        strConcepto(lngIndicePU) = ActiveSheet.Cells(lngFila, 4).Text
        lngIndicePU = lngIndicePU + 1
    Next



''! array CATEGORIA
    lngIndicePU = 0
    
    Dim strCategoria() As String
    
    ReDim Preserve strCategoria(lngLargoDelArrayPU)
        
    For lngFila = 11 To lngUltimaFilaOcupadaPU
        strCategoria(lngIndicePU) = ActiveSheet.Cells(lngFila, 3).Text
        lngIndicePU = lngIndicePU + 1
    Next



''! array PRECIO UNITARIO
    lngIndicePU = 0
        
    Dim dblPrecioUnitario() As Double
        
    ReDim Preserve dblPrecioUnitario(lngLargoDelArrayPU)
        
    For lngFila = 11 To lngUltimaFilaOcupadaPU
        dblPrecioUnitario(lngIndicePU) = ActiveSheet.Cells(lngFila, 6).Value
        lngIndicePU = lngIndicePU + 1
    Next



    lblConcepto0.Caption = strConcepto(0)
    lblConcepto1.Caption = strConcepto(1)
    lblConcepto2.Caption = strConcepto(2)
    lblConcepto3.Caption = strConcepto(3)
    lblConcepto4.Caption = ""
    lblConcepto5.Caption = ""
    lblConcepto6.Caption = ""
    lblConcepto7.Caption = ""
    lblConcepto8.Caption = ""
    lblConcepto9.Caption = ""
    lblConcepto10.Caption = ""
    lblConcepto11.Caption = ""
    lblConcepto12.Caption = ""
    lblConcepto13.Caption = ""
    lblConcepto14.Caption = ""
    lblConcepto15.Caption = ""
    lblConcepto16.Caption = ""
    lblConcepto17.Caption = ""
    lblConcepto18.Caption = ""
    lblConcepto19.Caption = ""
            
    lblCAT0.Caption = strCategoria(0)
    lblIMP0.Tag = strCategoria(0)
    lblCAT1.Caption = strCategoria(1)
    lblIMP1.Tag = strCategoria(1)
    lblCAT2.Caption = strCategoria(2)
    lblIMP2.Tag = strCategoria(2)
    lblCAT3.Caption = strCategoria(3)
    lblIMP3.Tag = strCategoria(3)
    lblCAT4.Caption = ""
    lblCAT5.Caption = ""
    lblCAT6.Caption = ""
    lblCAT7.Caption = ""
    lblCAT8.Caption = ""
    lblCAT9.Caption = ""
    lblCAT10.Caption = ""
    lblCAT11.Caption = ""
    lblCAT12.Caption = ""
    lblCAT13.Caption = ""
    lblCAT14.Caption = ""
    lblCAT15.Caption = ""
    lblCAT16.Caption = ""
    lblCAT17.Caption = ""
    lblCAT18.Caption = ""
    lblCAT19.Caption = ""
            
    lblPU0.Caption = Format(dblPrecioUnitario(0), "#,##0.00")
    lblPU1.Caption = Format(dblPrecioUnitario(1), "#,##0.00")
    lblPU2.Caption = Format(dblPrecioUnitario(2), "#,##0.00")
    lblPU3.Caption = Format(dblPrecioUnitario(3), "#,##0.00")
    lblPU4.Caption = ""
    lblPU5.Caption = ""
    lblPU6.Caption = ""
    lblPU7.Caption = ""
    lblPU8.Caption = ""
    lblPU9.Caption = ""
    lblPU10.Caption = ""
    lblPU11.Caption = ""
    lblPU12.Caption = ""
    lblPU13.Caption = ""
    lblPU14.Caption = ""
    lblPU15.Caption = ""
    lblPU16.Caption = ""
    lblPU17.Caption = ""
    lblPU18.Caption = ""
    lblPU19.Caption = ""

    txtCANT0.Visible = True
    txtCANT1.Visible = True
    txtCANT2.Visible = True
    txtCANT3.Visible = True
    txtCANT4.Visible = False
    txtCANT5.Visible = False
    txtCANT6.Visible = False
    txtCANT7.Visible = False
    txtCANT8.Visible = False
    txtCANT9.Visible = False
    txtCANT10.Visible = False
    txtCANT11.Visible = False
    txtCANT12.Visible = False
    txtCANT13.Visible = False
    txtCANT14.Visible = False
    txtCANT15.Visible = False
    txtCANT16.Visible = False
    txtCANT17.Visible = False
    txtCANT18.Visible = False
    txtCANT19.Visible = False

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Save Close Files
Replies
0
Views
240
MILTINO
M
F
  • Solved
Ayuda con formula
Replies
3
Views
680
DanteAmor
DanteAmor
J
  • Question Question
Contar días consecutivos con 2 condiciones
Replies
4
Views
1K
juanam
J
Z
  • Locked
  • Question Question
CONJETURE COLLATZ
Replies
4
Views
740
Fluff
Fluff
M
  • Solved
assign number to macro
Replies
2
Views
667
MILTINO
M

Forum statistics

Threads
1,223,970
Messages
6,175,707
Members
452,667
Latest member
vanessavalentino83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top