VBA Code Clean Up

J

Jason1H

New Member
Joined
Sep 17, 2021
Messages
20
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Afternoon everyone!!

I recently joined this forum to better hone my excel skills. Although I am not a new user of excel by any means but I am to the VBA/MACRO world. I am currently reviewing our quality assurance and control documents, implementing formulas & code to simplify things for the guys in the field . As well as minimize the amount of mistakes I am seeing in these documents when filled out. I have a section of code I would like some feed back on, and if there is a better way to write this. I have also included a screen shot of what the formatting looks like. If this is about the best way to write this code, is this pushing the limits are as far processing capability is concerned? I am noticing that it does take a second or two to run through this code when you change your spec in cell F9. This is currently done on Excel2013, I was running 2016 but was having issues with new updates crashing and will be returning back to Excel 2016 once my IT department sorts the issues out. Is there more or better coding options in 2016 compared to 2013? Or is it pretty much the same language and options?


VBA Code: 
Sub Worksheet_SelectionChange(ByVal Target As Range)
    'PVF / Tubing Specs Based on Piping Class
    Sheet1.Unprotect "8unch2015@"
    If Range("f9") = False Then
        Range("f8,f10,f11,f12,f13,y9,y10,y11,y13,AL13,y12") = ""
        Sheet1.Protect "8unch2015@"
        End
    ElseIf Range("f9") = "A 150" Then
        Range("f8") = "Hydrocarbon, unprocessed produced fluids, produced water, slop, fuel gas, flare, drain, gycol/water and Instrument Air"
        Range("f10") = "1/2 - 2, Sch 80(XS), Scrd/SW, A106 GrB"
        Range("f11") = "1/2 - 2, 3000#, Scrd/SW, A105N"
        Range("f12") = "1960 KPag (285 psig)"
        Range("f13") = "2940 KPag (428 psig)"
        Range("y9") = "1/2 - 2, Sch 80, Srcd or PE, A106 GrB"
        Range("y10") = "1/2 - 2, 3000#, Srcd or PE, A105N"
        Range("y11") = "1/2 - 2, Scrd/SW, 2000#, A105N/A216 WCB Body"
        Range("y13") = "3/8 x 0.035 A269 Gr316SS"
        Range("AL13") = "1/2 x 0.049 A269 Gr316SS"
        Range("ar8") = "ASME B31.3-(2018)"
        Range("y12") = "1/2 - 3/4 10,000 PSI, Scrd, SS316"
    ElseIf Range("f9") = "AC 150" Then
        Range("f8") = "Hydrocarbon, unprocessed produced fluids, produced water, slop, fuel gas, flare, drain, gycol/water"
        Range("f10") = "1/2 - 2, Sch 80, Scrd/SW, A106 GrB"
        Range("f11") = "/2 - 2, 3000#, Scrd/SW, A105N"
        Range("f12") = "1900 KPag (275 psig)"
        Range("f13") = "2850 KPag (413 psig)"
        Range("y9") = "1/2 - 2, Sch 80, Srcd or PE, A106 GrB"
        Range("y10") = "1/2 - 2, 3000#, Srcd or PE, A105N"
        Range("y11") = "1/2 - 2, Scrd/SW, 2000#, A105N/A216 WCB Body"
        Range("y13") = "3/8 x 0.035 A269 Gr316SS"
        Range("AL13") = "1/2 x 0.035 A269 Gr316SS"
        Range("ar8") = "ACSA Z662-19"
        Range("y12") = "1/2 - 3/4 10,000 PSI, Scrd, SS316"
     ElseIf Range("f9") = "AI 150" Then
        Range("f8") = "Utility & Instrument Air"
        Range("f10") = "1/2 - 2, Sch 80(XS), Scrd, A106 GrB or PE Galvanized"
        Range("f11") = "3/4 - 2, 2000#, Scrd, A105N Galvanized"
        Range("f12") = "1033 KPag (150 psig)"
        Range("f13") = "1033 KPag (150 psig)"
        Range("y9") = "3/4 - 2, Match Pipe, Scrd, A53 BrB or Galvanized"
        Range("y10") = "3/4 - 2, 2000#, Scrd,  A105N or A234 WPB Galvanized"
        Range("y11") = "1/2 - 2, Scrd/SW, 2000#, A105N/A216 WCB Body"
        Range("y13") = "3/8 x 0.035 A269 Gr316SS"
        Range("AL13") = "1/2x 0.035 A269 Gr316SS"
        Range("ar8") = "ASME B31.3-(2018)"
        Range("y12") = "1/2 - 3/4 10,000 PSI, Scrd, SS316"
    ElseIf Range("f9") = "AL 150" Then
        Range("f10") = "1/2 - 2 sch80 A333"
        Range("f11") = "1/2 - 2 3000# A350 LF2"
        Range("y9") = "1/2 - 2 sch80 A333"
        Range("y10") = "1/2 - 2 3000# A350 LF2"
        Range("y11") = "1/2 - 2 2000# A350 LF2"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "1/2 x 0.049"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "ALS 150" Then
        Range("f10") = "1/2 xxs, 3/4 - 1 1/2 sch160, 2 sch80 A333"
        Range("f11") = "1/2 - 1 1/2 3000#, 2 sch80 A350 LF2"
        Range("y9") = "1/2 xxs, 3/4 - 1 1/2 sch160, 2 sch80 A333"
        Range("y10") = "1/2 - 2 3000# A350 LF2"
        Range("y11") = "1/2 - 1 1/2 2000#, 2 150# A350 LF2"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "1/2 x 0.049"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "ALSC" Then
        Range("f10") = "1/2 - 1 1/2 sch160, 2 sch40 A106"
        Range("f11") = "1/2 - 1 1/2 3000#, 2 sch40 A105N"
        Range("y9") = "1/2 - 1 1/2 sch160 A106"
        Range("y10") = "1/2 - 2 3000# A105N"
        Range("y11") = "1/2 - 1 1/2 2000# A105N"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "1/2 x 0.035"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "AS" Then
        Range("f10") = "1/2 xxs, 3/4 - 1 1/2 sch160 A106"
        Range("f11") = "1/2 - 1 1/2 3000# A105N"
        Range("y9") = "1/2 xxs, 3/4 - 1 1/2 sch160 A106"
        Range("y10") = "1/2 - 2 3000# A105N"
        Range("y11") = "1/2 - 1 1/2 2000# A105N"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "1/2 x 0.049"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "BLS" Then
        Range("f10") = "1/2 - 1 1/2 XXS A333"
        Range("f11") = "1/2 - 1 1/2 3000# A105N LF2"
        Range("y9") = "1/2 - 1 1/2 XXS A333"
        Range("y10") = "3/4 - 2 3000# A350 LF2"
        Range("y11") = "1/2 2000# A350 LF2"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "1/2 x 0.049"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "BS" Then
        Range("f10") = "1/2 - 1 1/2 XXS A106"
        Range("f11") = "1/2 - 1 1/2 3000# A105N"
        Range("y9") = "1/2 - 1 1/2 XXS A106"
        Range("y10") = "1/2 - 2 3000# A105N"
        Range("y11") = "1/2 2000# A105N"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "1/2 x 0.049"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "C" Then
        Range("f10") = "1/2 - 2 sch160 A106"
        Range("f11") = "1/2 - 2 3000# A105N"
        Range("y9") = "1/2 - 2 sch160 A106"
        Range("y10") = "1/2 - 2 3000# A105N"
        Range("y11") = "1/2 - 2 3000# A105N"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "1/2 x 0.049"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "CL 600" Then
        Range("f10") = "1/2 - 2 sch160 A333"
        Range("f11") = "1/2 - 2 3000# A350 LF2"
        Range("y9") = "1/2 - 2 sch160 A333"
        Range("y10") = "1/2 - 2 3000# A350 LF2"
        Range("y11") = "1/2 - 2 3000# A350 LF2"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "1/2 x 0.049"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "CLS 600" Then
        Range("f10") = "1/2 - 1 1/2 XXS A333"
        Range("f11") = "1/2 - 1 1/2 6000# A350 LF2"
        Range("y9") = "1/2 - 1 1/2 XXS A333"
        Range("y10") = "3/4 -2 6000# A350 LF2"
        Range("y11") = "1/2 3000# A350 LF2"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "1/2 x 0.049"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "CLSC" Then
        Range("f10") = "1/2 - 1 1/2 sch160 A106"
        Range("f11") = "1/2 - 1 1/2 3000# A105N"
        Range("y9") = "1/2 - 1 1/2 sch160 A106"
        Range("y10") = "1/2 - 2 3000# A105N"
        Range("y11") = "1/2 - 1 1/2 3000# A105N"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "1/2 x 0.035"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "CLSZ" Then
        Range("f10") = "1/2 - 1 1/2 sch160"
        Range("f11") = "1/2 - 1 1/2 3000#"
        Range("y9") = "1/2 - 1 1/2 sch160"
        Range("y10") = "1/2 - 2 3000#"
        Range("y11") = "1/2 - 1 1/2 3000#"
        Range("y13") = ""
        Range("AL13") = ""
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "CS" Then
        Range("f10") = "1/2 - 1 1/2 XXS A106"
        Range("f11") = "1/2 - 1 1/2 6000# A105N"
        Range("y9") = "1/2 - 1 1/2 XXS A106"
        Range("y10") = "1/2 - 1 1/2 6000# A106"
        Range("y11") = "1/2 3000# A105N"
        Range("y13") = "3/8 x 0.049"
        Range("AL13") = "1/2 x 0.049"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "DS" Then
        Range("f10") = "1/2 - 3/4 XXS A106"
        Range("f11") = "1/2 - 3/4 6000# A105N"
        Range("y9") = "1/2 - 3/4 XXS A106"
        Range("y10") = "1/2 - 3/4 6000# A105N"
        Range("y11") = "1/2 6000# A105N"
        Range("y13") = "3/8 x 0.065"
        Range("AL13") = "1/2 x 0.065"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "EL" Then
        Range("f10") = "1/2 - 1 1/2 XXS A333"
        Range("f11") = "1/2 - 1 1/2 6000# A350 LF2"
        Range("y9") = "1/2 - 1 1/2 XXS A333"
        Range("y10") = "1/2 - 2 6000# A350 LF2"
        Range("y11") = "1/2 - 1 1/2 6000# A350 LF2"
        Range("y13") = "3/8 x 0.065"
        Range("AL13") = "1/2 x 0.065"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "ELS 1500" Then
        Range("f10") = "1/2 - 3/4 XXS A333"
        Range("f11") = "1/2 - 3/4 6000# A350 LF2"
        Range("y9") = "1/2 - 3/4 XXS A333 "
        Range("y10") = "1/2 - 3/4 6000# A350 LF2"
        Range("y11") = "1/2 6000# A350 LF2"
        Range("y13") = "3/8 x 0.065"
        Range("AL13") = "1/2 x 0.065"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "ELSC" Then
        Range("f10") = "1/2 - 1 XXS A106"
        Range("f11") = "1/2 - 1 6000# A105N"
        Range("y9") = "1/2 - 1 XXS A106"
        Range("y10") = "1/2 - 1 6000# A105N"
        Range("y11") = "1/2 - 1 6000# A105N"
        Range("y13") = "3/8 x 0.065"
        Range("AL13") = "1/2 x 0.065"
        Range("y12") = "10,000 PSI SS316"
    ElseIf Range("f9") = "FLS" Then
        Range("f10") = "1/2 - 3/4 XXS A333"
        Range("f11") = "1/2 - 3/4 6000# A350 LF2"
        Range("y9") = "1/2 - 1 XXS A333"
        Range("y10") = "1/2 - 3/4 6000# A350 LF2"
        Range("y11") = "1/2 A350 LF2"
        Range("y13") = "3/8 X 0.065"
        Range("AL13") = "1/2 X 0.083"
        Range("y12") = "10,000 PSI SS316"
  ElseIf Range("f9") = "SFLS 150" Then
        Range("f10") = "3/4 - 1 1/2 sch80 A333"
        Range("f11") = "3/4 - 1 1/2 2000# A350 LF2"
        Range("y9") = "3/4 - 1 1/2 sch80 A333"
        Range("y10") = "3/4 - 2 2000# A350 LF2"
        Range("y11") = "3/4 - 2 2000# A350 LF2"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "3/8 x 0.035"
        Range("y12") = "3000 PSI"
  ElseIf Range("f9") = "SRLS 600 (PN100)" Then
        Range("f10") = "3/4 - 1 1/2 sch160 A333"
        Range("f11") = "3/4 - 1 1/2 2000# A350 LF2"
        Range("y9") = "3/4 - 1 1/2 sch80 A333"
        Range("y10") = "3/4 - 2 2000# A350 LF2"
        Range("y11") = "3/4 - 2 2000# A350 LF2"
        Range("y13") = "3/8 x 0.035"
        Range("AL13") = "1/2 x 0.049"
        Range("y12") = "3000 PSI"
        End If
    Cancel = True
    Application.EnableEvents = True
    Sheet1.Protect "8unch2015@"
  
