Apologies if I've not posted this correctly. I don't yet know how to put vba coding into its own window. I’ve been relearning to use excel and vba try to automate an excel monthly meal plan for a seniors’ home in which I live. After several months, I’ve accomplished a lot. That said, I’ve been exploring how I might optimize my coding, reduce redundant variable, and restructure the file by expanding the versatility of some subs. To that end, I’ve integrated Debug.Print to help me gain understanding as to what is happening. What an eye-opener!!!!
Just within my first few subs I saw things happening I needed to understand to better design my subs and functions. In truth, I have yet to apply functions, but their structure seems to be more complicated for my aging brain grasp. Two important things at this point I hope can be explained as they could vastly improve the entire structure of my file. I’ve embedded comments in my coding to try to limit the length of my question and provide context
Questions:
Option Explicit
Public WksFlag As Boolean
Public WksRow As Integer, LdRow As Integer
Sub Worksheet_BeforeDoubleClick(ByVal WksUn As Range, WksFlag As Boolean)
Subroutine = "SUB: Wks_B4DblClick"
Debug.Print "---> Start " & Subroutine & "(" & WksUn & ", " & WksFlag & ")"
If WksUn.Column = 1 Then ' WksUn for Worksheet UnitNo (D112)
WksRow = WksUn.Row ' These dims should be global/public
LdRow = WksRow – 4 ' LdRow for ListDiners in User Form
formMealPlan.txtcRow.Value = LdRow
Load frmMealPlan
frmMealPlan.Show vbModeless
End If ' WksRow, LdRow, WksUn don’t seem to immediately Public
' Even if I also declare them as well in formMealPlan. (See included printout)
Debug.Print vbNewLine & "<--- End " & Subroutine & "(" & WksUn & ", " & WksFlag & ")"
End Sub
' formMealPlan (userform) Code
Public LdRow As Integer, WksRow As Integer
Sub UserForm_Initialize()
Subroutine = "SUB: UserForm_Initialize"
Debug.Print "---> Start " & Subroutine & "()"
Debug.Print "txtcRow(" & txtcRow & ")" <<<< This is here to display my point
Exit Sub ' I'll move this below Build_ddnUnitNo for 2nd run
Build_ddnUnitNo ' Populates the Unit Number drop down list
End Sub
The Debug Printout
1st Debug.Print run
---> Start SUB: Wks_B4DblClick(D112, False)
---> Start SUB: UserForm_Initialize()
txtcRow()
---> Start Sub: txtcRow_Change()
<--- End Sub: txtcRow_Change() <<< This appears Build_ddnUnitNo sub (see below)
<--- End SUB: Wks_B4DblClick(D112, False)
2nd Debug.Print run
---> Start SUB: Wks_B4DblClick(D112, False)
---> Start SUB: UserForm_Initialize()
txtcRow() <<< If this weren’t blank, I could use it immediately rather than
<<< have to force set it.
---> Start SUB: Build_ddnUnitNo()
<--- End SUB: Build_ddnUnitNo()
---> Start SUB: txtcRow_Change ()
<--- End Sub: txtcRow_Change() <<< I can’t seem to move this up. [Edited July 28th, 9:30 EST: Nothing can be done to alter the order of this since it's responding to what's happening inside a form object. But the answer provided from rotabator contributed to help making it a non-issue.
Didn't get the whole idea, but can propose to fix some details.
Create a new module to declare global variables. They can't be declared in worksheet's private code.
Option Explicit
Public WksFlag As Boolean, Subroutine As String
Public WksRow As Integer, LdRow As Integer
Your worksheet private code
Option Explicit
' variables declared here are not global,
' they are just (public) members of a worksheet object
' They, if declared as public, can be accessed outside
' with syntax "Worksheetcodename.WksFlag"
' where "Worksheetcodename" is the name of the worksheet object
' Public WksFlag As Boolean, Subroutine As String
' Public WksRow As Integer, LdRow As Integer
Sub Worksheet_BeforeDoubleClick(ByVal WksUn As Range, WksFlag As Boolean)
Subroutine = "SUB: Wks_B4DblClick"
Debug.Print "---> Start " & Subroutine & "(" & WksUn & ", " & WksFlag & ")"
If WksUn.Column = 1 Then
WksRow = WksUn.Row ' accesses global variable
LdRow = WksRow - 4 ' accesses global variables
' formMealPlan.txtcRow.Value = LdRow ' can't be accessed here
' since txtcRow is the private member of formMealPlan
Load formMealPlan
' any form's public variable can be accessed after form loads
formMealPlan.DemoVar = 5 ' demo
formMealPlan.Show vbModeless
End If
Debug.Print vbNewLine & "<--- End " & Subroutine & "(" & WksUn & ", " & WksFlag & ")"
End Sub
formMealPlan (userform) Code
Option Explicit
' Public LdRow As Integer, WksRow As Integer ' evidently it's extra declaration
Public DemoVar as Integer
Sub UserForm_Initialize()
txtcRow.Value = LdRow ' accesses global variable
Subroutine = "SUB: UserForm_Initialize"
Debug.Print "---> Start " & Subroutine & "()"
Debug.Print "txtcRow(" & txtcRow & ")" ' <<<< This is here to display my point
' I'll uncomment Build_ddnUnitNo for 2nd run
' Build_ddnUnitNo ' Populates the Unit Number drop down list
End Sub