Excel - Estudiante

Intento crear funciones para Excel mediante Visual Basic. Deseo crear una función en la cual el programa lea una matriz y dos argumentos enteros adicionales (la matriz quiero definirla como es usual en una fórmula de Excel).
¿Alguien me podría proporcionar ayuda?

Pues si explicas mejor lo que necesitas, y envias lo que has avanzado, claro que te ayudamos.

Abraham

PD: VBA no VB

Con otro programa (Scilab) realicé una función que me permite ordenar las filas de una matriz en orden creciente (conservando los valores de cada fila, mas no su posición).

Como necesito algo similar en Excel, me dispuse a probar con un ejemplo más pequeño.
Por ahora, me basta averiguar cómo hago que la entrada sea una matriz, y que devuelva un elemento de la misma. Haré un pequeño esquema:

Function ElementoMatriz(matriz As <recomendado para matriz>,fila As Integer, columna As Integer) As <recomendado para elemento de matriz>
ElementoMatriz = matriz(fila,columna)
End Function

El problema surge en Excel:
=ElementoMatriz(<celdainicial>:<celdafinal>;<fila>;<columna>)

Es necesario que lea la matriz mediante este método (así como suele aplicarse con las fórmulas de Excel).

Podría serme útil definir también una fórmula para que me indique el total de filas y columnas de una matriz.

¿Necesitan más información?

si te entendi bien:

Function contarfc(rango As Range)

Dim rango1 As Range
Dim f As Integer, c As Integer

Application.Volatile True

Set rango1 = rango

With rango1
f = .Rows.Count
c = .Columns.Count
End With

contarfc = ("Hay " & f & " filas y " & c & " columnas")

End Function

Abraham

Bastante interesante. Me será muy útil.
¿Sirve la variable de tipo "range" para trabajar con ella como si fuera una matriz? En ese caso, ¿sería el elemento inicial rango(0,0) o rango(1,1)?

Range se usa obviamente para rangos, y como las "matrices" en excel son rangos, entocnes, por eso.

Digamos que tu matriz se encuentra entre B5 y F10, entonces colcocas:

=contarfc(B5:F10)

y ahi te aprecera el numero de columnas y de filas.

Espero haberme dejado entender.

Abraham

Muchas gracias por todo.
Hace días buscaba esta información, infructuosamente.
Si surge algún problema, no dudaré en consultar aquí.

Function OrdenarLista(matriz As Range, fila As Integer, columna As Integer)
With matriz
total_filas = .Rows.Count
total_columnas = .Columns.Count
End With
For fila1 = 1 To total_filas - 1
For fila2 = fila1 + 1 To total_filas
For columna1 = 1 To total_columnas
If matriz(fila1, columna1) > matriz(fila2, columna1) Then
For columna2 = 1 To total_filas
auxiliar = matriz(fila1, columna2)
matriz(fila1, columna2) = matriz(fila2, columna2)
matriz(fila2, columna2) = auxiliar
Next columna2
Stop
ElseIf matriz(fila1, columna1) < matriz(fila2, columna1) Then
Stop
End If
Next columna1
Next fila2
Next fila1
OrdenarLista = matriz(fila, columna)
End Function

Sirve para ordenar filas de una matriz (primero por la primera columna, luego por la segunda…).
Pero no funciona, al parecer. Aplico en excel, y me devuelve siempre un error de valor.
¿Quién me asiste para pulirlo?

No puedes modificar celdas ni formatos (eso incluye ordenar-mover) con una funcion (Fubction) tienes que usar una rutina (Sub)

Abraham

¿Cómo crearía una subrutina para hacer el intercambio de variables necesario, entonces?
Hasta ahora sólo he manejado funciones.

P.D.: Me di cuenta, también, de que olvidé de declarar algunas variables. Ésto ya fue corregido.

Otra vez llego un poco tarde...

El problema por el cual te devuelve #Valor! es que estás manejando
mal los índices de la matriz. Si depurás el código vas a ver
que en el bucle

For columna2 = 1 To total_filas
auxiliar = matriz(fila1, columna2)
matriz(fila1, columna2) = matriz(fila2, columna2)
matriz(fila2, columna2) = auxiliar
Next columna2

