5 ESSENTIAL Excel VBA Beginner Techniques
HTML-код
- Опубликовано: 22 июл 2024
- If you’re interested in learning Excel VBA, you might be happy to know the basics of the language are relatively straightforward. At Tiger, we talk about five building blocks for automation with Excel VBA. In today’s session, we’ll demonstrate each of these building blocks through a simple automation exercise based on my experience of what professionals are trying to do in the real world. Welcome back to 30 Day Excel Analyst!
💻DOWNLOAD THE RESOURCES
tinyurl.com/2tb2sbst
🔥30 DAY EXCEL ANALYST PLAYLIST
tinyurl.com/4439xtnb
🔥FIRST VIDEO IN THE COURSE
ruclips.net/user/liveCF-Y46c829Q
So, what are the five building blocks of Excel VBA? The first is interaction with cells in the spreadsheet. We need to be able to write to the spreadsheet, and read information from it. You might be familiar with syntax such as Range(“A1”) = “Tiger”. This is an example of interacting with cells in Excel with VBA. I also recommend the ‘dot cells’ method: Cells(1,1) = “Tiger”. This is our first building block.
The second is using variables. A variable is an abstract idea and, like many things in Excel and computer programming more generally, is not helpfully named! I like to think of a variable as a place to store information. To that extent, it can be compared to a cell in the spreadsheet - which also stores information. It’s just that variables only exist in Excel’s memory and are less easily to visualise.
The third is to be able to control position. This involves techniques such as the Offset method in Excel VBA (a real Tiger favourite!) and even ‘dot cells’. Position control refers to how well we can navigate the grids, worksheets and files that Excel gives us. Dynamic position control is the next level and concerns working with datasets dynamically - since, as we know, data is constantly changing.
The fourth is loops. Loops allow us to repeat an instruction any number of times and, as such, are the key to powerful automation in Excel. Want to do something to each sheet in a file, each cell in a range, or each chart on a sheet? You’ll need some kind of loop. The For-Each loop that does something to each ‘object’ in a ‘collection’ is the best starting point.
The fifth is conditionality. A conditional statement in Excel VBA sends the code one of two or more ways depending on a ‘condition’ - whether something is happening in the spreadsheet or not. Syntax ranges from a simple ‘one-line’ If statement to more complex logic using If - Then - Else and Select Case. Conditionality adds another layer of sophistication and power to your VBA coding.
In today’s session, we’ll at least touch on all five of the building blocks. Remember, computer coding is a skill and you have to practise to improve … and reach those sweet ‘punch-the-air’ moments. So, make sure you download the Excel files and work along with me. Then, let me know how you got on in the comments - I would love to hear what you think about the 30 Day Excel Analyst programme.
FOLLOW-UP RESOURCES
🔥30 DAY EXCEL ANALYST PLAYLIST
tinyurl.com/4439xtnb
🔥EXCEL VBA BEGINNER BUILDING BLOCKS
• How To Write Your Firs...
🔥OUR MOST POPULAR VIDEO ON EXCEL VBA (1 MILLION VIEWS!)
• Excel Visual Basic (VB...
🔥30 FOR 30: LEARN EXCEL VBA 30 MINUTES A DAY
ruclips.net/user/livef42OniDWaIo?...
🔥17 THINGS BEGINNERS MUST KNOW ABOUT THE EXCEL VBA EDITOR
• 17 Things Beginners Mu...
🔥LEARN EXCEL VBA WITH THIS 5-HOUR WALKTHROUGH PROJECT
• Learn Excel VBA From B...
🔥9 EXCEL VBA BEGINNER MISTAKES
• 9 Excel VBA Beginner M...
🔥READ MORE ABOUT OUR PROFESSIONAL EXCEL DEVELOPMENT COMMUNITY: MEMBERS MONDAY
tinyurl.com/2p8wy5dr 
Развлечения
🔥30 DAY EXCEL ANALYST PLAYLIST
tinyurl.com/4439xtnb
Thanks Chris. If possible can you please explain how to define dynamic arrays on a sheet?
I am trying to define multiple arrays and then use an if type of condition to decide whether to keep or delete the array(s)... good times!
Good stuff, Bill! We won't have time to go deep into dynamic definitions in this series, but we might touch on it.
Check out this video for more on position control:
ruclips.net/video/q2ReyVkreUY/видео.htmlsi=rHzVJzFJltRHVFH1
Under Sub Variable_Test() we have decleared Name_Store As String, being whole number, right? So why Name_Store = Range(“A1”) containing a text, is returning the same text (not number)?
Hi Maz - a string variable holds text, and integer variable holds numbers, specifically whole numbers
@@TigerSpreadsheetSolutions Noted. Thanks!
Hopefully see you tomorrow Maz!
@@TigerSpreadsheetSolutions Exactly tomorrow having internal meeting at the same time… so will watch on reply. Chears