- 63
- 134 547
Skills and Automation
New Zealand
เข้าร่วมเมื่อ 4 ก.พ. 2021
Exploring automation possibilities beyond Excel.
"Hello and welcome to Skills and Automation. My name is Ash and today, we are going to talk about ..." this TH-cam channel!
I started out in core Finance and over the last decade, I've have slowly transitioned into IT, operating in the space of Project Management, Business Intelligence and Process Improvement. Moving into IT from a non-IT background was hard. But, through a persistent pursuit of online research and courses (a lot of them!), I was able to push forward.
The purpose of this channel is to help others learn better and faster. I hope to capitalize on my learned experience to post videos that are applicable in the real world.
If this sounds interesting, please do consider subscribing and join me as we explore this domain.
Code and related content will be posted to my blog site: skillsandautomation.com/
"Hello and welcome to Skills and Automation. My name is Ash and today, we are going to talk about ..." this TH-cam channel!
I started out in core Finance and over the last decade, I've have slowly transitioned into IT, operating in the space of Project Management, Business Intelligence and Process Improvement. Moving into IT from a non-IT background was hard. But, through a persistent pursuit of online research and courses (a lot of them!), I was able to push forward.
The purpose of this channel is to help others learn better and faster. I hope to capitalize on my learned experience to post videos that are applicable in the real world.
If this sounds interesting, please do consider subscribing and join me as we explore this domain.
Code and related content will be posted to my blog site: skillsandautomation.com/
Working with Dates || Excel VBA Master Class || 3.4e
Dates are tricky to deal with in VBA. In this video, we will deep dive into the date data type and look at ways to manipulate dates accurately using various functions.
In this lesson, we will cover a lot of examples that we will help us:
- Find the current date and time using Date(), Now(), Time()
- Extract date component from a datetime value using DateValue()
- Construct a date using time intervals such as year, month and day using DateSerial()
- Extract parts of a date such as Year, Quarter etc. using DatePart()
- Add and subtract dates using DateAdd()
- Find the difference between 2 dates using DateDiff()
- Find the Weekday value of a day using Weekday() and WeekdayName()
etc.
#vba #excel
Follow the rest of the series, in the below playlist:
th-cam.com/play/PLgG3u13JJuEbguNAl0zbkq3mDCvy07v0Y.html
Code snippets can be found here:
skillsandautomation.com/master-class/working-with-dates-excel-vba-master-class/
00:00 Intro
00:57 Practice the Basics
06:00 DateSerial
09:33 DatePart
10:13 DateAdd and DateDiff
11:50 Weekday
13:52 Format
16:52 IsDate
In this lesson, we will cover a lot of examples that we will help us:
- Find the current date and time using Date(), Now(), Time()
- Extract date component from a datetime value using DateValue()
- Construct a date using time intervals such as year, month and day using DateSerial()
- Extract parts of a date such as Year, Quarter etc. using DatePart()
- Add and subtract dates using DateAdd()
- Find the difference between 2 dates using DateDiff()
- Find the Weekday value of a day using Weekday() and WeekdayName()
etc.
#vba #excel
Follow the rest of the series, in the below playlist:
th-cam.com/play/PLgG3u13JJuEbguNAl0zbkq3mDCvy07v0Y.html
Code snippets can be found here:
skillsandautomation.com/master-class/working-with-dates-excel-vba-master-class/
00:00 Intro
00:57 Practice the Basics
06:00 DateSerial
09:33 DatePart
10:13 DateAdd and DateDiff
11:50 Weekday
13:52 Format
16:52 IsDate
มุมมอง: 157
วีดีโอ
Working with Strings || Excel VBA Master Class || 3.4d
มุมมอง 225วันที่ผ่านมา
Strings are fun to manipulate and VBA provides us with many tools such as Functions and Operators to slice and dice and shape text data any way we want. In this long lesson, we will cover a lot of examples that we will help us: - Insert Characters using Chr() - Determine Length of a string using Len() - Check whether a string is blank or not - Join strings using & and operators - Extract parts ...
Working with Booleans || Excel VBA Master Class || 3.4c
มุมมอง 103วันที่ผ่านมา
We encounter Boolean values throughout VBA, either as output of evaluation of expressions or return values of functions or values that we assign to variables. In this informative lesson, we will look at how to interact with the Boolean values True and False. Then, we will walk through the logic of a simple data entry form and check out how Boolean logic can be used to perform validation checks ...
Working with Numbers || Excel VBA Master Class || 3.4b
มุมมอง 118วันที่ผ่านมา
Working with Numerical Types can be daunting since there our a lot of options to choose from. We will discuss which option to use when and how to make that decision easy. We will also, look at how to validate whether a value is numerical or not, and how to perform operations such as Addition in VBA. In this short lesson, we will look at the functions to evaluate numerical values such as isNumer...
Intro to Data Types || Excel VBA Master Class || 3.4a
มุมมอง 13114 วันที่ผ่านมา
Data Types are a fundamental concept of programming. Understanding data types is important to maintain the integrity of data that we intend to manipulate via our VBA code. In this introductory lesson, - we will start with an overview of the Data Types that are available for us to use within VBA. - Then, we will disambiguate the terms Variable, Data Types, Values and Literals. - We will learn ho...
Early and Late Binding (Object Variables) || Excel VBA Master Class || 3.3b
มุมมอง 203หลายเดือนก่อน
The concept of binding objects to variables is important to know especially, when accessing external applications such as PowerPoint and Outlook. Early Binding is when we declare objects using a specific Object type. Late Binding is when we declare objects using a generic Object type. In this lesson, we will learn: - How to differentiate Early Binding from Late Binding - How to access external ...
Object Variables || Excel VBA Master Class || 3.3
มุมมอง 247หลายเดือนก่อน
Use Object Variables instead of repeating the same object reference over and over again in our code. Object Variables make our objects portable and make our code more manageable. We can use Object Variables to: - Refer to Existing Objects - Refer to New Worksheets - Refer to New or Existing Workbooks - Refer to Newly Created Objects Always use separate Dim and Set Keywords to create object vari...
Constants & Enumerations || Excel VBA Master Class || 3.2
มุมมอง 486หลายเดือนก่อน
Use Constants in place of raw, fixed values within the VBA code. Creating and using a Constant roughly follows the same pattern as a Variable, with a few key differences. We will learn more within this video. There are system defined constants and user defined constants. We will start with browsing system defined or intrinsic constants within the Object Browser. Then, we will check out how to c...
Variables || Excel VBA Master Class || 3.1
มุมมอง 232หลายเดือนก่อน
Start learning the components of the VBA programming language with an introduction to variables. Variables make our code more readable and at times, more efficient. We will start with a practical introduction to variables. We will discuss a scenario involving data variables, constants and object variables. Rest of the lesson will focus on data variables. Then, we will learn how to create variab...
Object Browser & Library || Excel VBA Master Class || 2.4
มุมมอง 3304 หลายเดือนก่อน
Learn the architecture behind the Excel Object Model. The Object Library enables access to few libraries by default, including the Excel Object Library. And this library allows us to interact with the components in Excel. We can even, interact with other Applications such as Outlook. We will learn to send an Outlook Email via Excel VBA. And, let's check out how to navigate the Excel Object Libr...
Object Properties and Methods || Excel VBA Master Class || 2.3
มุมมอง 3064 หลายเดือนก่อน
Once we are able to Identify (or Reference) an Object in VBA, we are free to manipulate it by changing its characteristics (properties) or performing some action with it (methods). We will explore both these aspects in this video. We will also, cover how to manipulate multiple properties and/or methods for the same object using With Statements. #vba #excel Follow the rest of the series, in the ...
Working with the Object Hierarchy || Excel VBA Master Class || 2.2
มุมมอง 4204 หลายเดือนก่อน
Deep-dive into using the Excel Object Model to locate which Object we want to manipulate. In this video, we will start by learning the formal way to reference any Object (using the Fully Qualified Reference). And then, we will look at shortcuts to reference the same object. These shortcuts include using the Global Object members such as: - ThisWorkbook - Active Objects - Using Workbooks, Worksh...
Intro to Excel Objects (Theory Only) || Excel VBA Master Class || 2.1
มุมมอง 2254 หลายเดือนก่อน
The goal for automation through Excel VBA is to manipulate the various components of Excel. These components are called Objects, which are stacked within the Excel Object Model. In this video, we will learn what Objects are and how to use them. Topics covered in this lesson are: - Objects in Real Life Context - Objects in Excel VBA Context And we will cover the following key points: - Excel Obj...
Saving Macros Files, Trust Center Settings, Locking Projects || Excel VBA Master Class || 1.6
มุมมอง 2144 หลายเดือนก่อน
What is the extension for saving a macro file? What are the settings for disabling macros when opening a Excel Macro file? Can we lock the macro code so that no one else can access it? We will answer all these questions in this video. Topics covered in this lesson are: Saving: - Using .xlsm and .xlsb file extensions. - Using Personal Macro Workbooks - Brief use case for Add Ins. Security: - Mac...
Assign Macro to a Button || Excel VBA Master Class || 1.5
มุมมอง 1904 หลายเดือนก่อน
Let's check out ways that we can make our macros accessible to the end user. We shouldn't expect the user to go into the VBA backend or even, into the Excel Run Macro menu-option to use our macros. We can make our macros available by: - Adding a Macro to a Form Button - Adding a Macro to the Quick Access Toolbar - Adding a Macro to a Picture, Shape or Icon #vba #excel Follow the rest of the ser...
Developer Tab (Overview) || Excel VBA Master Class || 1.4
มุมมอง 1104 หลายเดือนก่อน
Developer Tab (Overview) || Excel VBA Master Class || 1.4
Customize the VB Editor || Excel VBA Master Class || 1.3.3
มุมมอง 2104 หลายเดือนก่อน
Customize the VB Editor || Excel VBA Master Class || 1.3.3
Learn to Write VBA Code || Excel VBA Master Class || 1.3.2
มุมมอง 2974 หลายเดือนก่อน
Learn to Write VBA Code || Excel VBA Master Class || 1.3.2
Navigate the VB Editor || Excel VBA Master Class || 1.3.1
มุมมอง 1884 หลายเดือนก่อน
Navigate the VB Editor || Excel VBA Master Class || 1.3.1
Record Macros || Excel VBA Master Class || 1.2
มุมมอง 2545 หลายเดือนก่อน
Record Macros || Excel VBA Master Class || 1.2
Build your first macro || Excel VBA Master Class || 1.1
มุมมอง 3995 หลายเดือนก่อน
Build your first macro || Excel VBA Master Class || 1.1
Excel VBA || Calling Subs and Functions || Parent - Child Architecture || Make your code smarter
มุมมอง 4396 หลายเดือนก่อน
Excel VBA || Calling Subs and Functions || Parent - Child Architecture || Make your code smarter
Excel VBA | Create a PowerPoint Slide Deck With Dynamic Commentary | USEFUL for Corporate Reporting
มุมมอง 2.1K6 หลายเดือนก่อน
Excel VBA | Create a PowerPoint Slide Deck With Dynamic Commentary | USEFUL for Corporate Reporting
VBA Automation Project || Build a Worksheet to Control the Flow of a Macro || Lots of Tips & Tricks
มุมมอง 1.2K7 หลายเดือนก่อน
VBA Automation Project || Build a Worksheet to Control the Flow of a Macro || Lots of Tips & Tricks
Excel VBA Automation Project | Send Reports and Reconcile Responses | 2-Step Workflow
มุมมอง 7468 หลายเดือนก่อน
Excel VBA Automation Project | Send Reports and Reconcile Responses | 2-Step Workflow
Excel VBA | Lock & Unlock Excel Files, Sheets & Specific Areas within a Sheet | Basic Functionality
มุมมอง 3118 หลายเดือนก่อน
Excel VBA | Lock & Unlock Excel Files, Sheets & Specific Areas within a Sheet | Basic Functionality
A Practical Introduction to Using FIND Function in Excel VBA || Perform any kind of Lookups
มุมมอง 7328 หลายเดือนก่อน
A Practical Introduction to Using FIND Function in Excel VBA || Perform any kind of Lookups
Excel VBA || Transfer Dictionaries and Arrays through Functions || Make your code more manageable!
มุมมอง 9499 หลายเดือนก่อน
Excel VBA || Transfer Dictionaries and Arrays through Functions || Make your code more manageable!
Excel VBA || Select Data Range Except for Top Row || Range - CurrentRegion, Offset and Resize
มุมมอง 7639 หลายเดือนก่อน
Excel VBA || Select Data Range Except for Top Row || Range - CurrentRegion, Offset and Resize
Edit: At 1:57, I mention String is a Primitive Data Type. This is not entirely correct in VBA. A String is a LIST of individual characters which makes it a Composite Data Type. But, then again, we can't declare a Variable as a Character in VBA. So, the exact classification of a String in VBA is a bit murky. In other languages, String is clearly NOT a primitive data type. I talk more this in the lesson on Strings.
The best videos always ❤
Excellent ❤, keep up this good work
every one like video
thank you for continuing series
Please do a tutorial on how to automate a cashflow statement monthly using the income statement and balance sheet.
Hi, Thanks for the suggestion. I am currently, focused on completing chapter 3 in the VBA master class series. Once that's done, I will pick up your suggestion and do some accounting related examples. Cheers
My favourite channel
Yet another great tutorial...I'd be a happy student if you were my tutor. Look forward to the next one😂
thank you! very useful
Great compositional breakdown to the nucleus of objects.
I wish people would stop pronouncing "Parameters" as para-meters. Its pronounced "param-meters". A double m is silent....grrrrr
thanks for the feedback. i will make a note to pronounce it correctly.
Excellent video. Thanks for sharing and keep up this excellent work❤👍
Please do mapping for a cash flow statement example using trail balances
Hi, Pl guide. In the 2nd line in 'Else' section of 'If' statement for creating Storename Array, Redim is used without 'Preserve'. However, the array doesn't loose values assigned thru 'If' condition in previous iteration. Kindly guide how. Pl. advise if I am missing something to understand.
Hi, I will suggest that you refer to the screenshot at 04:35, steps 1,2 & 3. When we use Redim without Preserve after the Else statement, we are creating a brand new array. We are re-using the same array name, but its got nothing to do with the array from the previous iteration. After the Redim statement, we assign the dictionary item to this new array that we have created. At this point, it becomes the same array as the previous iteration for that region key. And now, we are ready to add a new Store to it and assign it back to the dictionary item. Hope that helps.
Ok..... Now I get it..... Thanks a lot for the guidance. The contents of your videos are definitely useful and helpful. Best Regards...
thank u bro, nice tutorial
good explanation of the project in perfect way able to understand the vba coding thank u bro
Nice tutorial
thanks for video and your work and please keep posting video
My Code is not implementing in project.
Excellent, can share source code?
Hi, Links to the source code can now be found in the video description. Cheers
Excellent video. Thanks for sharing
👍❤
Excellent. 👍Thanks
Another good one...some good teaching skills demonstrated here
Thank you for this playlist
Another excellent tut
thanks sir ji for video, please keep continue VBA master class
Hi there, If you possible , could You explain really time database related project using macro code Because I asked lots of questions of people no has not been answered
Please post weekly. I love this channel, extremely informative examples
ur a great teacher....iam lucky that finnaly i found this playlist......before i directly went to the projects playlist ..now i am here for basics then i will go there: note : i read a saw 10 basics videos already from some other channel in hindi ... now this is my second prepration
these projects are pro level projects
Please do more examples
this is good stuff.
Sir i need your help for create a cutting optimization program.
Thanks for the Video. It was very helpful. Just as an fyi, you forgot a line in the first script. oAttach.SaveAsFile sAttachName
This is like art, nice
Superb work that is of great help. Just having a run time 13 error when some of the item IDs are numbers and some are alphanumeric e.g. L7878. Any idea where I change the data type?
Brilliant, thank you!!
this is the best video i have ever found .....the only thing i regeret iti found this video in 2024 means after 3 long years..........thank you for making such valuabe video ...thankyou
Please post more accounting content
I found this case study extremely useful, very comprehensive and complex. Please post more case studies accounting and finance related
Superbly talented sir, simple with zero level 100.
hello , I tried to add the wsOut.Cells.Clear into my code but it says"The Varible is not defined" what might be the poblem?
Hi, This code uses the Worksheet Code Name e.g. wsOut. If your code names don't match as per the video, the code will not run for you. I briefly mention the code name setup at the 04:29 mark. Cheers
thankyou sir ji for your effort teaching
thank you sir from Zimbabwe
Learning Vba is fun
great job..thanks for video
how could i configure this to work when the Outlook Extractor folder is located in one drive which i can access from my laptop?
I am not sure about OneDrive. OneDrive Personal on desktop should be fine. You can construct the Folder Path like this "C:\Users\<YourUsername>\OneDrive\<FolderPath>\", which works for me. Source -techcommunity.microsoft.com/t5/excel/excel-vba-to-refer-to-files-stored-on-onedrive-instead-of-local/m-p/3894487 But, if you don't have the desktop version, I think it becomes the same as SharePoint and iterating over folders may not be possible. Haven't worked with it to be sure.
Thank you from Zimbabwe
Awesome tutorial, concise and crystal clear walking us through the VBE settings right before diving into the coding. This is absolutely vital for the level where I am standing currently now and just relying mostly on youtube resources to crack the vba. Thank you Sir for your outstanding committment to share such valuable lessons.