lunes, 8 de noviembre de 2010

Listado de valores con Macro

Descargar el fichero: Listado.xlsm


Hemos creado siete sistemas para que mediante macro llevemos cierto cálculo a una columna de una hoja. Los primeros cinco sistemas toman el valor de una celda calculada en la Hoja para llevarla a una columna a medida que toma diferentes valores el cálculo. Para simplificar hemos tomado el cálculo de un número aleatorio con la función =ALEATORIO(), pero la celda que tomamos podría ser, en general, cualquier otro valor que se obtenga en una hoja de Excel con cálculos mucho más complicados. Los dos últimos sistemas no toman el valor de una celda de la hoja sino que calculan, en nuestro caso, el número aleatorio desde la propia macro con RND. Será mucho más rápido que los cálculos los haga la macro internamente, y no la hoja de Excel, aunque somos conscientes de que en muchas ocasiones lo interesante es disponer de la Hoja de Excel toda su potencia de cálculo, y su organización intuitiva de los datos.


Hemos tomado tiempos para determinar el sistema más rápido, y gana de forma abrumadora el de la Hoja6. Para 1.000 datos generados los tiempos, en mi portátil, han sido los siguientes:
  1. Hoja1: 10 segundos
  2. Hoja 2: 3 segundos
  3. Hoja 3: 3 segundos
  4. Hoja 4: 2 segundos
  5. Hoja 5: 6 segundos
  6. Hoja 6: 0 segundos
  7. Hoja 7: 3 segundos
La Hoja6 da como resultado 0 segundos, ya que no llega a un segundo.

Para la Hoja6, si en lugar de pedir un listado de 1.000 datos, pedimos 1.000.000 de datos vemos que tarda 5 segundo. Por tanto, la Hoja6 contiene la macro (Listado6) más rápida con mucha diferencia.

Hoja 1

Código:

Sub Listado1()
Dim i As Long
'Activamos la Hoja1
Sheets("Hoja1").Activate
'Tiempo
Range("C12") = Now
'Borramos todo lo que hay en la columna F
Columns("F:F").ClearContents
'Generamos la Lista
'copiando y pegando con pegado especial valores
For i = 1 To Range("D6")
   Range("D4").Copy
   Cells(i, "F").PasteSpecial xlPasteValues
Next i
'Para quitar la linea de hormigas
Application.CutCopyMode = False
Range("C13") = Now
'Situamos el cursor en la celda A1
Range("A1").Select
End Sub

Hoja 2

Código:

Sub Listado2()
Dim i As Long
Sheets("Hoja2").Activate
Range("C12") = Now
Columns("F:F").ClearContents
For i = 1 To Range("D6")
   Cells(i, "F") = Range("D4")
Next i
Application.CutCopyMode = False
Range("C13") = Now
Range("A1").Select
End Sub

Hoja 3

Código:

Sub Listado3()
Dim i As Long
Dim A
Dim n As Long
Sheets("Hoja3").Activate
Range("C12") = Now
n = Range("D6")
ReDim A(n)
Columns("F:F").ClearContents
For i = 1 To n
   A(i) = Range("D4")
   Cells(i, "F") = A(i)
Next i
Application.CutCopyMode = False
Range("C13") = Now
Range("A1").Select
End Sub

Hoja 4

Código:

Sub Listado4()
Dim i As Long
Dim R As Range
Dim A
Dim n As Long
Sheets("Hoja4").Activate
Range("C12") = Now
n = Range("D6")
Set R = Range("F1:F" & n)
A = R
Columns("F:F").ClearContents
'Generamos la matriz A
For i = 1 To Range("D6")
   Calculate
   A(i, 1) = Range("D4")
Next i
'Depositamos los datos de la matriz A en la columna F
Range("F1:F" & n) = A
Range("C13") = Now
End Sub

Hoja 5

Código:

