Visual Basic para Aplicaciones - Variables publicas vs Propiedades personalizadas

Saludos a todos estoy trabajando con VBA para excel y hace semanas que tengo un problema que me esta volando la cabeza. Espero que alguien pueda ayudarme pk ya no se que hacer.

La idea es escrivir un codigo que me genere programaticamente controles (opttion y command butons, image controls, etc). Una vez generados los controles ese codigo me tiene que generar programáticamente el codigo correspondiente a los controles. Como "complicacion" adicional todo (absolutamente todo) el codigo debe estar escrito en el módulo de objeto ThisWorkbook.

Les comento que avance bastante hasta que me tope con un problema que no puedo solucionar.

Lo primero que hice es utilizar el evento Workbook_Open() para iniciar todo. El evento Workbook_Open llama a otros procedimientos sub (tambien escritos en ThisWorkbook) que van generando los controles e introduciendolos en una hoja del libro (que por cierto, tambien se crea programaticamente desde el evento Workbook_Open).

Luego se carga la referencia "Microsoft Visual Basic for Applications Extensibility 5.3". Esta _
biblioteca de objetos permite generar programaticamente los eventos de los controles. Ahora puedo utilizar metodos (pertenecientes a la biblioteca anterior) para generar programaticamente los eventos de los controles creados previamente (si los controles se ubicaron en HojaX los eventos se deben ubicar tambien en HojaX).

Esta aqui todo bien. Escriviendo codigo sólo en ThisWorkbook y utilizando el evento Workbook_Open pude lograr, o al menos eso pense, lo que queria.

El problema me surgio cuando quice poner a preba los eventos. Cuando le daba click a algun control me salia el siguiente error:

Error de compilacion: no se ha definido sub o function

Despues de probar mucho me di cuenta que era porque en ThisWorkbook defini variables publicas que no eran reconocidas por los eventos. Por ejemplo, en thisworkbook declaro la variable de objeto miRango:

Public miRango as Range

set miRango = Activesheet.Range("A1:C2")

Tengo un command button que al hacer click en el me debe cambiar el contenido del rango anterior. El evento del comman button que se ingreso programaticamente (desde thisworkbook) es algo así:

private sub Commandbutton1_Click()

Activesheet.ThisWorkbook.miRango.Value = "Ronda de Apuestas"

end sub

La variable publica miRango no es detectada por el evento del control!!!!

Porque pasa esto????? es porque cuando se termina de ejecutar ThisWorkbook_Open todas las variables publicas desaparecen?????

Se pueden utilizar variables publicas definidas en thisWorkbook en los eventos de los controles???? como se puede hacer????

si no se puede, de que otra manera puedo hacerlo???? propiedades personalizadas???? AYUDA POR FAVOR

las variables públicas declaralas en un módulo estándar y debería funcionar

Saludos desde Baires, JuanC

Saludos Juan. Y gracias por tan pronta respuesta.

Sabes ya intente previamente lo que dices pero me sale este error:

se ha producido el error 91 en tiempo de ejecucion:
variable de objeto o bloque with no establecido

Aqui te dejo el codigo para que lo pruebes. Copialo y pegalo en ThisWorkbook.



Option Explicit

Private Sub Workbook_Open()

On Error GoTo Err_Workbook_Open

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Tutor"

Call Cargar_Referencia

Call Cargar_Definir_Variables

Call Dar_Formato_Hoja

Call Cargar_Ronda_Apuestas

Exit_Workbook_Open:
Exit Sub

Err_Workbook_Open:

Application.DisplayAlerts = False
ActiveSheet.Delete
Resume Exit_Workbook_Open

End Sub

Private Sub Cargar_Referencia()

On Error GoTo Err_Cargar_Referencia

Application.VBE.ActiveVBProject.References.AddFromFile _
"C:Archivos de programaArchivos comunesMicrosoft SharedVBAVBA6VBE6EXT.OLB"

Exit_Cargar_Referencia:
Exit Sub

Err_Cargar_Referencia:
Resume Exit_Cargar_Referencia

End Sub

Private Sub Cargar_Definir_Variables()

With ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
.Name = "Definir_Variables"
With .CodeModule

.InsertLines 1, "Option Explicit" & vbCrLf