los valores de los índices se salen del rango de la matriz.
Quizá columna2 debe variar entre 1 y total_filas -1...

Hace poco vimos que una función puede devolver más de un valor
utilizando matrices y funciones matriciales.
Otro tema que tocó Abraham -por eso lo menciono- es el hecho de que
una función no puede modificar las propiedades de una celda; es decir,
una función sólo puede modificar el valor de la celda desde donde fue
llamada y de ninguna otra, como así tampoco puede cambiar el color
o el tipo de fuente. (para eso está el formato condicional...)
A continuación voy a dejar un código que pretende 'solucionar' tal situación.
Consta de un módulo de clase y uno normal...
Un problema que presenta el método es que no funciona cuando una fórmula
es arrastrada o copiada y pegada, pero puede modificarse y mejorarse...

Código de la clase

Option Explicit

'//----------------------------------
'//Clase clsFunctionColor
'//
'//By JuanC 2007
'//----------------------------------

Public WithEvents App As Application

Public Cell As Range
Public bPintar As Boolean
Public iColor As Integer

Private Sub Class_Initialize()
On Error Resume Next
Set App = Application
Set Cell = Nothing
bPintar = False
iColor = -4142
End Sub

Private Sub Class_Terminate()
On Error Resume Next
Set App = Nothing
Set Cell = Nothing
End Sub

'//Puede usarce otro evento de la hoja
'//En este caso uso SheetSelectionChange
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
'//Sólo se modifica el color de la celda,
'//pero podría cambiarse cualquier otra propiedad
With Cell.Interior
If bPintar Then
.ColorIndex = iColor
Else
.ColorIndex = -4142
End If
End With
Set Cell = Nothing
End Sub

Código que va en el módulo

Option Explicit

Dim App As clsFunctionColor

'//Una función sencilla que sólo
'//modifica el color de la celda
'//desde donde fue llamada la función.
'//Obviamente el método puede aplicarse
'//a cualquier UDF
Function FC(b As Boolean)
Set App = New clsFunctionColor
On Error Resume Next
With App
If TypeName(Application.Caller) = "Range" Then
Set .Cell = Application.Caller
.bPintar = b
.iColor = 3 '//Rojo
Else
.Cell = Nothing
End If
End With
End Function

Saludos desde Baires, JuanC

Mi querdio Juan, siempre tu con alguna novedad interesante. Lo pruebo apenas me de un tiempo y te aviso cualquier cosa. Gracias

Abraham

Con respecto a la variable "columna2", el rango está mal definido.
Como muestra, les daré el problema resuelto en Scilab (alguno sabrá trabajar con el programa).

//Inicio.

function resultado = OrdenarLista(matriz) //Sólo matriz de dos dimensiones, a lo sumo.
dimensiones = size(matriz)
for fila1 = 1 : dimensiones(1)-1
for fila2 = fila1+1 : dimensiones(1)
for columna1 = 1 : dimensiones(2)
if matriz(fila1,columna1) > matriz(fila2,columna1)
for columna2 = 1 : dimensiones(2)
[matriz(fila1,columna2),matriz(fila2,columna2)] = Intercambiar(matriz(fila1,columna2),matriz(fila2,columna2))
end
break
elseif matriz(fila1,columna1) < matriz(fila2,columna1)
break
end
end
end
end
resultado = matriz
endfunction

function [A,B] = Intercambiar(m,n)
A = n; B = m
endfunction

//"OrdenarLista":
//Ordena los valores de una lista de columnas, intercambiando solamente el orden de las filas.
//Autor: R.G.A.M.
//Fecha: 19-6-2.007

//"Intercambiar":
//Realiza la operación de intercambio de variables.
//Autor: R.G.A.M.
//Fecha: 4-6-2.007

//Fin.

Realicé varias pruebas, y funciona perfectamente en esta interfaz (la función de intercambiar considérenla un simple experimento, sé que existen métodos mejores para trabajar).
Me gustaría emular en Excel el mismo trabajo, pero necesito ordenar de alguna forma los elementos de un array o un rango mediante Visual Basic.
Tenía en mente ordenar mediante el programa y, en la fórmula, solicitar que me devuelva un elemento de la matriz, simplemente.

