Excel Macros & VBA: 3 Learn simple Excel VBA using the Macro Recorder
Вставка
- Опубліковано 11 лип 2024
- // FREE Excel E-book "Record Your First Macro"
→ www.launchexcel.com/record-yo...
// Recommended Excel Courses //
1. Launch Excel Macros & VBA School: go.launchexcel.com/macros-vba...
2. Excel Campus Elevate Program: go.launchexcel.com/excel-camp...
3. My Online Training Hub Dashboards: go.launchexcel.com/moth-dashb...
4. Coursera Excel Specialisation: go.launchexcel.com/coursera-e...
5. Coursera Everyday Excel: go.launchexcel.com/coursera-e...
6. Coursera Excel Data Visualisation: go.launchexcel.com/coursera-d...
// Recommended Excel Templates //
If you are looking for pre-built, easy to use spreadsheets, check out Simple Sheets.
go.launchexcel.com/simple-she...
They have Excel templates covering cash flow planning, invoices, expense reports, personal budgets, and more!
--
// EPISODE NOTES
In episode 3 I will:
▸ Reveal that the Macro Recorder tool can be used to learn simple Excel VBA
▸ Demonstrate the types of Excel statements the Macro Recorder can write
▸ Explain what the Excel Macro Recorder cannot do for you
▸ You will learn how easy it is to start writing Macros to automate Microsoft Excel.
--
// LEARN MACROS WITH LAUNCH EXCEL
Most Excel users never start using Macros because they either haven't heard of them, or feel overwhelmed because they think it's all too hard. This is a shame because Macros and VBA can give you Excel superpowers.
You can save vast amounts of time by automating repetitive tasks and even create your own new functionality to do things that Excel does not do by itself.
But... it sometimes looks like Excel Macros are so technical. Don't worry, in this video I show you that the Macro Recorder tool can be a good friend for learning the Macro language of VBA (Visual Basic for Applications)
--
// VISIT MY WEBSITE
For more awesome tutorials on Microsoft Excel visit my website → www.launchexcel.com
- - - - - - - - - -
Extra Notes
- - - - - - - - - -
1. You can access the VBA Editor using the shortcut key combination ALT + F11
2. To step line-by-line through your macros in the VBA Editor use the keyboard shortcut F8
3. Here is the sample code for two worksheet event handlers. It's here for entertainment purposes only. Please use cautiously, and remember to keep backups of your documents before you run macros on them.
You need to copy and paste these into worksheet modules, not code modules.
When you want to stop them, just delete the code from the module. Or comment it out using the apostrophe (‘) at the start of a line. Excel will skip over comment lines that start with an apostrophe (‘)
------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count ＞ 1 Then Exit Sub
Dim i As Integer
i = Target.Interior.ColorIndex
Select Case i
Case -4142
i = 1
Case 0 To 55
i = i + 1
Case Is ＞ 55
i = 0
End Select
Target.Interior.ColorIndex = i
End Sub
---------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 1 'black
End Sub
Thanks for watching this video about using the Excel Macro Recorder to learn simple VBA. You can get your free e-book and email course at the following link:
DOWNLOAD EBOOK → launchexcel.lpages.co/first-macro-ebook
Please do leave comments below with any questions you have about learning Macros and VBA for Excel. And click on the LIKE button if you found this video helpful. Thanks for supporting my channel!
Very nice series, thanks kindly bud
Hello victor one question has been in my mind for several years that Macros are a sort of one step ahead of Conditional formatting. Thanks for all the videos
Awesome video. i love your channel.It would be awesome if you upload videos about entrepreneurship using excel. Just because its a hot topic and i think you get a lot of growth on this channel. Maybe by starter how to create a good to do list about project or how to analyze market.
Thanks Alyx. That's a cool idea. Do you have an interest in entrepreneurship?
Yeah. Excel is vital in every business. You have a great voice, calming and the worksheets are clean and perfect. Look for good keyword search and mix things up with it. I'm sure you can get into 100k soon. And teach udemy course too. You have natural calming voice which works like magic on people.
Hello Victor! Ive watched all your videos so far and you're really good at teaching this stuff. For work though I have to graph and I did some work on my own and figured out that I can make a graphing macro but it does not change the range based on how many numbers I have to graph. Is there anyway to make it so that the range is flexible and graphs all the cells that have numbers instead of basing the graphing on the range originally set in the macro? I hope this makes sense, if not let me know and i can try to simplify it. Thank you again for the awesome videos!
Hi Fuzzysuperpi. Thanks for your feedback on my videos! I recommend you check out the .CurrentRegion property, which generally selects a rectangular block surrounded by one or more blank rows or columns. Here's one reference: www.informit.com/articles/article.aspx?p=2021718&seqNum=14
Victor Chan thank you I will definitely check that out
Hello
I want to use VBA code to delete raw with the cell value multiple time and a different value in the cell please help to find the solution.
Well , i didn't quite understand he event part.Could you help me rgarding that?
Sure... let me explain.
From 12:58 onwards I talk about Excel worksheet change events.
These are subroutines that run automatically when something happens on an Excel worksheet.
For example, if you select a new cell then the subroutine "Worksheet_SelectionChange" is automatically run.
And if you change the value of a cell then the subroutine "Worksheet_Change" is automatically run.
There are two subroutines in Sheet3 - the one I explain in the video is Worksheet_SelectionChange. These subroutines don't go in normal VBA code modules - they must be stored with the worksheet (in this case Sheet3).
Let's say we start on Sheet3 in cell A1. When we change the selection to B2, the subroutine "Worksheet_SelectionChange" is automatically fired up. All the code in that subroutine runs, and it changes the background color of the cell B2.
And if we select another cell, say C1, the subroutine "Worksheet_SelectionChange" runs again. In this case it changes the background color of the cell C1.
Hope that helps!
- Victor