Excel VBA & Macro Training
The purpose of education
This education will teach VBA software language so you can use effective MS Office Programs. You will save time by creating Excel files more regularly and automating your repeated operations.
Who can participate in the education
It is suitable for the participation of individual participants and corporate employees who can use advanced level MS Office and Excel programs.
Learning Outcomes
- They will learn the Visual Basic for Applications (VBA) language in order to automate routine operations that are constantly repeated in MS Excel.
- This education aims at participants developing their own application to using VBA codes at MS Excel.
- The participants will create recording macros.
- The participants will assign macros to objects.
- The participants will learn basic concepts using VBA windows.
- The participants will learn operators, loops, procedures and structure of functions.
- The participants will create user forms and make error checking in codes.
Education Dates: First is got preregistration then educations are dated and informed according to demand.
The method of education: Online education (synchronous)
Duration of education: 24 hours
Number of participants: Minimum of 10 people
Education Content
Module 1: Overview and Record a Macro
- General knowledge about macros.
- Types of macros.
- Recording a macro.
- Running a macro.
- Editing a macro.
- Warning encountered when opening a macro saved workbook
- Understanding macro viruses.
- Detecting viruses in excel.
- Changing the protection level for Infected macro protection.
- Changing the macro-options
- Assigning the ready-made macro to the button or menu
- Deleting to saved macro
- Address in macros.
Module 2: Learning windows Visual Basic for Applications (VBA)
- Macros writing in VBA
- Project windows
- Properties windows
- Encrypting codes
Module 3: Variables, Loops, Condition Structures
- Variables and types of data
- Types of variables and identification of variables.
- Constants
- Loops
- Types of Loops
- For Each...... Next
- Do While......Loop
- Do .......Loop While
- Do Until...... Loop
- Do.....Loop Until
- Loop of Goto
- Conditional (Conditional) Structures
- If.....Then....Else
- Select Case
Module 4: Introduction to Writing Code
- Selecting cell
- Selecting row
- Selecting column
- Selecting all cells
- Selecting the entire row for the selected cells
- Selecting the entire column for the selected cells
- Selecting the solid cells around the active cell
- Learning the number of selected cells.
- Finding the number of selected row and column.
- Finding the number of selected areas
- Writing formula into cell.
- Assigning a value to a cell.
- Assigning a value to a selected cell
- Automatic editing
- Assigning the same value to all selected cells
- Changing the properties of the cell and font
- Filling the inside of the cell automatically
- Filling in the target Cells by referencing the source cells
- Assigning a random number to a cell
- Running other applications from within excel
Module 5: Workbook and Worksheet Codes
- Saving the active workbook
- To save all open workbooks
- Closing the active workbook
- Adding a new workbook
- Opening an existing workbook
- Adding a new page to the workbook
- Selecting a page
- Moving a page
- Learn the number of pages
- Learn the name of the active page
- Sorting pages from small to large
- The number of filled cells in the selection
- The number of cells that appear on the screen
- The location of the excel window
Module 6: MsgBox and InputBox
- Message box – MsgBox
- Function of MsgBox
- Data entry box – Inputbox
- Selecting the spacing of cells entered in the InputBox
- The Application object in the InputBox function
Module 7: Offset, Character, WorksheetFunction, Set
- Function of offset
- Interference with characters Inside the cell
- Some properties related to the WorksheetFunction object
- Property of sum
- Property of sumlf
- Property of count
- Property of countA
- Property of countBlank
- Property of cuntlf
- Property of min
- Property of max
- Property of rank
- Set assignment statement
Module 8: Functions of copy paste
- Copy and paste
- Transpose paste
- Copying a page
- Moving a page
Module 9: Operators
- Arithmetic operators
- Comparison operators
- Logical operators
- LIKE operators
- IS operators
- Not operators
- AND operators
- OR operators
Module 10: Functions
- Creating functions
- User-Defined Functions
- Some functions
- Date
- Day
- IsDate
- IsEmpty
- IsNumeric
- LCase
- Left
- Len
- LTrim
- Mid
- Month
- Now
- Rnd
- Round
- RTrim
- Ucase
- Val
Module 11: User Forms and Properties
Module12: Toolbox and Control Objects
- Toolbox
- Label
- TextBox
- ComboBox
- ListBox
- OptionButton
- Checkbox
- Group
- Frame
- ScrollBars
- ScrollBar
- ToggleButton
- RefEdit
- Image
- Picture
- UserForm
Module 13: Events
- Activate
- Change
- Click
- DblClick
- Enter
- Exit
- Initialize
- KeyDown, KeyUp, KeyPress
- MouseDown, MouseUp
- MouseMove
Module 14: Filtering and Sorting Operations
- Filter
- Subtotal and sequence
- Print Preview and Printing
- Description
- Error alert
Module 15: Error Checking
- Debugging window
- Error checking statements
- Error codes and explanations
The Process of Education:
- The training will be broadcast live simultaneously over the Internet.
- In order to participate in the training, you must have internet access and a suitable internet package in your current environment.
- In order for the educational system to be used,camera and microphone equipment have been introduced, a device that can connect to the Internet(smartphone, tablet and preferably computer) is needed.
- Participants will log in to the system with their cameras and microphones turned on together with our instructor who provides the training during the specified lesson hours.
- User names and passwords will be sent to the participants' e-mails before the training starts.
- After the trainings are completed, the system will be kept open for participants for another 7 days. By the end of the seventh day at 23:00, the system will be completely shut down.
- In order to help us during the training period, a whatsapp group will be created in the form of sending messages only to the administrator, and all kinds of support information will be shared by the administrator during the lesson period. Participants have all kinds of questions and problems usem@uludag.edu.tr they can write to our address. The mail will be followed up instantly during the training.
Important Information:
- Certificates are issued for participation of 80% and above of the total training period. for participation of 79% and below, no documents will be issued.
- After the completion of the training period, certificates will be issued after the controls belonging to our institution and will be sent to the addresses you entered at the time of registration with PTT Cargo. Your address and contact information must be up-to-date.
- Participants have all kinds of questions and problems usem@uludag.edu.tr they can write to our address. The mail will be followed up instantly during the training.
- Application for education and paying the tuition fee online are made via the Internet. Installment payments cannot be paid on the participants' bank credit cards.