ParamArray workaround?

Dec 14, 2012 at 3:57 AM
Edited Dec 14, 2012 at 4:07 AM

Well, I should have the read the docs; you can't do ParamArray (or couldn't 4 years ago at least). So here I am trying to work around the limitation but trying to avoid a 20 element case statement.

	function Frotz(ByVal p1 As Object,ByVal p2 As Object,ByVal p3 As Object,ByVal p4 As Object,ByVal p5 As Object,ByVal p6 As Object,ByVal p7 As Object,ByVal p8 As Object,ByVal p9 As Object,ByVal p10 As Object,ByVal p11 As Object,ByVal p12 As Object,ByVal p13 As Object,ByVal p14 As Object,ByVal p15 As Object,ByVal p16 As Object,ByVal p17 As Object,ByVal p18 As Object,ByVal p19 As Object,ByVal p20 As Object) as object
		dim i as integer
		dim r(20) as string
		dim s as string
		dim o as object
		dim app as object = exceldnautil.application
		for i = 1 to 20
			s = "getvalueof(p" & cstr(i) & ")"
			o = app.evaluate(s)
			r(i) = o
		next
		Frotz = join(r,",")
	end function
	
	private function getvalueof( x as object ) as object
		getvalueof = x
	end function

Now when I run that I get a long line of what I assume are error codes, viz

,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259

Is this ever going to work?

Kind regards,

Bruce/bugmagnet

Dec 14, 2012 at 2:45 PM

You could always consider using a matrix function, i.e put your function parameters in a row in excel and pass that range to the function. Its not as flexible but it would work.

Ian

Ian Murphy

San Agustinalde, 1 esc.dcha. 6º dcha. · 48200 · Durango · Bizkaia

ian@integra-xp.com

946 21 52 65

www.integra-xp.com

946 20 00 41


En cumplimiento de la Ley Orgánica 15/1999, de Protección de Datos de Carácter Personal, se le informa que sus datos personales, facilitados por Ud., están incluidos en un fichero con finalidad de gestionar la relación comercial que nos une, así como de informarles de los productos o servicios de nuestra empresa, por correo, fax o correo electrónico, que pudiera serle de interés, cuyo responsable es INTEGRA EXPERIENCE, S.L. - SAN AGUSTINALDE, 1 ESC.DCHA. 6º DCHA, 48200, DURANGO, BIZKAIA. Podrá ejercitar sus derechos de acceso, rectificación, cancelación u oposición remitiendo una solicitud por escrito a la dirección anteriormente citada, acompañando un documento para su identificación.

De: bugmagnet [email removed]
Enviado el: viernes, 14 de diciembre de 2012 5:57
Para: Ian Murphy
Asunto: ParamArray workaround? [exceldna:406727]

From: bugmagnet

Well, I should have the read the docs; you can't do ParamArray (or couldn't 4 years ago at least). So here I am trying to work around the limitation but trying to avoid a 20 element case statement.

        function Frotz(ByVal p1 As Object,ByVal p2 As Object,ByVal p3 As Object,ByVal p4 As Object,ByVal p5 As Object,ByVal p6 As Object,ByVal p7 As Object,ByVal p8 As Object,ByVal p9 As Object,ByVal p10 As Object,ByVal p11 As Object,ByVal p12 As Object,ByVal p13 As Object,ByVal p14 As Object,ByVal p15 As Object,ByVal p16 As Object,ByVal p17 As Object,ByVal p18 As Object,ByVal p19 As Object,ByVal p20 As Object) as object
               dim i as integer
               dim r(20) as string
               dim s as string
               dim o as object
               for i = 1 to 20
                       s = "getvalueof(p" & cstr(i) & ")"
                       o = app.evaluate(s)
                       r(i) = o
               next
               Frotz = join(r,",")
        end function
        
        private function getvalueof( x as object ) as object
               getvalueof = x
        end function
 

Now when I run that I get a long line of what I assume are error codes, viz

,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259,-2146826259

Is this ever going to work?

Kind regards,

Bruce/bugmagnet

Dec 15, 2012 at 3:22 AM
imurphy wrote:

You could always consider using a matrix function, i.e put your function parameters in a row in excel and pass that range to the function. Its not as flexible but it would work.

Ian

I actually have tried that, and it's certainly a good place to fall back to.

 

Bruce/bugmagnet