VBA loops and subs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Trying to clean up some code for entering Daily sales figures from a userform
to another sheet. As the below code sits, it works fine and does everything I
want except after several "months" of code VBA says it is to large to compile
(Didn't know there was a limit to size). Tried to clean up the Daily checks
with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an
error loop.
Any hints or suggestions would be greatly appreciated.

Example:
Private Sub cmdEnter_Click()
Sheet2.Select
Dim Msg, Style, Title 'Set up Message Box
Msg = "Sales Already Entered for this Day. Select Another Day" ' Define
message.
Style = vbOKOnly ' Define buttons.
Title = "Entry Error" ' Define MsgBox Title.
Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As
Currency, curWine As Currency, curSoda As Currency
curLiq = tbLiq 'Set inputs to currency for this section
curDraft = tbDraft
curBottle = tbBottle
curCan = tbCan
curWine = tbWine
curSoda = tbSoda
'Start Month Checks
If combMonth = "January" Then ' Check Month Selection
' Start Day Checks
If combDay = "1st" Then
If [c54].Value > 0 Then 'Check if Sales already Entered
GoSub MyResp 'If entered tell to select different day
Else
Range("c54").Activate ' If blank Enter Sales for day
GoSub MySalesUpdate
End If

ElseIf combDay = "2nd" Then
If [c55].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c55").Activate
GoSub MySalesUpdate
End If


ElseIf combDay = "3rd" Then
If [c56].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c56").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "4th" Then
If [c57].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c57").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "5th" Then
If [c58].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c58").Activate
GoSub MySalesUpdate
End If
'etc,etc,etc for the rest of the year
 
this is why we have loops and pass parameters

Dim thisYear As Long
Dim startCell As Range
Dim thisDay As Date
'
thisYear = 2006
Set startCell = Range("C34")
For thisDay = DateSerial(thiyear, 1, 1) To DateSerial(thisYear, 12, 31)
Set target = startCell.Offset(thisDay - DateSerial(thisYear, 1, 1))
If target.Value > 0 Then
MyResp
Else
MySalesUpdate target
End If
Next


''''the procedure called MySalesUpdate needs one pass parameter, a range
object that's passed from the loop. There's no need to actually select the
cell. Also, try to avoid cell references like [A1], instead, use Range("A1")

HTH
 
Use this, you only need one set of code not 31

Private Sub cmdEnter_Click()
Sheet2.Select
Dim Msg, Style, Title 'Set up Message Box
Msg = "Sales Already Entered for this Day. Select Another Day" ' Define
message.
Style = vbOKOnly ' Define buttons.
Title = "Entry Error" ' Define MsgBox Title.
Dim curLiq, curDraft As Currency, curBottle As Currency
Dim curCan As Currency, curWine As Currency, curSoda As Currency
Dim DayOff As Long
curLiq = tbLiq 'Set inputs to currency for this section
curDraft = tbDraft
curBottle = tbBottle
curCan = tbCan
curWine = tbWine
curSoda = tbSoda
'Start Month Checks
If combMonth = "January" Then ' Check Month Selection
DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53
If Range("C" & DayOff).Value > 0 Then 'Check if Sales already
Entered
GoSub MyResp 'If entered tell to select different day
Else
Range("C" & DayOff).Activate ' If blank Enter Sales for day
GoSub MySalesUpdate
End If


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

harrar said:
Trying to clean up some code for entering Daily sales figures from a userform
to another sheet. As the below code sits, it works fine and does everything I
want except after several "months" of code VBA says it is to large to compile
(Didn't know there was a limit to size). Tried to clean up the Daily checks
with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an
error loop.
Any hints or suggestions would be greatly appreciated.

Example:
Private Sub cmdEnter_Click()
Sheet2.Select
Dim Msg, Style, Title 'Set up Message Box
Msg = "Sales Already Entered for this Day. Select Another Day" ' Define
message.
Style = vbOKOnly ' Define buttons.
Title = "Entry Error" ' Define MsgBox Title.
Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As
Currency, curWine As Currency, curSoda As Currency
curLiq = tbLiq 'Set inputs to currency for this section
curDraft = tbDraft
curBottle = tbBottle
curCan = tbCan
curWine = tbWine
curSoda = tbSoda
'Start Month Checks
If combMonth = "January" Then ' Check Month Selection
' Start Day Checks
If combDay = "1st" Then
If [c54].Value > 0 Then 'Check if Sales already Entered
GoSub MyResp 'If entered tell to select different day
Else
Range("c54").Activate ' If blank Enter Sales for day
GoSub MySalesUpdate
End If

ElseIf combDay = "2nd" Then
If [c55].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c55").Activate
GoSub MySalesUpdate
End If


ElseIf combDay = "3rd" Then
If [c56].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c56").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "4th" Then
If [c57].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c57").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "5th" Then
If [c58].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c58").Activate
GoSub MySalesUpdate
End If
'etc,etc,etc for the rest of the year
Click to expand...
 
You can also move all the other subs into different modules, there is a
limit of 64K per module, so break them up.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Thanks for your quick reponse. Unfortunetly, when debugging your code
segement, this line:

Set target = startCell.Offset(thisDay - DateSerial(thisYear, 1, 1))

returned an "Application-defined or Object-defined error

Also, isn't square brackets proper coding versus typing in Evaluate each time.


Patrick Molloy said:
this is why we have loops and pass parameters

Dim thisYear As Long
Dim startCell As Range
Dim thisDay As Date
'
thisYear = 2006
Set startCell = Range("C34")
For thisDay = DateSerial(thiyear, 1, 1) To DateSerial(thisYear, 12, 31)
Set target = startCell.Offset(thisDay - DateSerial(thisYear, 1, 1))
If target.Value > 0 Then
MyResp
Else
MySalesUpdate target
End If
Next


''''the procedure called MySalesUpdate needs one pass parameter, a range
object that's passed from the loop. There's no need to actually select the
cell. Also, try to avoid cell references like [A1], instead, use Range("A1")

HTH


harrar said:
Trying to clean up some code for entering Daily sales figures from a userform
to another sheet. As the below code sits, it works fine and does everything I
want except after several "months" of code VBA says it is to large to compile
(Didn't know there was a limit to size). Tried to clean up the Daily checks
with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an
error loop.
Any hints or suggestions would be greatly appreciated.

Example:
Private Sub cmdEnter_Click()
Sheet2.Select
Dim Msg, Style, Title 'Set up Message Box
Msg = "Sales Already Entered for this Day. Select Another Day" ' Define
message.
Style = vbOKOnly ' Define buttons.
Title = "Entry Error" ' Define MsgBox Title.
Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As
Currency, curWine As Currency, curSoda As Currency
curLiq = tbLiq 'Set inputs to currency for this section
curDraft = tbDraft
curBottle = tbBottle
curCan = tbCan
curWine = tbWine
curSoda = tbSoda
'Start Month Checks
If combMonth = "January" Then ' Check Month Selection
' Start Day Checks
If combDay = "1st" Then
If [c54].Value > 0 Then 'Check if Sales already Entered
GoSub MyResp 'If entered tell to select different day
Else
Range("c54").Activate ' If blank Enter Sales for day
GoSub MySalesUpdate
End If

ElseIf combDay = "2nd" Then
If [c55].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c55").Activate
GoSub MySalesUpdate
End If


ElseIf combDay = "3rd" Then
If [c56].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c56").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "4th" Then
If [c57].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c57").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "5th" Then
If [c58].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c58").Activate
GoSub MySalesUpdate
End If
'etc,etc,etc for the rest of the year
Click to expand...
Click to expand...
 
Thanks for the quick response,Bob. When debugging the code an "Invalid
Procedure Call or Argument" error popped up on this line:

DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53

Thanks again


Bob Phillips said:
Use this, you only need one set of code not 31

Private Sub cmdEnter_Click()
Sheet2.Select
Dim Msg, Style, Title 'Set up Message Box
Msg = "Sales Already Entered for this Day. Select Another Day" ' Define
message.
Style = vbOKOnly ' Define buttons.
Title = "Entry Error" ' Define MsgBox Title.
Dim curLiq, curDraft As Currency, curBottle As Currency
Dim curCan As Currency, curWine As Currency, curSoda As Currency
Dim DayOff As Long
curLiq = tbLiq 'Set inputs to currency for this section
curDraft = tbDraft
curBottle = tbBottle
curCan = tbCan
curWine = tbWine
curSoda = tbSoda
'Start Month Checks
If combMonth = "January" Then ' Check Month Selection
DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53
If Range("C" & DayOff).Value > 0 Then 'Check if Sales already
Entered
GoSub MyResp 'If entered tell to select different day
Else
Range("C" & DayOff).Activate ' If blank Enter Sales for day
GoSub MySalesUpdate
End If


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

harrar said:
Trying to clean up some code for entering Daily sales figures from a userform
to another sheet. As the below code sits, it works fine and does everything I
want except after several "months" of code VBA says it is to large to compile
(Didn't know there was a limit to size). Tried to clean up the Daily checks
with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an
error loop.
Any hints or suggestions would be greatly appreciated.

Example:
Private Sub cmdEnter_Click()
Sheet2.Select
Dim Msg, Style, Title 'Set up Message Box
Msg = "Sales Already Entered for this Day. Select Another Day" ' Define
message.
Style = vbOKOnly ' Define buttons.
Title = "Entry Error" ' Define MsgBox Title.
Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As
Currency, curWine As Currency, curSoda As Currency
curLiq = tbLiq 'Set inputs to currency for this section
curDraft = tbDraft
curBottle = tbBottle
curCan = tbCan
curWine = tbWine
curSoda = tbSoda
'Start Month Checks
If combMonth = "January" Then ' Check Month Selection
' Start Day Checks
If combDay = "1st" Then
If [c54].Value > 0 Then 'Check if Sales already Entered
GoSub MyResp 'If entered tell to select different day
Else
Range("c54").Activate ' If blank Enter Sales for day
GoSub MySalesUpdate
End If

ElseIf combDay = "2nd" Then
If [c55].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c55").Activate
GoSub MySalesUpdate
End If


ElseIf combDay = "3rd" Then
If [c56].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c56").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "4th" Then
If [c57].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c57").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "5th" Then
If [c58].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c58").Activate
GoSub MySalesUpdate
End If
'etc,etc,etc for the rest of the year
Click to expand...
Click to expand...
 
Did you correct my typo?

DayOff = CLng(Left(comboDay, Len(comboDay) - 2)) + 53

Then, is the combo list always of the form 1st6, 2nd, 3rd, etc.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

harrar said:
Thanks for the quick response,Bob. When debugging the code an "Invalid
Procedure Call or Argument" error popped up on this line:

DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53

Thanks again


Bob Phillips said:
Use this, you only need one set of code not 31

Private Sub cmdEnter_Click()
Sheet2.Select
Dim Msg, Style, Title 'Set up Message Box
Msg = "Sales Already Entered for this Day. Select Another Day" ' Define
message.
Style = vbOKOnly ' Define buttons.
Title = "Entry Error" ' Define MsgBox Title.
Dim curLiq, curDraft As Currency, curBottle As Currency
Dim curCan As Currency, curWine As Currency, curSoda As Currency
Dim DayOff As Long
curLiq = tbLiq 'Set inputs to currency for this section
curDraft = tbDraft
curBottle = tbBottle
curCan = tbCan
curWine = tbWine
curSoda = tbSoda
'Start Month Checks
If combMonth = "January" Then ' Check Month Selection
DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53
If Range("C" & DayOff).Value > 0 Then 'Check if Sales already
Entered
GoSub MyResp 'If entered tell to select different day
Else
Range("C" & DayOff).Activate ' If blank Enter Sales for day
GoSub MySalesUpdate
End If


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

harrar said:
Trying to clean up some code for entering Daily sales figures from a userform
to another sheet. As the below code sits, it works fine and does everything I
want except after several "months" of code VBA says it is to large to compile
(Didn't know there was a limit to size). Tried to clean up the Daily checks
with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an
error loop.
Any hints or suggestions would be greatly appreciated.

Example:
Private Sub cmdEnter_Click()
Sheet2.Select
Dim Msg, Style, Title 'Set up Message Box
Msg = "Sales Already Entered for this Day. Select Another Day" ' Define
message.
Style = vbOKOnly ' Define buttons.
Title = "Entry Error" ' Define MsgBox Title.
Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As
Currency, curWine As Currency, curSoda As Currency
curLiq = tbLiq 'Set inputs to currency for this section
curDraft = tbDraft
curBottle = tbBottle
curCan = tbCan
curWine = tbWine
curSoda = tbSoda
'Start Month Checks
If combMonth = "January" Then ' Check Month Selection
' Start Day Checks
If combDay = "1st" Then
If [c54].Value > 0 Then 'Check if Sales already Entered
GoSub MyResp 'If entered tell to select different day
Else
Range("c54").Activate ' If blank Enter Sales for day
GoSub MySalesUpdate
End If

ElseIf combDay = "2nd" Then
If [c55].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c55").Activate
GoSub MySalesUpdate
End If


ElseIf combDay = "3rd" Then
If [c56].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c56").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "4th" Then
If [c57].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c57").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "5th" Then
If [c58].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c58").Activate
GoSub MySalesUpdate
End If
'etc,etc,etc for the rest of the year
Click to expand...
Click to expand...
Click to expand...
 
Thank You Very Much, Bob

Didn't catch the typo but after correcting, the error still appeared. With
a slight alteration though, I got it to work fine by adding a preceeding 0 to
1st, 2nd changing it to 01st, 02nd, etc and changing the Len(combDay) - 2 to
4 - 2)) + 53. It didn't seem to like the nested Len for some reason.

Thanks again for pointing me in the right direction. Saved a lot of coding.

Bob Phillips said:
Did you correct my typo?

DayOff = CLng(Left(comboDay, Len(comboDay) - 2)) + 53

Then, is the combo list always of the form 1st6, 2nd, 3rd, etc.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

harrar said:
Thanks for the quick response,Bob. When debugging the code an "Invalid
Procedure Call or Argument" error popped up on this line:

DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53

Thanks again


Bob Phillips said:
Use this, you only need one set of code not 31

Private Sub cmdEnter_Click()
Sheet2.Select
Dim Msg, Style, Title 'Set up Message Box
Msg = "Sales Already Entered for this Day. Select Another Day" ' Define
message.
Style = vbOKOnly ' Define buttons.
Title = "Entry Error" ' Define MsgBox Title.
Dim curLiq, curDraft As Currency, curBottle As Currency
Dim curCan As Currency, curWine As Currency, curSoda As Currency
Dim DayOff As Long
curLiq = tbLiq 'Set inputs to currency for this section
curDraft = tbDraft
curBottle = tbBottle
curCan = tbCan
curWine = tbWine
curSoda = tbSoda
'Start Month Checks
If combMonth = "January" Then ' Check Month Selection
DayOff = CLng(Left(combDay, Len(comboDay) - 2)) + 53
If Range("C" & DayOff).Value > 0 Then 'Check if Sales already
Entered
GoSub MyResp 'If entered tell to select different day
Else
Range("C" & DayOff).Activate ' If blank Enter Sales for day
GoSub MySalesUpdate
End If


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

Trying to clean up some code for entering Daily sales figures from a
userform
to another sheet. As the below code sits, it works fine and does
everything I
want except after several "months" of code VBA says it is to large to
compile
(Didn't know there was a limit to size). Tried to clean up the Daily
checks
with a DO_WHILE and FOR_NEXT loops but reference errors put my brain in an
error loop.
Any hints or suggestions would be greatly appreciated.

Example:
Private Sub cmdEnter_Click()
Sheet2.Select
Dim Msg, Style, Title 'Set up Message Box
Msg = "Sales Already Entered for this Day. Select Another Day" '
Define
message.
Style = vbOKOnly ' Define buttons.
Title = "Entry Error" ' Define MsgBox Title.
Dim curLiq, curDraft As Currency, curBottle As Currency, curCan As
Currency, curWine As Currency, curSoda As Currency
curLiq = tbLiq 'Set inputs to currency for this section
curDraft = tbDraft
curBottle = tbBottle
curCan = tbCan
curWine = tbWine
curSoda = tbSoda
'Start Month Checks
If combMonth = "January" Then ' Check Month Selection
' Start Day Checks
If combDay = "1st" Then
If [c54].Value > 0 Then 'Check if Sales already Entered
GoSub MyResp 'If entered tell to select different day
Else
Range("c54").Activate ' If blank Enter Sales for day
GoSub MySalesUpdate
End If

ElseIf combDay = "2nd" Then
If [c55].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c55").Activate
GoSub MySalesUpdate
End If


ElseIf combDay = "3rd" Then
If [c56].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c56").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "4th" Then
If [c57].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c57").Activate
GoSub MySalesUpdate
End If

ElseIf combDay = "5th" Then
If [c58].Value > 0 Then 'Check if Data already Entered
GoSub MyResp
Else
Range("c58").Activate
GoSub MySalesUpdate
End If
'etc,etc,etc for the rest of the year
Click to expand...
Click to expand...
Click to expand...
 
Didn't I do well <VBG>

Glad u sorted it.

Bob

harrar said:
Thank You Very Much, Bob

Didn't catch the typo but after correcting, the error still appeared. With
a slight alteration though, I got it to work fine by adding a preceeding 0 to
1st, 2nd changing it to 01st, 02nd, etc and changing the Len(combDay) - 2 to
4 - 2)) + 53. It didn't seem to like the nested Len for some reason.

Thanks again for pointing me in the right direction. Saved a lot of coding.
Click to expand...
 
Back
Top