![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Microsoft 365 and Office | Excel | For home | Windows
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
#1
I use excel 365 to develop financial models and dashboards for business data and financial analysis. By the time I am realizing that Ms Excel has some limitations which does not allows me to obtain the exact information that I needed very quickly and by automation.
#2
I want to learn VBA as an absolute beginner like the "ABC" "123".
#3
I dont even know what is meant by a "code" and "script" I have seen many videos over the youtube but everytime they write something new and thats a nightmare for me. I didnt learn .0001% from any video. I dont know what this sub end sub etc mean. why they put "." between the words etc.
"I want to learn VBA" is a big question.
I can see why most YouTubes are confusing. They assume a certain basic understanding of VBA, which you don't have yet (and you know you don't have).
.
#1 Excel Limitations and automation
Yes, VBA provides the orginal method of automation in Excel, and provides low level access to Excel. It is certainly a good way. But another alternative is the "new" (since 2010 ...) "Power Tools" features. They provide a great deal of automation and a surprising functionality. With more in depth knowledge of the PowerTools, you may be able to do what you want, without VBA.
.
.
#2 Links to some VBA Learning resources
.
One place you can go is to the following link. It links to a free webinar being offered all next week. Although it is not specifically about VBA, it is about PowerQuery and other 'PowerTools", they do mention VBA. The webinar is educational, but at the end it is also a shill for their online training offering, which does include VBA modules.
.
.
Free Webinar about PowerQuery / PowerPivot / PivotTools / Dashboards: to Thur May 14
.
If you are impatient, here is a link to the recording of today's webinar:
https://members.excelcampus.com/blueprint-webinar-replay
.
Here is the page for their educational offering that is made at the end of the webinar:
https://www.excelcampus.com/join-elevate/
.
Take a look at the page, join one of the webinars to see if
.
Here are links to some other websites. Some specialize in Excel VBA, so more more general with articles about Excel features including VBA
.
THE MYEXCELONLINE ACADEMY ONLINE EXCEL COURSE! - https://www.myexcelonline.com/academy/enroll-now/
I have taken some of MyExcelOnline courses. They are pretty good. The above link includes course outlines and links to some sample s
http://excelchamps.com/blog/category/vba/
Wellsr VBA Tutorials https://wellsr.com/vba/
Writing macros using VBA, an introduction – CodeVBA.COM. http://codevba.com/learn/vba.htm
https://wellsr.com/vba/excel/ - Collection of macros you can download .
MyOnlineTrainingHub Blog http://www.myonlinetraininghub.com/category/excel-vba to Mar 2016.
Better Solutions: www.bettersolutions.com/ .
Excel Matters: http://excelmatters.com/ .
ExcelVBA: http://excelevba.com.br/ .
Mr. Excel: www.mrexcel.com .
Ozgrid: www.ozgrid.com/VBA/find-method.htm .
Pearson Software Consulting: www.cpearson.com/ .
Ron de Bruin Excel Automation: www.rondebruin.nl/win/section2.htm .
StackExchange: http://stackexchange.com/ .
StackOverflow: http://stackoverflow .
VB-fun.de: www.vb-fun.de/ .
Yoda Learning - https://yodalearning.com/tutorials/category/excel/ .
Changoo https://chandoo.org/wp/welcome/ https://chandoo.org/wp/excel-vba/
.
.
https://excelribbon.tips.net/index.html - this is a site I've subscribed to for a long time. It provides a weekly newsletter with several short 1-page "Tips". You can also search the site for specific tips. As well they have some resources, including collections of their TIPS and a few tutorial books, including some specifically about VBA.
.
.
https://www.computer-pdf.com/office/excel/ this site has many short PDF books on various subjects
Although this next ebook is "older" the concepts still apply in 365
E anal2003- Automated Data Analysis Using Excel 2003.pdf (2007) 478pg (downloaded) Brian_D._Bissett_
https://drive.google.com/file/d/0B0DBjBjJsSoLZ2t0aHhTRWVKSUU/view?usp=sharing
.
e anal 2016- data analysis with Excel 2016 tutorial.pdf (downloaded) Free
https://www.pdfdrive.com/data-analysis-with-excel-e30591074.html
558 Pages · 2016 · by Priya Sen
.
e anal 2007- Data Analysis with Microsoft Excel 2007 3rd ed-2010( PDFDrive.com ).pdf (downloaded) Free
https://www.pdfdrive.com/data-analysis-with-microsoft-excel-updated-for-office-2007-e157317287.html
613 Pages · 2009 · by Kenneth N. Berk & Partrick Carey
.
This book has 3 chapters, about 100 pages on VBA
E formula- Excel 2016 formulas-John Wiley & Sons.pdf (2016) (downloaded) Michael Alexander, Richard Kusleika-
https://drive.google.com/file/d/0B0DBjBjJsSoLck1pLUQ0QkwxWlk/view?usp=sharing
.
**e vba- 100 Excel VBA Simulations_- Using Excel VBA to Model Risk, Investments, Genetics. Growth, Gambling, and Monte Carlo Analysis ( PDFDrive.com ).**pdf (downloaded) Free
460 Pages·2016·by Dr. Gerard M. Verschuuren Covering a variety of Excel simulations by using Visual Basic (VBA), from gambling to genetics ...
.
E vba- Excel 2007 Macros Made Easy.pdf (2009) 202pg (downloaded) Gail_Perry_
https://drive.google.com/file/d/0B0DBjBjJsSoLMF95UFBjcklqc3c/view?usp=sharing
Ch 1 Recording Macros------------------------ 1
Ch 2 Editing Macros--------------------------- 19
Ch 3 Creating Macros in Visual Basic------ 35
Ch 4 Storing Macros--------------------------- 53
Ch 5 Understanding Macro Commands--- 73
Ch 6 Using Visual Basic Subroutines and Creating Functions------------------ 91
Ch 7 Creating Interactive Macros--------- 103
Ch 8 Using Macros to Format Cells------- 119
Ch 9 Using Variables in Macros----------- 133
Ch 10 Creating If/Then/Else Routines---- 147
Ch 11 Exploring Loops----------------------- 157
Ch 12 Adding Controls to Your Worksheets 171
.
e vba-Excel 2013 Power Programming with VBA ( PDFDrive.com ).pdf (downloaded) Free
https://www.pdfdrive.com/excel-2013-power-programming-with-vba-e49652299.html
1567 Pages · 2013 · by John Walkenbach
.
e vba- Excel 2016 Power Programming with VBA ( PDFDrive.com ).pdf (downloaded) Free
https://www.pdfdrive.com/excel-2016-power-programming-with-vba-d157084299.html
We both added a decent bit of new content to Power Programming.
977 Pages · 2016 · by Michael Alexander & Richard Kusleika
.
e vba- Excel 2016 VBA and Macros - pearsoncmg.com – 2016 (downloaded) Free
https://www.pdfdrive.com/excel-2016-vba-and-macros-pearsoncmgcom-e18120452.html
by Bill Jelen 596 Pages · 2016 ·by Bill Jelen & Tracy Syrstad
.
e vba- Excel Macros and VBA.pdf 42pg (2016) (downloaded) Agnihotri, Prateek-
https://drive.google.com/file/d/0B0DBjBjJsSoLNGRSQTFFZWx2c2M/view?usp=sharing
1.) Variables and Data Types.----------------- 2.) Conditional and Logical operators.
3.) Nested Loops, Switch Cases, conditional statements etc.
4.) Error Handling.-------------------------------- 5.) Object handling.
6.) single and multiple dimensional arrays in VBA.
7.) String manipulation.-------------------------- 8.) Macro Recording.
.
e vba- Excel VBA 24-Hour Trainer 2011( PDFDrive.com ).pdf(downloaded) Free
https://www.pdfdrive.com/excel-vba-24-hour-trainer-d157884378.html
523 Pages · 2011 · by Tom Urtis
.
E vba- Excel VBA 24-Hour Trainer 2nd Ed.pdf 603 pg Excel 2013 - (2015) (downloaded) Tom Urtis-
https://drive.google.com/file/d/0B0DBjBjJsSoLUGNkcnpkZkVzZm8/view?usp=sharing
This book contains 33 lessons, which are broken into five parts:
**Part I, Understanding the BASICs:**Lessons 1 to 4, introducing you to VBA by providing a historical background and a discussion of what VBA is and what it can do for you. This part familiarizes you with the Macro Recorder and the Visual Basic Editor, where VBA code is maintained.
Part II, Diving Deeper Into VBA:
Lessons 5 to 9, which discuss VBA topics including an overview of object-oriented programming, variable declaration, objects and collections, arrays, and options for decision-making.
Part III, Beyond the Macro Recorder: Writing Your Own Code
Lessons 10 to 20. You learn how to write your own macros without help from the Macro Recorder. You become familiar with loops, event programming at the workbook and worksheet levels, charts, PivotTables, user-defined functions, and embedded controls. You learn to program formulas and how to debug your VBA code.
Part IV, Advanced Programming Techniques:
Lessons 21 to 28, and deals with the more advanced topics of UserForms, class modules, add-ins, retrieving external data, and various examples of programming Excel to achieve solutions you might not have thought possible.
Part V, Interacting with Other Office Applications:
Lessons 29 to 33, dealing with how to control Word, Outlook, Access, and PowerPoint from Excel.
.
e vba- Excel VBA Notes for Professionals- MUO- w_goaa12 2019 04 15.pdf (downloaded)****http://GoalKicker.com/ExcelVBABook****This free ebook will help you to learn everything you need to know about Excel VBA and start customizing office solutions. With over 100 pages of knowledge, this resource covers the basics and dives into more advanced components. Take advantage of the examples, screen shots, and strategic advice inside to take your Excel skills to the next level!
Chapter 1: Getting started with excel-vba -------------------------------- 2 Section 1.1: Opening the Visual Basic Editor (VBE) 2Section 1.2: Declaring Variables ---------------------------- 4
Section 1.3: Adding a new Object Library Reference ------------------------ 5
Section 1.4: Hello World -------------------- -------------------------------- 9
Section 1.5: Getting Started with the Excel Object Model ---------------- 11
Chapter 2: Debugging and Troubleshooting ------------------------------ 15 Section 2.1: Immediate Window ----------- ------------------------------ 15
Section 2.2: Use Timer to Find Bottlenecks in Performance -------------- 16
Section 2.3: Debugger Locals Window --- ------------------------------ 16
Section 2.4: Debug.Print -------------------- ------------------------------ 17
Section 2.5: Stop 18
Section 2.6: Adding a Breakpoint to your code
18
Chapter 3: Methods for Finding the Last Used Row or Column in a Worksheet------------------------------------- 19 Section 3.1: Find the Last Non-Empty Cell in a Column ------------------- 19
Section 3.2: Find the Last Non-Empty Row in Worksheet ---------------- 19**** Section 3.3: Find the Last Non-Empty Column in Worksheet ------------ 20**** Section 3.4: Find the Last Non-Empty Cell in a Row ----------------------- 21**** Section 3.5: Get the row of the last cell in a range ------------------------- 21**** Section 3.6: Find Last Row Using Named Range --------------------------- 21**** Section 3.7: Last cell in Range.CurrentRegion ------------------------------ 22
Section 3.8: Find the Last Non-Empty Cell in Worksheet - Performance (Array) ------------------------------------------ 22
Chapter 4: User Defined Functions (UDFs) ------------------------------- 25 Section 4.1: Allow full column references without penalty ----------------- 25
Section 4.2: Count Unique values in Range ------------------------------ 26**** Section 4.3: UDF - Hello World ------------ ------------------------------ 26
Chapter 5: VBA Best Practices -------------- ------------------------------ 29 Section 5.1: ALWAYS Use "Option Explicit" ------------------------------ 29**** Section 5.2: Work with Arrays, Not With Ranges --------------------------- 31**** Section 5.3: Switch on properties during macro execution ---------------- 32**** Section 5.4: Use VB constants when available
33**** Section 5.5: Avoid using SELECT or ACTIVATE ---------------------------- 34**** Section 5.6: Always define and set references to all Workbooks and Sheets ------------------------------------------ 36
Section 5.7: Use descriptive variable naming ------------------------------ 36**** Section 5.8: Document Your Work ------- ------------------------------ 37**** Section 5.9: Error Handling ----------------- ------------------------------ 38
Section 5.10: Never Assume The Worksheet ------------------------------ 40
Section 5.11: Avoid re-purposing the names of Properties or Methods as your variables --------------------------------- 40
Section 5.12: Avoid using ActiveCell or ActiveSheet in Excel ------------- 41
Section 5.13: WorksheetFunction object executes faster than a UDF equivalent -------------------------------------- 41
Chapter 6: Loop through all Sheets in Active Workbook------------------ 44 Section 6.1: Retrieve all Worksheets Names in Active Workbook ------- 44
Section 6.2: Loop Through all Sheets in all Files in a Folder -------------- 44
Chapter 7: Ranges and Cells ----------------- ------------------------------ 46 Section 7.1: Ways to refer to a single cell 46 Section 7.2: Creating a Range ---------------------------------------- 46
Section 7.3: Oset Property --------------- ------------------------------ 48**** Section 7.4: Saving a reference to a cell in a variable --------------------- 48**** Section 7.5: How to Transpose Ranges (Horizontal to Vertical & vice versa) ------------------------------------------- 48
Chapter 8: Common Mistakes --------------- ------------------------------ 50 Section 8.1: Qualifying References --- 50 Section 8.2: Deleting rows or columns in a loop -------------------------- 51
Section 8.3: ActiveWorkbook vs. ThisWorkbook ---------------------------- 51
Section 8.4: Single Document Interface Versus Multiple Document Interfaces -------------------------------------- 52
Chapter 9: Arrays ------------------------------- ------------------------------ 54 9.1: Dynamic Arrays (Array Resizing and Dynamic Handling) ------------ 54
9.2: Populating arrays (adding values) 54 9.3: Jagged Arrays (Arrays of Arrays) ----------------------------- 55**** 9.4: Check if Array is Initialized (If it contains elements or not) ---------- 55**** 9.5: Dynamic Arrays [Array Declaration, Resizing] ------------------------- 55
Chapter 10: Excel VBA Tips and Tricks --- ------------------------------ 57 10.1: Using xlVeryHidden Sheets --------- ------------------------------ 57
10.2: Using Strings with Delimiters in Place of Dynamic Arrays ---------- 58
10.3: Worksheet .Name, .Index or .CodeName ----------------------------- 58**** 10.4: Double Click Event for Excel Shapes 60 10.5: Open File Dialog - Multiple Files ------------------------------------ 61
Chapter 11: PowerPoint Integration Through VBA-------------------------- 62 11.1: The Basics: Launching PowerPoint from VBA ------------------------ 62
Chapter 12: Workbooks ----------------------- ------------------------------ 63 12.1: When To Use ActiveWorkbook and ThisWorkbook ----------------- 63**** 12.2: Changing The Default Number of Worksheets In A New Workbook
- 63**** 12.3: Application Workbooks --------------- ------------------------------ 63**** 12.4: Opening A (New) Workbook, Even If It's Already Open ------------ 64
12.5: Saving A Workbook Without Asking The User ----------------------- 65
Chapter 13: Pivot Tables ---------------------- ------------------------------ 66 13.1: Adding Fields to a Pivot Table -- 66 13.2: Creating a Pivot Table -------------------------------------------- 66**** 13.3: Pivot Table Ranges --------------- 69 13.4: Formatting the Pivot Table Data ------------------------------ 69****
Chapter 14: Binding ---------------------------- ------------------------------ 70 14.1: Early Binding vs Late Binding ------- 70
Chapter 15: Charts and Charting ----------- ------------------------------ 72 15.1: Creating a Chart with Ranges and a Fixed Name ------------------- 72**** 15.2: Creating an empty Chart ------------ ------------------------------ 73**** 15.3: Create a Chart by Modifying the SERIES formula ------------------- 74
15.4: Arranging Charts into a Grid -------- 76****
Chapter 16: Application object -------------- ------------------------------ 80 16.1: Simple Application Object example: Display Excel and VBE Version 80
16.2: Simple Application Object example: Minimize the Excel window
- 80****
Chapter 17: Merged Cells / Ranges -------- ------------------------------ 81 17.1: Think twice before using Merged Cells/Ranges ---------------------- 81****
Chapter 18: VBA Security --------------------- ------------------------------ 82 18.1: Password Protect your VBA -------- ------------------------------ 82
Chapter 19: How to record a Macro -------- ------------------------------ 83 19.1: How to record a Macro -------------- ------------------------------ 83
Chapter 20: Locating duplicate values in a range--------------------------- 85 20.1: Find duplicates in a range ----------- ------------------------------ 85
Chapter 21: Named Ranges ------------------ ------------------------------ 87 21.1: Define A Named Range ------------- ------------------------------ 87**** 21.2: Using Named Ranges in VBA ------ ------------------------------ 87**** 21.3: Manage Named Range(s) using Name Manager -------------------- 88
21.4: Named Range Arrays ---------------- ------------------------------ 90
Chapter 22: autofilter ; Uses and best practices-------------------------------- -----------------------------------------------------91 22.1: Smartfilter! ----------------------------- ------------------------------ 91
Chapter 23: Creating a drop-down menu in the Active Worksheet with a Combo Box----------------------------------- 95 23.1: Example 2: Options Not Included 95 23.2: Jimi Hendrix Menu -------------------------------------------- 96
Chapter 24: Conditional statements ------- ------------------------------ 98 24.1: The If statement ---------------------- ------------------------------ 98****
Chapter 25: Working with Excel Tables in VBA---------------------------- 100 25.1: Instantiating a ListObject ------------ ---------------------------- 100
25.2: Working with ListRows / ListColumns ---------------------------- 100
25.3: Converting an Excel Table to a normal range ---------------------- 100
Chapter 26: Excel-VBA Optimization ------- ---------------------------- 101 26.1: Optimizing Error Search by Extended Debugging ----------------- 101
26.2: Disabling Worksheet Updating 102 26.3: Row Deletion - Performance --------------------------------- 102**** 26.4: Disabling All Excel Functionality Before executing large macros 103**** 26.5: Checking time of execution ---- 104 26.6: Using With blocks -------------------------------------------------- 105
Chapter 27: Conditional formatting using VBA 107 27.1: FormatConditions.Add ---------- 107 27.2: Remove conditional format --------------------------- 108**** 27.3: FormatConditions.AddUniqueValues 108----------------------- 27.4: FormatConditions.AddTop10 ------------- 109**** 27.5: FormatConditions.AddAboveAverage 109 27.6: FormatConditions.AddIconSetCondition------ 109
Chapter 28: File System Object ------------- ---------------------------- 112 28.1: File, folder, drive exists --------- 112 28.2: Basic file operations ------------------------------------ 112
28.3: Basic folder operations --------- 113 28.4: Other operations 113
Chapter 29: SQL in Excel VBA - Best Practices---------------------------------- -------------------------------------------------- 115 29.1: How to use ADODB.Connection in VBA? ---------------------------- 115
Chapter 30: Use Worksheet object and not Sheet object---------------- 117 30.1: Print the name of the first object -- ---------------------------- 117
Chapter 31: CustomDocumentProperties in practice--------------------- 118 31.1: Organizing new invoice numbers -- ---------------------------- 118
.
**e vba- Excel VBA Programming For Dummies, 3rd Edition 2013.**pdf (downloaded) Free
https://www.pdfdrive.com/excel-vba-programming-for-dummies-3rd-edition-e29072717.html
411 Pages · 2013 · by Walkenbach, John
.
e vba- Mastering VBA for Microsoft Office 2016 ( PDFDrive.com ).pdf (downloaded) Free
https://www.pdfdrive.com/mastering-vba-for-microsoft-office-2016-e167570957.html
987 Pages · 2016 · by Richard Mansfield
.
e vba- Programming Excel 2016 with VBA A Practical Real-World Guide-Apress (2016).pdf 802pg (downloaded) Flavio Morgado-
https://drive.google.com/file/d/0B0DBjBjJsSoLQUlUVm5NTkZqUEE/view?usp=sharing
Ch 1: Understanding Visual Basic for Applications (VBA)------------------------- 1
Ch 2: Programming the Microsoft Excel Application Object-------------------- 81
Ch 3: Programming the Microsoft Excel Workbook Object-------------------- 149
Ch 4: Programming the Microsoft Excel Worksheet Object------------------- 185
Ch 5: Programming the Microsoft Excel Range Object------------------------ 239
Ch 6: Special Range Object Properties and Methods-------------------------- 333
Ch 7: Using Excel as a Database Repository ----------------------------- 427
Ch 8: Creating and Setting a Worksheet Database Class--------------------- 495
Ch 9: Exchanging Data Between Excel Applications---------------------------- 603
Ch 10: Using the Windows API----------------- ----------------------------- 711
Ch 11: Producing a Personal Ribbon Using RibbonEditor.xlam-------------- 761
.
o learn- Office 2019 All-in-One For Dummies ( PDFDrive.com ).pdf (downloaded) Free
https://www.pdfdrive.com/office-2019-all-in-one-for-dummies-e185750286.html
819 Pages · 2018 by Peter Weverka
.
o learn- Office 2019 for Dummies ( PDFDrive.com ).pdf (downloaded) Free****https://www.pdfdrive.com/office-2019-for-dummies-e187480840.html
435 Pages · 2018 · by Wallace Wang
.
*********** Reference material, not "learning" material
Excel Programming Conceptshttps://msdn.microsoft.com/en-us/library/bb687921.aspx
#3 What is ...
what is meant by a "code"
"code" is just the program language used to make VBA do what ever it is you want.
what is meant "script"
A script is a collection of code that does a specific task.
what this sub end sub etc mean.
In the VBA editor you can have many macros / scripts.
Sub includes the name of the specific script / macro. You use this name to invoke / run the macro from your spreadsheet.
Sub identifies the start of a script
the code after the sub defines what it is you want to do
End Sub is the way to tell VBA that a script is finished.
why they put "." between the words etc.
The period between words is a way of subdividing a command. You work from high level to lower level becoming more specific, ie
ActiveWindow.Selection.Address
Breaks down as:
When the macro is running, work in the currently "Active" Excel Window / spreadsheet
Act on whatever is currently selected
Pick up the address / range of the selection (used to pass on to other commands).
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(70.4, 15%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ET5%3C/text%3E%3C/svg%3E)
