Dynamic Object Naming
 

I have a problem, I am trying to convert one of my first programs to
be more stable. In doing so, I found that one of my form modules is
upwards of 550kb in size and I know that it is causing a lot of my
instability. I have offloaded all private subs to another module to
bring the size down, but I am faced with a number of similar
repetitive textbox_change subs.

The program is for a budget program for a software testing group, so I
have a ton of these textboxes with names like:
txtAlphaMGRHours
txtAlphaSrLeadHours
txtBetaSrTesterDays
txt1PartyTempTesterPeople

and so on...

Each name has 4 parts, for example 'txtAlphaMGRHours' has these parts:
1. 'txt', just a prefix
2. 'Alpha', the phase of the project
3. 'MGR', the row we are calculating
4. 'Hours', the column we are calculating

I have posted a link to the UI here for your reference:
http://drop.wowtcgdeckbuilder.com/budget.png

Here is the change code for the 'txtAlphaMGRHours' text box.
=============================================
Private Sub txtAlphaMGRHours_Change()
'runs when the box changes

If boolLoadAlpha = True Then Exit Sub
If InChange = True Then Exit Sub

InChange = True

'look for a trailing '.' or ','
Dim RightChar As String
RightChar = Right(Me.txtAlphaMGRHours.Value, 1)

If RightChar = "." Or RightChar = "," Then
Me.txtAlphaMGRHours.Value = Left(Me.txtAlphaMGRHours.Value, Len
(Me.txtAlphaMGRHours.Value) - 1)
End If

'check for a valid entry
Me.txtAlphaMGRHours.Value = ValidateChangeX
(Me.txtAlphaMGRHours.Value)

'assign the value to the Total Hours box
dblAlphaMGRTotalHours = Me.txtAlphaMGRPeople.Value * _
Me.txtAlphaMGRHours.Value * _
Me.txtAlphaMGRDays.Value

'get the total dollars for the phase
dblAlphaMGRTotalDollars = GetAlphaTotalDollars(1,
Me.txtAlphaMGRPeople.Value, _

Me.txtAlphaMGRHours.Value, _

Me.txtAlphaMGRDays.Value, _
dblMGRRate)

'assign the variable a value
Me.txtAlphaMGRTotalHours.Value = dblAlphaMGRTotalHours

'assign the variable a value
Me.txtAlphaMGRTotalDollars.Value = FormatCurrency
(dblAlphaMGRTotalDollars)

'assisgn the subphase hours
dblAlpha1Hours = dblAlphaMGRTotalHours + dblAlphaSrLeadTotalHours
+ _
dblAlphaProjectLeadTotalHours +
dblAlphaSrTesterTotalHours + _
dblAlphaTempTesterTotalHours

'Assign the SubPhase Dollars
dblAlpha1Dollars = dblAlphaMGRTotalDollars +
dblAlphaSrLeadTotalDollars + _
dblAlphaProjectLeadTotalDollars +
dblAlphaSrTesterTotalDollars + _
dblAlphaTempTesterTotalDollars

'call the subs that calc the Total Dollars and hours
AlphaTotalDollars
AlphaTotalHours

If RightChar = "." Or RightChar = "," Then
Me.txtAlphaMGRHours.Value = Me.txtAlphaMGRHours.Value &
RightChar
End If

InChange = False

End Sub
=============================================

First mistake I made the when I made this was not knowing how to use a
tab strip which would cut this down by 80%, but I don't have the time
to make a transition like that.

I was thinking about doing something like this...
==============================================
Public Sub HoursTestSub(ObjHours As MSForms.TextBox, _
ObjPeople As MSForms.TextBox, _
ObjDays As MSForms.TextBox)

'look for a trailing '.' or ','
Dim RightChar As String
RightChar = Right(ObjHours.Value, 1)

If RightChar = "." Or RightChar = "," Then
ObjHours.Value = Left(ObjHours.Value, Len(ObjHours.Value) - 1)
End If

'check for a valid entry
ObjHours.Value = ValidateChangeX(ObjHours.Value)
==============================================

But get stuck when i need to assign phase, position, type variables
like 'dblAlphaMGRTotalHours'

Any help here would be great, I may have to switch to tabstrip I
really want to get this down. FYI, the program runs with a form
module size of 590kb, but it requires a save before all the code is
run through.

Dynamic Object Naming
 

You can call Control object dynamically like below. this may be able to
reduce the amount of your code.


