Excel VBA & Macro Training

4,5 (94 voting)
 Last update date 02/2024
 Türkçe

MS-SQL Server and PostgreSQL Database Programming check out our education.

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 

  • Forms 
  • Option Explicit   

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.

Comments

You are allow cookie by using us website. ENTER