Function/Macro Limit!??!

[Ryath]

Hi all,

In abit of a dilemma!
What I’ve got so in a spreadsheet is A combobox with an example of the following code (in objects for relating sheet).

Case "Clean Sheet"
Application.Run "Clean"
Case "Part 1 Hours"
Application.Run "Part 1 Hr"
Case "Part 1 Cycles"
Application.Run "Part 1 Cyc"

Each application is a Macro created to perform the calculations, formatting etcetc required to complete the task!
The problem is that I have 176 parts therefore to do calculations in-terms of hours and cycles will indeed required over 300 macros / subs / functions!

I’ve already written the 176 for the hours and released that everytime I open the file Excel would lock, after some digging I realised that there is a limit of 150 in a template (or workbook in my case).
Any suggestions of how I can get over this?

Cheers all!

Will

 

[kodr]

Not enough code to really understand what you're doing, but is it possible to re-use some code?

What I mean is, do you really need seperate macro's for each part type? Can't you just pass variables to a set of standard code for 'hours' and another for 'cycle'?

 

[JerryKlmns]

Will,

Are all your functions / subs so different that you cant use the same part of code with just changing a variable's value?

 

[Ryath]

Here's a sample of one sub:-

Sub Part1Hr()

' W.Chong

Application.ScreenUpdating = False

'Application.Run "FifteenMnthCCheck"

Dim MSNCount As Variant
MSNCount = 0

Dim LoopCount As Variant
LoopCount = Range("PartCalcsHr!A4")

Worksheets("PartCalcsHr").Select
Rows("7:65536").Select
Selection.Delete Shift:=xlUp
Range("A7").Formula = "='Airbus Data'!E2"
Range("B7").Formula = "='Airbus Data'!B2"
Range("C6").Formula = "Part Drop Dead"
Range("C7").Formula = "=IF(HLOOKUP($A7,Constants!$C:$K,94,FALSE)=0,0,IF(IF('Raw Data'!$F7=0,0,((((HLOOKUP($A7,Constants!$C:$K,94,FALSE))-'Raw Data'!$E7)/'Raw Data'!$F7)*365)+'Raw Data'!$C7)>46022,46388,IF('Raw Data'!$F7=0,0,((((HLOOKUP($A7,Constants!$C:$K,94,FALSE))-'Raw Data'!$E7)/'Raw Data'!$F7)*365)+'Raw Data'!$C7)))"
Range("D7").Formula = "=IF(C7=0,0,LOOKUP(C7-90,'Raw Data'!$I7:$X7))"
Range("E7").Formula = "=YEAR(D7)"
Range("A7:E7").Select
Selection.AutoFill Destination:=Range(Cells(MSNCount + 7, Value + 1), Cells(LoopCount + 7, Value + 5)), Type:=xlFillDefault