boxes = Array("Textbox1", "Textbox2", "Textbox3")
For Each box In boxes
Set mybox = ActiveSheet.OLEObjects(box)
mybox.Object.Text = "Joel"
Next box


"theSquirrel" wrote:

I have a problem, I am trying to convert one of my first programs to
be more stable. In doing so, I found that one of my form modules is
upwards of 550kb in size and I know that it is causing a lot of my
instability. I have offloaded all private subs to another module to
bring the size down, but I am faced with a number of similar
repetitive textbox_change subs.

The program is for a budget program for a software testing group, so I
have a ton of these textboxes with names like:
txtAlphaMGRHours
txtAlphaSrLeadHours
txtBetaSrTesterDays
txt1PartyTempTesterPeople

and so on...

Each name has 4 parts, for example 'txtAlphaMGRHours' has these parts:
1. 'txt', just a prefix
2. 'Alpha', the phase of the project
3. 'MGR', the row we are calculating
4. 'Hours', the column we are calculating

I have posted a link to the UI here for your reference:
http://drop.wowtcgdeckbuilder.com/budget.png

Here is the change code for the 'txtAlphaMGRHours' text box.
=============================================
Private Sub txtAlphaMGRHours_Change()
'runs when the box changes

If boolLoadAlpha = True Then Exit Sub
If InChange = True Then Exit Sub

InChange = True

'look for a trailing '.' or ','
Dim RightChar As String
RightChar = Right(Me.txtAlphaMGRHours.Value, 1)

If RightChar = "." Or RightChar = "," Then
Me.txtAlphaMGRHours.Value = Left(Me.txtAlphaMGRHours.Value, Len
(Me.txtAlphaMGRHours.Value) - 1)
End If

'check for a valid entry
Me.txtAlphaMGRHours.Value = ValidateChangeX
(Me.txtAlphaMGRHours.Value)

'assign the value to the Total Hours box
dblAlphaMGRTotalHours = Me.txtAlphaMGRPeople.Value * _
Me.txtAlphaMGRHours.Value * _
Me.txtAlphaMGRDays.Value

'get the total dollars for the phase
dblAlphaMGRTotalDollars = GetAlphaTotalDollars(1,
Me.txtAlphaMGRPeople.Value, _

Me.txtAlphaMGRHours.Value, _

Me.txtAlphaMGRDays.Value, _
dblMGRRate)

'assign the variable a value
Me.txtAlphaMGRTotalHours.Value = dblAlphaMGRTotalHours

'assign the variable a value
Me.txtAlphaMGRTotalDollars.Value = FormatCurrency
(dblAlphaMGRTotalDollars)

'assisgn the subphase hours
dblAlpha1Hours = dblAlphaMGRTotalHours + dblAlphaSrLeadTotalHours
+ _
dblAlphaProjectLeadTotalHours +
dblAlphaSrTesterTotalHours + _
dblAlphaTempTesterTotalHours

'Assign the SubPhase Dollars
dblAlpha1Dollars = dblAlphaMGRTotalDollars +
dblAlphaSrLeadTotalDollars + _
dblAlphaProjectLeadTotalDollars +
dblAlphaSrTesterTotalDollars + _
dblAlphaTempTesterTotalDollars

'call the subs that calc the Total Dollars and hours
AlphaTotalDollars
AlphaTotalHours

If RightChar = "." Or RightChar = "," Then
Me.txtAlphaMGRHours.Value = Me.txtAlphaMGRHours.Value &
RightChar
End If

InChange = False

End Sub
=============================================

First mistake I made the when I made this was not knowing how to use a
tab strip which would cut this down by 80%, but I don't have the time
to make a transition like that.

I was thinking about doing something like this...
==============================================
Public Sub HoursTestSub(ObjHours As MSForms.TextBox, _
ObjPeople As MSForms.TextBox, _
ObjDays As MSForms.TextBox)

'look for a trailing '.' or ','
Dim RightChar As String
RightChar = Right(ObjHours.Value, 1)

If RightChar = "." Or RightChar = "," Then
ObjHours.Value = Left(ObjHours.Value, Len(ObjHours.Value) - 1)
End If

'check for a valid entry
ObjHours.Value = ValidateChangeX(ObjHours.Value)
==============================================

But get stuck when i need to assign phase, position, type variables
like 'dblAlphaMGRTotalHours'

Any help here would be great, I may have to switch to tabstrip I
really want to get this down. FYI, the program runs with a form
module size of 590kb, but it requires a save before all the code is
run through.