Excel VBA FUNCTIONS & ARRAYS with Projects - Series3

person icon Ajay Parmar

Excel VBA FUNCTIONS & ARRAYS with Projects - Series3

VBA UDF and INBUILT Functions with Powerful Arrays

updated on icon Updated on Sep, 2023

language icon Language - English

person icon Ajay Parmar

architecture icon Excel VBA,Development


30-days Money-Back Guarantee

Training 5 or more people ?

Get your team access to 19,000+ top Tutorialspoint courses anytime, anywhere.

Course Description

  1. In this Section1 - We are first starting with VBA Arrays Let us see what are we learning in Arrays.
  2. You will learn the Use of Powerful Arrays from Basics to Advance. You will be a master in creating your own designed Functions and your team can use it in excel
  3. How to Define Arrays in VBA using Dim and Redim statements.
  4. What are One dimensional arrays and multi-dimensional arrays.
  5. How to use Loops inside Arrays to fill its indexes.
  6. By default what is the index of arrays and how to change it using Option Base1.
  7. Interview related questions discussion on Arrays - Learn how to Crack it.
  8. What is the Role of Redim statement in Arrays. When you should declare dim and Redim - Practical knowledge
  9. Know the Importance of Preserve keyword in Arrays. What is the role of PRESERVE.
  10. How to declare static and dynamic arrays - Using constants and variables - Static and Dynamic
  11. How to Resize arrays in program as per the requirement.
  12. How to supply large real type data into arrays without running complex loops.
  13. How to use VLOOKUP in arrays and make your lookup tasks very easy.
  14. What does SPLIT Function does and why we need it.
  15. Things you need to take care in Defining Arrays using SPLIT Function.
  16. Real time based projects solutions for you to see actual use of Arrays.
  17. Why errors like Subscript Out of Range and Constant Expression required comes in the program - Full discussion on same.

  1. Let us now see in 2nd section. What we have for Functions - UDFs and INBUILT Functions.
  2. Learn how to create your own custom built functions . We call them USER DEFINED FUNCTIONS, UDF.
  3. How to write UDFs and why do we need it . Advantages of UDFs in VBA.
  4. How to use arguments in UDFs. What happens if you give more arguments and user uses different arguments
  5. How to debug functions . Can they run on F8 mode
  6. Learn about type mismatch errors if you provide wrong arguments types in your functions.
  7. How to use VLOOKUP , LEFT, RIGHT inbuilt functions in VBA.
  8. Several Projects for you to give practical visibility.
  9. You will also understand the mixing of arrays ,loops with functions which is going to be pretty awesome.
  10. Functions use Loops and Arrays a lot so we must know this as well other than understanding basics and fundamentals.
  11. Take a deep dive into VBA Date and time functions too.


What will you learn in this course:

  • In section1 , We will learn that What are arrays .Why we use them? Why they are so powerful and holds so much weightage in VBA learning.
  •  What is their by-default index system and how we can change it using option base. 
  • What are static and dynamic arrays.
  • how to use arrays with VBA loops and if functions . They work like magic together. 
  • What is the use of Preserve word in arrays and how we can use it in projects.
  • Complete tutorial with in-depth discussion.
  • How to use split function in arrays and what are the key things to be taken care while using split in arrays.
  • Questions discussed from  VBA interview perspective as well.
  • As usual- Assignments are also added to give you confidence and to check the progress.
  • how to use Dim or Redim Statements .Their real use in projects. How to use variable value in Arrays and what is the purpose of using them in arrays.
  • How to use split function ,one of the best function we can use in arrays. How arrays work under split function.
  • How option Base-1 works for arrays created by split function.
  • What is the use of preserve word in arrays and how we can use it to hold the values. What happens if we do not use them.
  • How and where we use them . Complete discussion on its use.
  • For more understanding on arrays tutorial we are discussing amazing Projects. Only for you. In-depth discussion.

  • In section2, We are learning everything about VBA Functions from their definitions to their practical use in excel day to day work life.

  •  Lessons are started from very basic understanding and gradually moving toward advance levels. 

  • You will see the use of functions practically as well and their powerful and unique combinations with loops and arrays. Must watch for wannabe developers

  • VBA has its own function library and it offers various powerful function just like excel. Let us see how to use Inbuilt functions of VBA

  • UDFs - User defined functions in VBA

  • Advantage of UDFs over Inbuilt excel functions. From limitations to dealing with complex situations - I have got everything for you. Many amazing projects are shared in the series.

  • Can we pass in parameters in sub routines . 

  • How to Call Functions from excel sheets or sub routines.

  • How to do debugging of UDFs and Inbuilt VBA Functions.

  • What is the process of passing parameters from a sub routine to a function and how it is useful for us.

  • Questions asked in your VBA Interview.

  • Assignments are added to give you a real exposure.

  • How to use VLOOKUP in VBA at advance level project discussed. Real application coming your way.

  • How to use Match function in a loop along with VLOOKUP to create this wonderful real practical automation.

  • My online support forever for you.


What are the prerequisites for this course?

You should have good knowledge of VARIABLES & LOOPS before opting for this Course.

If you know it , you can take this course otherwise you can see Part1 and Part2 before this one.

Excel VBA FUNCTIONS & ARRAYS with Projects - Series3


Check out the detailed breakdown of what’s inside the course

VBA ARRAYS - Section1
12 Lectures
  • play icon A demo Video on Arrays before we begin
  • play icon Demo for fun 14:58 14:58
  • play icon Introduction to Arrays- Let us Begin the Course
  • play icon Arrays Begin 01:21:27 01:21:27
  • play icon Introducing SPLIT Function in Arrays
  • play icon Split lecture 20:10 20:10
  • play icon A project for you Using Split function- super awesome
  • play icon Split project for you 42:13 42:13
  • play icon A lookup Project to give you sense of incredible Arrays- Very Practical Scenario
  • play icon Lookup project 44:03 44:03
  • play icon VLOOKUP Fantastic Projects on One dimensional and Two -dimensional Arrays
  • play icon Two Dimensional Project 59:57 59:57
20 Lectures

Instructor Details

ajay parmar

ajay parmar

Data Analytics Trainer

I have been teaching online from 10 years now and teaching is my hobby and passion. I cannot think of anything better than teaching and shaping up students career. Enroll today and take your knowledge to the next level. My training content is my proud and you will not be disappointed. I teach advance Excel, Excel VBA, MS Access, Access VBA, PowerQuery, M code in Powerquery, PowerPivot,DAX Formulas,Power BI and WebScraping using html libraries in VBA.

Show less

Course Certificate

User your certification to make a career change or to advance in your current career. Salaries are among the highest in the world.

sample Tutorialspoint certificate

Our students work
with the Best


Related Video Courses

View More

Annual Membership

Become a valued member of Tutorials Point and enjoy unlimited access to our vast library of top-rated Video Courses

Subscribe now
People having fun around a laptop

Online Certifications

Master prominent technologies at full length and become a valued certified professional.

Explore Now
People having fun around a laptop

Talk to us