.InsertLines .CountOfLines + 1, "Public ranRondaApuestasCabecera As Range, ranPosiciónJuegoCabecera As Range, _" & vbCrLf & _
"ranCategoríaManoCabecera As Range, ranJugadaContrariosCabecera As Range, _" & vbCrLf & _
"ranNuestraJugadaCabecera As Range, ranRondaApuestasCuerpo As Range, _" & vbCrLf & _
"ranPosiciónJuegoCuerpo As Range, ranCategoríaManoCuerpo As Range, _" & vbCrLf & _
"ranJugadaContrariosCuerpo As Range, ranNuestraJugadaCuerpo As Range" & vbCrLf

.InsertLines .CountOfLines, "Public Sub Asignar_Objetos()" & vbCrLf & vbCrLf & _
vbTab & "Set ranRondaApuestasCabecera = ActiveSheet.Range(""B3:C4"")" & vbCrLf & _
vbTab & "Set ranPosiciónJuegoCabecera = ActiveSheet.Range(""D3:E4"")" & vbCrLf & _
vbTab & "Set ranCategoríaManoCabecera = ActiveSheet.Range(""F3:H4"")" & vbCrLf & _
vbTab & "Set ranJugadaContrariosCabecera = ActiveSheet.Range(""I3:J4"")" & vbCrLf & _
vbTab & "Set ranNuestraJugadaCabecera = ActiveSheet.Range(""B17:H18"")" & vbCrLf & _
vbTab & "Set ranRondaApuestasCuerpo = ActiveSheet.Range(Cells( _" & vbCrLf & _
vbTab & vbTab & "ranRondaApuestasCabecera.Row + 2, ranRondaApuestasCabecera.Column), _" & vbCrLf & _
vbTab & vbTab & "Cells(ranRondaApuestasCabecera.Row + 9, ranRondaApuestasCabecera.Column + 1))" & vbCrLf & _
vbTab & "Set ranPosiciónJuegoCuerpo = ActiveSheet.Range(Cells( _" & vbCrLf & _
vbTab & vbTab & "ranPosiciónJuegoCabecera.Row + 2, ranPosiciónJuegoCabecera.Column), _" & vbCrLf & _
vbTab & vbTab & "Cells(ranPosiciónJuegoCabecera.Row + 9, ranPosiciónJuegoCabecera.Column + 1))" & vbCrLf & _
vbTab & "Set ranCategoríaManoCuerpo = ActiveSheet.Range( _" & vbCrLf & _
vbTab & vbTab & "Cells(ranCategoríaManoCabecera.Row + 2, ranCategoríaManoCabecera.Column), _" & vbCrLf & _
vbTab & vbTab & "Cells(ranCategoríaManoCabecera.Row + 9, ranCategoríaManoCabecera.Column + 2))" & vbCrLf & _
vbTab & "Set ranJugadaContrariosCuerpo = ActiveSheet.Range(Cells( _" & vbCrLf & _
vbTab & vbTab & "ranJugadaContrariosCabecera.Row + 2, ranJugadaContrariosCabecera.Column), _" & vbCrLf & _
vbTab & vbTab & "Cells(ranJugadaContrariosCabecera.Row + 9, ranJugadaContrariosCabecera.Column + 1))" & vbCrLf & _
vbTab & "Set ranNuestraJugadaCuerpo = ActiveSheet.Range(Cells( _" & vbCrLf & _
vbTab & vbTab & "ranNuestraJugadaCabecera.Row + 2, ranNuestraJugadaCabecera.Column), _" & vbCrLf & _
vbTab & vbTab & "Cells(ranNuestraJugadaCabecera.Row + 9, ranNuestraJugadaCabecera.Column + 6))" & vbCrLf & _
vbCrLf & "End Sub" & vbCrLf

End With
End With

End Sub

Private Sub Dar_Formato_Hoja()

Call Asignar_Objetos

Dim ranGrupo As New Collection

With ranGrupo
.Add ranRondaApuestasCabecera
.Add ranRondaApuestasCuerpo
.Add ranPosiciónJuegoCabecera
.Add ranPosiciónJuegoCuerpo
.Add ranCategoríaManoCabecera
.Add ranCategoríaManoCuerpo
.Add ranJugadaContrariosCabecera
.Add ranJugadaContrariosCuerpo
.Add ranNuestraJugadaCabecera
.Add ranNuestraJugadaCuerpo
End With