Sub Listado5()
Dim i As Long
'Dim R As Range
Dim A
Dim n As Long
Sheets("Hoja5").Activate
Range("C12") = Now
n = Range("D6")
Set A = Range("F1:F" & n)
Columns("F:F").ClearContents
'Generamos la matriz A
For i = 1 To Range("D6")
   Calculate
   A(i, 1) = Range("D4")
Next i
Range("C13") = Now
End Sub

Hoja 6

Código:

Sub Listado6()
Dim i As Long
Dim R As Range
Dim A
Dim n As Long
Sheets("Hoja6").Activate
Range("C12") = Now
n = Range("D6")
Set R = Range("F1:F" & n)
A = R
Columns("F:F").ClearContents
Randomize
For i = 1 To Range("D6")
   A(i, 1) = Rnd
Next i
Range("F1:F" & n) = A
Range("C13") = Now
End Sub

Hoja 7

Código:

Sub Listado7()
Dim i As Long
Dim A
Dim n As Long
Sheets("Hoja7").Activate
Range("C12") = Now
n = Range("D6")
Set A = Range("F1:F" & n)
Columns("F:F").ClearContents
Randomize
For i = 1 To Range("D6")
   A(i, 1) = Rnd
Next i
Range("C13") = Now
End Sub

sábado, 6 de noviembre de 2010

Sensibilidad de un Forward con Solver

Puede descargar el archivo de Excel siguiente.







No se ve la opción "Convertir variables sin restricciones en no negativas" al grabar la macro


Desde la versión de Excel 2010 en Solver se ha incluido la posibilidad de marcar o dejar sin marcar una casilla de verificación que se denomina "Convertir variables sin restricciones en no negativas".

Para dejar desmarcada esta casilla de verificación desde el código VBA se ha de añadir a la macro la siguiente línea.
  • SolverOptions AssumeNoNeg:=False

Para ver todas las opciones de Solver que se pueden incluir en código VBA ir al siguiente enlace.

La línea de código anterior se puede poner por ejemplo como línea anterior a la que ejecuta Solver al final de la macro.

La línea anterior no aparece al ver el código que ha generado la grabadora de macros, así como no aparecen otras muchas propiedades inherentes al Solver que estamos ejecutando. Por ejemplo, tampoco aparece la precisión que hemos utilizado, entre otras opciones.

Si queremos que aparezcan las opciones utilizadas en Solver, lo que tenemos que hacer mientras grabamos la macro con la grabadora es abrir las Opciones de Solver y luego cerrarlas. Esto nos genera el código necesario que luego si podremos ver. Entre todo este código está el siguiente.
  • AssumeNonNeg:=True

Este código se corresponde con el hecho de no marcar la casilla de verificación de "Convertir variables sin restricciones en no negativas".

Veamos el código que he obtenido al abrir y luego aceptar las Opciones de Solver.

Sub Macro3()
SolverOk SetCell:="$G$6", MaxMinVal:=3, ValueOf:=0, ByChange:="$C$4:$F$4", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOptions MaxTime:=0, Iterations:=0, Precision:=0.0000005, Convergence:= _
    0.0001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _
    :=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _
    IntTolerance:=0.1, SolveWithout:=False, MaxTimeNoImp:=30
SolverOk SetCell:="$G$6", MaxMinVal:=3, ValueOf:=0, ByChange:="$C$4:$F$4", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$G$6", MaxMinVal:=3, ValueOf:=0, ByChange:="$C$4:$F$4", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverSolve
End Sub

SolverFinish KeepFinal:=1

En el vídeo vemos que se añade una línea de código para no tener que estar aceptando la ventana que nos devuelve Solver al final del proceso. En el vídeo se pone en español el siguiente código.

   SolverResolver resultadoDeseado:=True

Si necesitamos ponerlo en inglés podemos cambiarlo por este otro:

   SolverFinish KeepFinal:=1

En el siguiente enlace se comenta este aspecto.