Make Copies Before Using The Worksheet: Fill & Download for Free

Thanks for the A2A request.This is a great question. I’m surprised I haven’t seen it before.Here is a list of things that I’ve seen learners get wrongUsing Select and Activate when copying dataYou don’t need to select a range to copy it. Not only is it wasteful but it is very slow.You can copy by assignmentSheet1.Range("A1").Value = Sheet1.Range("C2").Value Sheet1.Range("A1:C12").Value = Sheet1.Range("F1:H12").Value This is the fastest way to copy. You can copy between any open worksheets this waySheet2.Range("A1").Value = Sheet1.Range("C2").Value Sheet2.Range("A1:C12").Value = Sheet1.Range("F1:H12").Value Using assignment copies values only. If you want to copy everything(formulas, formats etc.) use' Copies everything Sheet1.Range("C2").Copy Sheet1.Range("A1") If you want to copy individual formats, formulas etc. use PasteSpecialSheet1.Range("C2").Copy  Sheet1.Range("A1").PasteSpecial xlPasteFormats Sheet1.Range("A1").PasteSpecial xlPasteComments SeeThe Ultimate VBA TutorialHow to copy and paste cellsReading values from one cell to anotherNot using the code name of the worksheetEach worksheet has a code name. It is the name to the left of the parentheses in the project window(see the code name of the workbook).You can use the code name to reference a worksheet if the worksheet is in the same workbook:Sheet1.Range("A1") If the user changes the worksheet name then your code will still work fine.Anywhere you see thisThisWorkbook.Worksheets ("Sheet1") you can replace with the code name of the worksheet.Putting all the code in one subMost new users place all their code in one sub. They can often have 100+ lines of code in one sub.This makes code inflexible. It makes it difficult to find errors and difficult to update the code.You should break your code into sub/functions. Each of these should be dedicated to a single task. For example:Sub CreateWeeklyReport()   ' Read the raw data  ReadData    ' Perform calculations on the data  PerformCalcs    ' write the data to the report  WriteData  End Sub Not declaring variablesThe default in VBA is not to use declare variables.' Use total without declaring Total = 7 ' Declare Total Dim Total As Long  Total = 7 This can lead to errors such as spellings errors not being detected(See Is Dim actually required).When Dim is not used the variable is a Variant by default and this has its’ own set of issues.You can set make Dim mandatory by placing Option Explicit at the top of the module.If you want this to happen automatically select Tools->Options and check the Require Variable Declaration checkbox. Now when you open a module you will see Option Explicit at the top.Turn off Excel FunctionalityWhen we run our code we want to turn off certain functionality to make it run faster.It use a function like the one below to turn off the functionalityPublic Sub TurnOffFunctionality()  Application.Calculation = xlCalculationManual  Application.DisplayStatusBar = False  Application.EnableEvents = False  Application.ScreenUpdating = False End Sub I use this one to turn it back onPublic Sub TurnOnFunctionality()Public Sub TurnOnFunctionality()  Application.Calculation = xlCalculationAutomatic  Application.DisplayStatusBar = True  Application.EnableEvents = True  Application.ScreenUpdating = True End Sub You can use them like thisSub Main()   TurnOffFunctionality    ' Your code here    TurnOnFunctionality  End Sub Turning off Calculation, DisplayStatusBar and ScreenUpdating will make your macros run faster.Turning off events using EnableEvents prevents events like Worksheet_Change from running. If these run they can cause data errors or even end up crashing excel if an infinite sequence is created.Note: Sometimes your code will have an error and it will not reach TurnOnFunctionality.If this happens you can place the cursor in the TurnOnFunctionality sub and press F5(or select Run->Run Sub/UserForm from the menu).Not using Complete Word(Ctrl + Space)Add this line of codeDim MyRange As Range Type My on the next line and press Ctrl + Space. VBA will automatically complete the line.If there are several possible options then VBA will present a list for you to select from.Not using Debug.CompileDebug.Compile is a magical but almost unknown tool in VBA.There are three types of errors in VBASyntaxCompileRuntimeSyntax errors are errors where incorrect syntax is used on one line e.g.' then is missing If a > b  ' equals is missing after i For i 2 To 7  ' missing right parenthesis b = left("ABCD",1 Compile errors are errors that relate to more than one line. Here are some examples:If statement without corresponding End If statementFor without NextSelect without End SelectCalling a Sub or Function that does not existCalling a Sub or Function with the wrong parametersGiving a Sub or Function the same name as a moduleVariables not declared(Option Explicit must be present at the top of the module)We can find all compile errors in our code by running Debug->Compile from the menu.When we run it, it will display the first error that it finds. It includes both syntax and compile errors in it’s search.When we fix the current error we can run Debug->Compile to find the next one.When there are no more errors in our code, Debug->Compile will be disabled in the menu. Just add more code to enable it.Running the code will also find errors. However, it will only find errors in subs that it runs. Debug->Compile will look for errors in the entire project.Using Mid, Left, Right instead of Split for string extractionThe function Mid, Left and Right are fine when you are dealing with a fixed size string:Sub ExtractString()   Dim s As String: s = "ABCD-7789.WXYZ"   Debug.Print Left(s, 2) ' Prints AB  Debug.Print Left(s, 4) ' Prints ABCD   Debug.Print Right(s, 2) ' Prints YZ  Debug.Print Right(s, 4) ' Prints WXYZ   Debug.Print Mid(s, 1, 2) ' Prints AB  Debug.Print Mid(s, 6, 4) ' Prints 7789  End Sub But if the string can be different sizes(e.g. a person’s name) then they can be tricky to use.For example, if we want to get the last name from a string it can be tricky because all the names can be of varying sizesSub GetLastName()   Dim s As String: s = "John,Henry,Smith"  Dim Position As Long, Length As Long   Position = InStrRev(s, ",")  Length = Len(s)   ' Prints Smith  Debug.Print Right(s, Length - Position)   ' Alternative method. Prints Smith - do in one line  Debug.Print Right(s, Len(s) - InStrRev(s, ","))  End Sub We can use the Split function to do this much more easilyDim s As String: s = "John Henry Smith"  Debug.Print Split(s, " ")(0) ' John Debug.Print Split(s, " ")(1) ' Henry Debug.Print Split(s, " ")(2) ' Smith We can store the result of Split in an arrayDim s As String: s = "John Henry Smith"  Dim arr As Variant arr = Split(s, " ")  Debug.Print arr(0) ' John Debug.Print arr(1) ' Henry Debug.Print arr(2) ' Smith See Using Split.ConclusionThere are many more thing that learners get wrong, but these are the most common ones that I see on a regular basis.ReferencesThe Ultimate VBA Tutorial Part 1The Complete Guide to Ranges and Cells in Excel VBAThe Complete Guide To The VBA WorksheetVBA Dim – A Complete GuideVBA Error TypesUsing Split instead of Instr

Recorded VBA macros are invariably poorly written code. They are much longer than they need to be and run slower. They make it difficult for users to figure out how to loop through a range of cells or worksheets. They encourage bad practices like activating and selecting cells and worksheets. They record statements that you never performed in a misguided zeal to be complete. They are difficult to debug and update. Recorded macros are bad, bad, bad.Every Excel VBA expert records macros, sometimes several times a day. We do it because we don't remember the syntax of the objects, methods and properties needed to do things like create a PivotTable or update Page Setup settings. And when we are done editing the recorded code, you can't tell it from VBA code written from scratch.The Debug...Compile VBAProject feature will find most of your syntax errors. It will find your typos too, if you use Option Explicit (check the box for Require Variable Declaration in the Tools...Options...Editor menu item).The Immediate Window is really useful when you are debugging your code. This is especially helpful when you get a runtime error and are dumped into the Debugger. If you don't see it underneath your code, display it using the View...Immediate Window menu item. The Immediate Window can be used to interrogate or set values or launch subs. It works only on single line instructions, and will perform the requested action as soon as you hit Enter. A question mark asks the Immediate Window to evaluate a variable or expression, and print the result on the next line.? MyVariable 'returns the value of the variableMyVariable = 42 'sets the value of MyVariable, overwriting any previous valueMySub Foo, Charlie 'runs sub MySub using parameters Foo and CharlieWhen you get a runtime error and are dumped into the debugger, you can grab the yellow arrow and move it to a previous statement and resume operation from that point (very useful if you have changed some of the intervening statements to address the error). I was in a room full of Excel MVPs when this trip was shared, and almost none of them knew the trick. Keep it in your hip pocket, impress even experienced VBA coders when you use it in a demo.In the Debugger, you can execute one statement at a time with F8. Or you can execute until the next Stop (big maroon dot to left of the statement) using F5.Almost everyone who writes VBA code does so only as a small part of their job. Most of their time is spent doing other things, including processing paper. But the "processing paper" part of the job takes much more time than ought to be necessary. If you are handy with VBA code, you should be able to double your productivity after a few years. By that, I mean you can literally handle twice the business volume without working any harder.If you want to make your code resistant to users who rename worksheets, start using the worksheet codename instead of the tab name. This appears in the VBA Project Explorer pane to the left of the code. You may see Sheet1 (New Business) in the Explorer pane. Sheet1 is the codename and New Business is the tab name (what you see in the worksheet tab in the normal worksheet user interface). You can change the codename to something easier to remember using the Properties pane (View...Properties Window) by typing the new codename in the (Name) field, then hitting Enter. You use the codename in your code like MyCodename.Range("A1") instead of Worksheets("Sheet1").Range("A1").While not impregnable, you can store confidential information on a hidden worksheet that a user can't unhide using the Unhide... item in the context menu when you rightclick a sheet tab. The trick is to say Worksheets("Confidential").Visible = xlSheetVeryHidden. Ditto for named ranges that you want to hide in the Name Manager dialog: ThisWorkbook.Names("CEO Salary").Visible = FalseIf you don't want your subs to appear in the ALT + F8 macro selector, just declare them with an optional variable. You don't need to use that variable in your code, and you can continue to call the sub from other macros the same as always.Sub MySub(Optional b As Boolean) About the only time that you really do need to Select a cell is when you are setting conditional formatting with formulas and relative addresses. In that limited circumstance, you will get absolutely crazy formulas if the active cell is anything other than the top left cell of the range being conditionally formatted.Never write a macro that deletes all the Shapes on a worksheet. The Shapes collection includes a lot of stuff that you don't want to delete like data validation dropdowns, cell comment boxes, textboxes, charts, command buttons. And if you ignore my advice and delete the data validation dropdowns, you cannot restore it or add another one to the worksheet. You will either need to revert to an older version of the file or laboriously copy the worksheet information onto a newly added worksheet.You will be retired before Microsoft deprecates your VBA code. Gosh, you can still run Excel 4 macros, and they were replaced by VBA in Excel 5 around 1993. So many millions of business critical VBA macros have been written, that VBA support will continue for many decades into the future.

I automated my university’s class scheduleSince I joined my university, I found it quite hard to look for my classes. Our timetable is based on Excel sheet and this is how it looks like for a day (only for BSCS students):Here is how we find our schedule:We open up the worksheet according to current day.All batches have specific color (since I am in sophomore year so my classes have pink color in cells’ background) so then we look for the cells having our batch color.We look for courses in which we are registered.We look for the section of course in which we are registered.At last, we check the venue (the left-most column).It was exhausting to do it repeatedly in all 8 slots and our timetable often changes, so we can’t simply remember the schedule.Solution: I developed a PHP application: TimeTable Notifier which in start used to show classes on web page but then I made it to send emails to all of the registered students with their schedules. I used MySQL to store the data (name, email, sections, courses) of students, PHPExcel to parse classes from the Excel sheet & then used crontab, PHPMailer & SendGrid to send emails daily (except weekend) with students’ next day’s classes sorted according to the timing..Here is how an email looks like:Post-Solution: After completing this, I found myself lazy to grab the updated copy of timetable when we get a new one so I wrote a python webcrawler earlier today to grab an excel sheet from here: BSCS Time Table Fall-17 - Timetable Computer Science Karachi, modify it to minimalist form & then push it to github (private repo.) & on live server too.UPDATE: Even opening the email again and again was hectic and being one of the most laziest creatures on this planet, I developed an Android application that fetches whole week timetable from web API, saves it locally, shows whole week classes anytime you need those and notifies 10 minutes before every class: FAST-NU KHI Timetable Notifier - Android Apps on Google PlayI will really appreciate feedback to make it better as I am nowhere near to call myself good at development :)