I am learning the VBA language, and I have been practicing, making a table that generates random values according to a triangular distribution, for this in a table inside a sheet I place the names of variables, the mode and the historical minimum and maximum of that variable . With those last 3 parameters I generate a vector with the numbers according to the distribution.
I start by creating 3 type objects, one for the variables, another for constants (these 2 will be used to later make a model and find an expected value) and another for the scenarios.
As a first step I am creating the macro to generate random values based on a distribution, in the first I take the values of how many scenarios I want to generate and how many variables and constants the user has entered. Then I call the second macro that is responsible for generating a table with a column with the scenarios generated for the first variable entered by the user. However when calling the second macro I get the following error.
I'm also attaching the code I've been running. It's a bit long, but I'm just learning, the error comes after calling the second macro from the first.
'Generar matriz para variables
Type variable
nombrev As String
minh As Double
vesp As Double
maxh As Double
End Type
'Generar matriz para constantes
Type constante
nombrec As String
valor As Double
End Type
'Generar matriz para los escenarios
Type combinacion
posicion As Integer
primer_variable As Integer
End Type
Sub generar_escenarios()
Dim nescenarios As Integer
Dim nvariables As Integer
Dim nconstantes As Integer
'Pedir cantidad de escenarios
nescenarios = CInt(InputBox("Coloque cuántos escenarios desea generar", "Generar escenarios", 10))
' Contar cuantas variables hay
nvariables = CInt(WorksheetFunction.CountA(Range("c3:g3")))
'Contar cuantas constantes hay
nconstantes = CInt(WorksheetFunction.CountA(Range("j3:n3")))
Call dist_triang(nescenarios, nvariables, nconstantes)
End Sub
'Macro para generar la matriz con los valores que obtendrá la tabla
Sub dist_triang(escenarios As Integer, n_variables As Integer, n_constantes As Integer)
Dim cant_escenarios As Integer
cant_escenarios = escenarios
'Almacenar variables
'almacenar_variables (n_variables)
Dim cantidad_variables As Integer
cantidad_variables = n_variables
Dim variables(1 To cantidad_variables) As variable
Dim contadorv As Integer
contadorv = 1
For v = 3 To 7
If Sheets(1).Cells(3, v).Value <> Null Then
variables(contadorv).nombrev = Sheets(1).Cells(3, v).Value
variables(contadorv).minhv = Sheets(1).Cells(4, v).Value
variables(contadorv).vesp = Sheets(1).Cells(5, v).Value
variables(contadorv).maxh = Sheets(1).Cells(6, v).Value
contadorv = contadorv + 1
End If
Next v
'Almacenar constantes
'almacenar_constantes (n_constantes)
Dim cantidad_constantes As Integer
cantidad_constantes = n_constantes
Dim constantes(1 To cantidad_constantes) As constante
Dim contadorc As Integer
contadorc = 1
For v = 10 To 15
If Sheets(1).Cells(3, v).Value <> Null Then
constantes(contadorc).nombrev = Sheets(1).Cells(3, v).Value
constantes(contadorc).minhv = Sheets(1).Cells(4, v).Value
contadorc = contadorc + 1
End If
Next v
'*****************************************************************
'Declarar vector con n números aleatorios entre 0 y 1
Dim n_aleatorios(1 To cant_escenarios)
'Almacenar números aleatorios en vector n_aleatorios
For i = 1 To cant_escenarios
n_aleatorios(i) = Worksheet.Function.rand()
Next i
'******************************************************************
'declarar matriz tipo combinacion que almacenara todos los escenarios
Dim matriz_esc(1 To cant_escenarios) As combinacion
'generar valores segun distribucion triangular para primera variable
For h = 1 To cant_escenarios
matriz_esc(h).posicion = h
matriz_esc(h).primer_variable = TRIANGULAR(variables(h).minh, variables(h).vesp, variables(h).maxh, n_aleatorios(h))
Next h
'************************************************
'generar tabla
Dim fin_tabla As Integer
fin_tabla = 13 + cant_escenarios
Sheets(1).Cells(12, 3).Value = variables(1).nombrev
For t = 13 To fin_tabla
Sheets(1).Cells(t, 2).Value = matriz_esc(1).posicion
Sheets(1).Cells(t, 3).Value = matriz_esc(1).primer_variable
Next t
End Sub
'funcion para generar número segun distribución triangular sacada de gugol
Public Function TRIANGULAR(a As Double, b As Double, c As Double, P As Single) As Double
Dim k As Single
Dim X As Double
'Calculamos k
k = (b - a) / (c - a)
If P < k Then
X = a + Sqr(P * (b - a) * (c - a))
Else
X = c - Sqr((1 - P) * (b - a) * (c - a))
End If
TRIANGULAR = X
End Function
I found the error, when declaring an array, it declares it of a size that was equal to a variable. When declaring it with Dim array_name(variable) as object_type, the error was that I should have declared it with ReDim, in this way we already know that it is a dynamic array and it does not ask for a constant.