Dim ran1 As Range

For Each ran1 In ranGrupo
With ran1
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.ShrinkToFit = True
.MergeCells = True
.Borders.Weight = xlMedium
.Font.Name = "Verdana"
.Font.FontStyle = "Negrita"
.Font.Size = 9
End With
Next

Dim i1 As Integer

For i1 = 1 To 10 Step 2
ranGrupo(i1).Interior.ColorIndex = 9
ranGrupo(i1).Font.ColorIndex = 2
Next i1

For i1 = 2 To 9 Step 2
ranGrupo(i1).Interior.ColorIndex = 15
Next i1

ranRondaApuestasCabecera.Value = "Roda de Apuestas"
ranPosiciónJuegoCabecera.Value = "Posición en la Mesa"
ranCategoríaManoCabecera.Value = "Categoría de la Mano"
ranJugadaContrariosCabecera.Value = "1º Jugada de Nuestros Contrarios"
ranNuestraJugadaCabecera.Value = "Nuestra Primer Jugada"

End Sub

Private Sub Cargar_Ronda_Apuestas()

Dim optRondaApuestas(1 To 4) As OLEObject
Dim strRondaApuestas() As Variant

strRondaApuestas = Array("optPreflop", "optPostflop", "optPostturn", "optPostriver", "Preflop", _
"Postflop", "Postturn", "Postriver")

Dim i1 As Integer

For i1 = 1 To 4
Set optRondaApuestas(i1) = ActiveSheet.OLEObjects.Add(ClassType:="Forms.OptionButton.1", _
Left:=ranRondaApuestasCuerpo.Left, Top:=ranRondaApuestasCuerpo.Top + _
ranRondaApuestasCuerpo.Height * (i1 - 1) / 4, Width:=ranRondaApuestasCuerpo.Width, _
Height:=ranRondaApuestasCuerpo.Height / 4)
With optRondaApuestas(i1)
.Name = strRondaApuestas(i1 - 1)
.Shadow = True
With .Object
.GroupName = "RonaApuestas"
.BackColor = ranRondaApuestasCuerpo.Interior.Color
.Caption = strRondaApuestas(i1 - 1 + 4)
.TextAlign = 2
With .Font
.Name = "Verdana"
.Bold = True
.Size = 9
End With
End With
End With
Next i1

optRondaApuestas(1).Object.Value = True

Call Cargar_Eventos_Ronda_Apuestas

End Sub

Private Sub Cargar_Eventos_Ronda_Apuestas()

With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule

.InsertLines .CreateEventProc("Click", "optPreflop") + 2, _
vbTab & "Definir_Variables.ranPosiciónJuegoCabecera.Value = ""Posición en la Mesa""" & vbCrLf

.InsertLines .CreateEventProc("Click", "optPostflop") + 2, _
vbTab & "Definir_Variables.ranPosiciónJuegoCabecera.Value = ""Juego Antes del Flop""" & vbCrLf

.InsertLines .CreateEventProc("Click", "optPostturn") + 2, _
vbTab & "Definir_Variables.ranPosiciónJuegoCabecera.Value = ""Juego Antes del Flop""" & vbCrLf

.InsertLines .CreateEventProc("Click", "optPostriver") + 2, _
vbTab & "Definir_Variables.ranPosiciónJuegoCabecera.Value = ""Juego Antes del Flop""" & vbCrLf

End With

End Sub


Luego cierra el libro y vuelvelo a abrir (se crea una hoja, se cargaran automaticamente los controles en ella y luego los eventos corrrespondientes)

Ahora haz click en algun control y fijate .

Porque pasa esto????? aún nadie me lo ha sabido decir . Es porque debo asignarle el objeto a las variables dentro del propio evento???

Como puedo hacer esta tarea??? alguna otra forma????

el error se debe a que el objeto no está asignado
deberías chequear primero y luego usarlo
un ejemplo de cómo quedaría

Private Sub optPostflop_Click()
If ranPosiciónJuegoCabecera Is Nothing Then
Call Asignar_Objetos
End If
ranPosiciónJuegoCabecera.Value = "Juego Antes del Flop"
End Sub

Saludos desde Baires, JuanC