Quisiera trabajar posteriormente con el ejemplo que me mandó. Aún no soy muy diestro trabajando con distintos lenguajes de programación, en mayor parte porque me malcrié con Scilab desde hace dos años (me solucionaba muchos problemas con los cuales ahora me enfrento).

Muchos saludos de mi parte.

no lo tomes a mal, pero me parece que estás queriendo volar demasiado
alto sabiendo apenas carretear... paso a paso salen las cosas...

Saludos desde Baires, JuanC

Es muy probable que tenga razón. Es mi costumbre intentar aprender demasiado rápido.

En esta ocasión, intenté controlarme. Poco a poco estuve averiguando distintas funciones que me pudieran ayudar en el efecto. Tomé nota de las que me faltaban, y busqué la información necesaria para saber de ellas.
Sólo me faltó pulir esta última parte, y envié la función hecha en Scilab para que se supiera que tengo el problema solucionado, en otro lenguaje. Lo único que necesito es representarlo en una hoja de Excel.
En unas pruebas, dado un rango, conseguí que me devolviera una función dada el elemento ubicado en la posición ingresada. [ función(matriz,fila,columna) = elemento_matriz(fila,columna) ]
Para este toque final, he solicitado ayuda, justamente. ¿Me dan un ejemplo en el cual intercambian las posiciones de los elementos de un rango? (No deseo yo que las celdas se modifiquen, pues sólo me interesa crear la fórmula para ordenar en otra parte de la hoja todas las celdas).

para intercambiar valores de una matriz (rango) tenés que trabajar con funciones matriciales

http://www.lawebdelprogramador.com/news/mostrar_new.php?id=127&texto=Excel&n1=442307&n2=1&n3=1&n4=1&n5=1&n6=1&n7=1&n8=1&n9=1&n0=3

Saludos desde Baires, JuanC

Observo el sitio que me recomendó, mas no encuentro la información buscada. Necesito realizar todo este proceso, como bien sabe en Visual Basic.
Si lo que busco realmente está en ese vínculo, me gustaría recibir un poco de asistencia para comprender mejor.

definitivamente no tenés ganas de aprender...
acá va el código, está hecho con un procedimiento Sub
para hacerlo con Function hay que trabajar con matrices y funciones
matriciales, lo cual es un poco más complicado (para vos, no para mí)
no lo comenté, así que a estudiar...

Option Explicit

'//By JuanC - 24 de Junio de 2007

Sub OrdenaMatriz()
Dim mx() As Variant, v() As Variant
Dim P As Range, Matriz As Range
Dim i&, j&, ii&, jj&, m&, n&

Set Matriz = [B3:E7]

With Matriz
m = .Rows.Count
n = .Columns.Count
ReDim mx(m, n) As Variant
ReDim v(n) As Variant
Set P = Range(Split(.Address, ":")(0))
End With

For i = 0 To m - 1
For j = 0 To n - 1
mx(i, j) = P.Offset(i, j)
Next
Next

For i = 0 To m - 1
ii = 0
For j = 0 To n - 1
v(ii) = mx(i, j)
ii = ii + 1
Next
QuickSort v, 0, n - 1
ii = 0
For j = 0 To n - 1
mx(i, j) = v(ii)
ii = ii + 1
Next
Next

ii = 0
For i = 0 To m - 1
For j = 0 To n - 1
P.Offset(ii, jj) = mx(i, j)
jj = jj + 1
Next
ii = ii + 1
jj = 0
Next

Erase mx
Erase v
Set Matriz = Nothing
Set P = Nothing
End Sub

Private Sub QuickSort(vArray As Variant, L As Long, R As Long)
Dim i&, j&
Dim X As Variant, Y As Variant

i = L
j = R
X = vArray((L + R) / 2)

Do While (i <= j)
Do While (vArray(i) < X And i < R)
i = i + 1
Loop

Do While (X < vArray(j) And j > L)
j = j - 1
Loop

If (i <= j) Then
Y = vArray(i)
vArray(i) = vArray(j)
vArray(j) = Y
i = i + 1
j = j - 1
End If
Loop

If (L < j) Then QuickSort vArray, L, j
If (i < R) Then QuickSort vArray, i, R
End Sub

y asunto terminado...

Saludos desde Baires, JuanC

PD: para cambiar el sentido de la ordenación sólo basta cambiar algunos
signos en QuickSort