End Sub

Specifications Image.JPG


Thanks guys for any and all input.
 
Last edited by a moderator:

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
I guess my first question would be why input the values through VBA instead of using vlookups in cells F10- F11, Y9-Y13 and AL13 that pull from an "inputs" tab depending on the value in F9?

That would also solve the fact that it is taking a while to load. I personally try to make my code as dynamic as possible, in this case excel is hard coding strings on each cell which is part of the reason why it's running as slow as it is. You might also want to look at loops, arrays, I also used a scripting dictionary once similar to a javascript object - (key/value pairs).
 
Last edited:
Upvote 0
In this case I think vlookups would be the way to go and only use VBA to protect or unprotect the sheet. Which if you use vlookups I believe you can keep the sheet protected at all times and you wouldn't even need that piece of code.
 
Upvote 0
I thought of using V/H lookup. I’ll just have to hide the second sheet is all. I figured I would try some coding. How would loops/ arrays work?
 
Upvote 0
I would go with the lookups as suggested by @avd88.

Not much point in a loop as it's the same cell (f9) that is used each time.
You could probably use a Select Case instead of ElseIf's...but it still comes back to Native EXcel functions being a better option if they are vailable.
AND
In future could you please use code tags when posting code....simply press the little VBA button on the reply toolbar and paste the code between them where the cursor is flashing.....makes it so much easier to read and Debug...(y)
 
Upvote 0
Solution
Duly noted on the vba code button. Thanks guys for the feed back.
 
Upvote 0
If you want to practice your coding skills I'd suggest looking into Udemy. I've done a couple of programming courses in other languages. There should be VBA courses in there but at the end of the day a lot of the concepts you learn are applicable with most any language. Good luck!
 
Upvote 0
Thanks avd88, I have been subscribed to the GoSkills platform for quite some time now, taking refresher courses as well as new.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
317
RoryA
RoryA
S
  • Question
Rank Formula - When a Range Contains Zeroes
Replies
3
Views
261
Peter_SSs
Peter_SSs
I
  • Question
Copy column value but skip over to next column if empty
Replies
0
Views
308
inwonderland7
I
P
  • Solved
Keep W-L of card game played over time
Replies
6
Views
410
Preacherman771
P
K
  • Solved
Get min value in floating range
Replies
9
Views
308
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,559
Messages
6,120,203
Members
448,951
Latest member
jennlynn

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top