Range(Cells(MSNCount + 7, Value + 4), Cells(LoopCount + 7, Value + 4)).Select
Selection.Copy
Range("A1").Select
Worksheets("Planner").Select
Cells(MSNCount + 7, Value + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Replace What:="#n/a", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Range("D4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2006"")"
Range("P4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2007"")"
Range("AB4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2008"")"
Range("AN4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2009"")"
Range("AZ4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2010"")"
Range("BL4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2011"")"
Range("BX4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2012"")"
Range("CJ4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2013"")"
Range("CV4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2014"")"
Range("DH4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2015"")"
Range("DT4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2016"")"
Range("EF4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2017"")"
Range("ER4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2018"")"
Range("FD4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2019"")"
Range("FP4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2020"")"
Range("GB4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2021"")"
Range("GN4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2022"")"
Range("GZ4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2023"")"
Range("HL4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2024"")"
Range("HX4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2025"")"
Range("IJ4").Formula = "=COUNTIF(PartCalcsHr!E:E,""2026"")"
Range("D7").Select

Line1:
If MSNCount < LoopCount Then

'Drop Dead Date
Worksheets("PartCalcsHr").Select
Dim YrA As Variant
YrA = Year(Cells(MSNCount + 7, Value + 3))
If YrA > 0 Then
YrA = ((YrA - 2006) * 12)
End If

Worksheets("PartCalcsHr").Select

'Drop Dead Date
Dim MonthA As Variant
MonthA = Month(Cells(MSNCount + 7, Value + 3))

Worksheets("Planner").Select

'Drop Dead Date
If YrA > 0 Then
If YrA <= 251 Then
Cells(MSNCount + 7, MonthA + YrA + 3).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
With Selection.Borders(xlDiagonalDown)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 3
End With
'With Selection.Interior
' .ColorIndex = 3
' .Pattern = xlSolid
'End With
Else
Cells(MSNCount + 7, Value + 3).Select
With Selection.Interior
.ColorIndex = 32
.Pattern = xlSolid
End With
End If
Else
Cells(MSNCount + 7, Value + 3).Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
End If

MSNCount = MSNCount + 1

GoTo Line1
End If
Range("D7").Select
Worksheets("Planner").ComboBox2.Value = ""
End Sub

I thought about just changing it using varibles, but the problem is the change between each function is contained within the .Formula i.e

Range("C7").Formula = "=IF(HLOOKUP($A7,Constants!$C:$K,XXX,FALSE)=0,0,IF(IF('Raw Data'!$F7=0,0,((((HLOOKUP($A7,Constants!$C:$K,XXX,FALSE))-'Raw Data'!$E7)/'Raw Data'!$F7)*365)+'Raw Data'!$C7)>46022,46388,IF('Raw Data'!$F7=0,0,((((HLOOKUP($A7,Constants!$C:$K,XXX,FALSE))-'Raw Data'!$E7)/'Raw Data'!$F7)*365)+'Raw Data'!$C7)))"

where the XXX is what changes!

:/

Will

 

[kodr]

Range("C7").Formula = "=IF(HLOOKUP($A7,Constants!$C:$K,XXX,FALSE)=0,0,IF(IF('Raw Data'!$F7=0,0,((((HLOOKUP($A7,Constants!$C:$K,XXX,FALSE))-'Raw Data'!$E7)/'Raw Data'!$F7)*365)+'Raw Data'!$C7)>46022,46388,IF('Raw Data'!$F7=0,0,((((HLOOKUP($A7,Constants!$C:$K,XXX,FALSE))-'Raw Data'!$E7)/'Raw Data'!$F7)*365)+'Raw Data'!$C7)))"
[\code]

Something like:

[code]
Range("C7").Formula = "=IF(HLOOKUP($A7,Constants!$C:$K," & strVar1 & ",FALSE)=0,0,IF(IF('Raw Data'!$F7=0,0,((((HLOOKUP($A7,Constants!$C:$K," & strVar2 & ",FALSE))-'Raw Data'!$E7)/'Raw Data'!$F7)*365)+'Raw Data'!$C7)>46022,46388,IF('Raw Data'!$F7=0,0,((((HLOOKUP($A7,Constants!$C:$K," & strVar3 & ",FALSE))-'Raw Data'!$E7)/'Raw Data'!$F7)*365)+'Raw Data'!$C7)))"
[\code]

[code]
sub parthr1(byval strVar1 as string, strVar2 as string, strVar3 as string)
[\code]

 

[Ryath]

Coool!!!! Works!
Cheers!

Was in one track mind regarding using values in forumulae S ... didn't think of doing it that way!

Thanks again! )

Will

 

[kodr]

No problem. Simpler is always better.

 

[Ryath]

Dang,

Problem... After testing using my combobox with code:-

Private Sub ComboBox1_Click()
Select Case ComboBox1.Text
Case ""

Case "Clean Sheet"
Application.Run "Clean"
Case "Part 1 Hours"
Dim rowrefhr As String
rowrefhr = 4
Call PartHour(rowrefhr)
Case "Part 2 Hours"
Dim rowrefhr As String
rowrefhr = 6
Call PartHour(rowrefhr)
Case "Part 3 Hours"
Dim rowrefhr As String
rowrefhr = 8
Call PartHour(rowrefhr)
Case "Part 4 Hours"
Dim rowrefhr As String
rowrefhr = 10
Call PartHour(rowrefhr)
Case "Part 5 Hours"
Dim rowrefhr As String
rowrefhr = 12
Call PartHour(rowrefhr)
Case "Part 6 Hours"
Dim rowrefhr As String
rowrefhr = 14
Case Else
End Select
End Sub

I've got the problem of duplicate occurence! (
Any ideas?

Will

 

[GlennUK]

You only need to Dim rowrefhr once, at the beginning of the Sub. It's not an executable statement, so it doesn't go inside code.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GlennUK]

You can shorten your code quite a bit actually:

Private Sub ComboBox1_Click()
Dim rowrefhr As String
Dim temptest As String
temptest = ComboBox1.Text
If Left(temptest, 4) = "Part" Then temptest = "Part"
Select Case temptest
    Case ""
    
    Case "Clean Sheet"
       Application.Run "Clean"
    Case "Part"
        rowrefhr = Mid(ComboBox1.Text, 6, 1) * 2 + 2
        Call PartHour(rowrefhr)
    Case Else
End Select
End Sub

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Ryath]

Thanks Glenn!

Understand what's going on now )
Just that If I haven't used a function before I tend to just go with instinct!
All working well now aswell! D

Cheers,

Will

Will