Is excel vba able to store a few lines of steps As..smt

[edlim85]

Hello again,

I am doing a macro for tier system calculation facing a issue right now. "procedure is too large" . Splitting my macro will be my last resort.

My question: If i have similar procedure that are repeated in my macro, can it be store somewhere and i can pull when i need it?
***will this reduce my macro size and solve my "procedure too large issue"??

let me explain my macro a bit more.
I have a lot of "IF...Then" formula in my macro to check for several conditions. Those conditions will ultimately determine which one of the 14 tier to apply.

As in tier i mean, and i have 14 of such.
<TABLE style="WIDTH: 154pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=206><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17 width=71>First</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=71 align=right>$100,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=64 align=right>4.0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17>Next</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>$150,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>5.0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17>Next</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>$250,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>6.0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17>Next</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>$250,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>7.0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17>Next</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>$250,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>8.0%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17>Next</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>$250,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>10.0%</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=18>Thereafter</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>10.0%</TD></TR></TBODY></TABLE>

Example: Jus like to "Dim MyText As String" and vBA will store the cell data which Mytext points to.

Can I store a procedure example ...Dim TierCal1 As "procedure"

If CYNPI <= LTier1 Then
CalcCost = CYNPI * LTier1_Gperc
wNPI.Cells(a, 10).Value = CalcCost

ElseIf (CYNPI > LTier1) And (CYNPI <= LTier1 + LTier2) Then
CalcCost = (LTier1 * LTier1_Gperc) + ((CYNPI - LTier1) * LTier2_Gperc)
wNPI.Cells(a, 10).Value = CalcCost

ElseIf (CYNPI > LTier1 + LTier2) And (CYNPI <= LTier1 + LTier2 + LTier3) Then
CalcCost = (LTier1 * LTier1_Gperc) + (LTier2 * LTier2_Gperc) + ((CYNPI - (LTier1 + LTier2)) * LTier3_Gperc)
wNPI.Cells(a, 10).Value = CalcCost

Else
CalcCost = (LTier1 * LTier1_Gperc) + (LTier2 * LTier2_Gperc) + (LTier3 * LTier3_Gperc) + ((CYNPI - (LTier1 + LTier2 + LTier3)) * LTier4_Gperc)
wNPI.Cells(a, 10).Value = CalcCost
End If

Pls Advise me
Thank you.

Regards
Edmund

 

[Jerry Sullivan]

Hello again Edmund,

You can't store a Procedure as a data type, but you can break out parts of your Procedure into Functions that take Parameters and return results. This is the best way to handle your situation of having a lot of steps that are repeating the same process on different variables.

 

[edlim85]

Hi JS411,

You can't store a Procedure as a data type, but you can break out parts of your Procedure into Functions that take Parameters and return results. This is the best way to handle your situation of having a lot of steps that are repeating the same process on different variables.

Do you mind giving me a simple example. I dont understand.

Thanks

Sincerely,
Edmund

 

[Jerry Sullivan]

Let's say you're manufacturing round discs from square metal stock. You have a macro that helps with your waste management and at several points the macro needs to calculate the amount of scrap left over from each piece of stock after the circle has been cut out.

The formula will be Scrap = Area of the Square - Area of the Circle
The result is placed in the Cell below the Cell that has the Length of the Square.

You could repeat the process of calculating the scrap throughout the macro like this...

Sub LargeProcedure()
    Dim dblLength As Double
    Dim dblCircle As Double
    Dim dblSquare As Double
    Dim dblScrap As Double
    Const PI = 3.14159265358979
    
    dblLength = Range("A1")
    dblCircle = PI * (0.5 * dblLength) ^ 2
    dblSquare = dblLength ^ 2
    dblScrap = dblSquare - dblCircle
    Range("A2") = dblScrap
    
    dblLength = Range("C3")
    dblCircle = PI * (0.5 * dblLength) ^ 2
    dblSquare = dblLength ^ 2
    dblScrap = dblSquare - dblCircle
    Range("C4") = dblScrap
    
    dblLength = Range("Z5")
    dblCircle = PI * (0.5 * dblLength) ^ 2
    dblSquare = dblLength ^ 2
    dblScrap = dblSquare - dblCircle
    Range("Z6") = dblScrap
End Sub

Or you could write a function that takes the Length as an Input and returns the Area of Scrap as Output...

Function CalcScrap(dblLength As Double) As Double
    Dim dblCircle As Double
    Dim dblSquare As Double
    Const PI = 3.14159265358979
    dblCircle = PI * (0.5 * dblLength) ^ 2
    dblSquare = dblLength ^ 2
    CalcScrap = dblSquare - dblCircle
End Function

Now you can have a single line in your Main Procedure for each place you want to calculate the scrap.

Sub SmallerProcedure()
    Range("A2") = CalcScrap(Range("A1"))
    Range("C4") = CalcScrap(Range("C3"))
    Range("Z6") = CalcScrap(Range("Z5"))
End Sub

Hopefully you won't find this example too convoluted. (I was trying to think of something that wasn't too simple to be trivial or too complicated that it was hard to follow).

I think if you can adopt the same approach in your code it will make your "procedure is too large" problems go away.