8 Access VBA courses in Leicester delivered Live Online

Duration 1 Days 6 CPD hours This course is intended for This course is primarily designed for students who want to gain the skills necessary to use VBA to automate tasks in Excel such as collecting data from external sources, cleaning, and manipulating data. The target student may also want to learn how to create custom worksheet functions to streamline worksheet formulas and make complex worksheets easier to support, maintain, and understand. Overview Identify general components of VBA and their appropriate use in solving business solutions. Record VBA macros to automate repetitive tasks. Write VBA code to create VBA subroutines and functions. Use various programming elements to solve problems through code, including variables,objects, conditional statements, and loops. Eliminate, avoid, or handle errors in VBA code, and optimize its performance. VBA (Visual Basic for Applications) enables you to enhance and extend the capabilities of Microsoft Excel and other applications in the Microsoft 365 application suite. You can use VBA to perform tasks that would be difficult or impossible to do using only worksheet functions, and you can automate a wide range of tasks so they can be performed faster, more consistently, and with less effort than performing them manually. This course will give you a good foundation for understanding, creating, and using VBA in your own Excel workbooks. You will learn how to use the macro recorder effectively, as well as how to write your own VBA code from scratch. You will use tools built into Excel to explore and learn VBA's capabilities and to optimize and debug your code. Prerequisites To ensure your success in this course, you should be an experienced Excel user who is comfortable creating and working with Excel workbooks, including tasks such as entering worksheet formulas, using absolute and relative addressing, formatting cells, and creating pivot tables and charts. This level of skill could be acquired by taking the Microsoft Excel for Office 365 (Desktop or Online) courses, Parts 1, 2, and 3. 1. USING VBA TO SOLVE BUSINESS PROBLEMS * Use Macros to Automate Tasks in Excel * Identify Components of Macro-Enabled Workbooks * Configure the Excel VBA Environment 2. RECORDING A MACRO * Use the Macro Recorder to Create a VBA Macro * Record a Macro with Relative Addressing * Delete Macros and Modules * Identify Strategies for Using the Macro Recorder 3. WRITING VBA CODE DIRECTLY * Write VBA Code * Discover Objects You Can Use * Create a Custom Function 4. SOLVING PROBLEMS THROUGH CODE * Make Decisions in Code * Work with Variables * Perform Repetitive Tasks 5. IMPROVING YOUR VBA CODE * Debug VBA Errors * Deal with Errors * Improve Macro Performance 6. CONTROLLING HOW AND WHEN MACROS RUN * Prompt the User for Information * Configure Macros to Run Automatically ADDITIONAL COURSE DETAILS: Nexus Humans VBA with Microsoft Excel (v1.2) training program is a workshop that presents an invigorating mix of sessions, lessons, and masterclasses meticulously crafted to propel your learning expedition forward. This immersive bootcamp-style experience boasts interactive lectures, hands-on labs, and collaborative hackathons, all strategically designed to fortify fundamental concepts. Guided by seasoned coaches, each session offers priceless insights and practical skills crucial for honing your expertise. Whether you're stepping into the realm of professional skills or a seasoned professional, this comprehensive course ensures you're equipped with the knowledge and prowess necessary for success. While we feel this is the best course for the VBA with Microsoft Excel (v1.2) course and one of our Top 10 we encourage you to read the course outline to make sure it is the right content for you. Additionally, private sessions, closed classes or dedicated events are available both live online and at our training centres in Dublin and London, as well as at your offices anywhere in the UK, Ireland or across EMEA.

Duration 1 Days 6 CPD hours This course is intended for To ensure success, students should have completed Excel Essentials and Excel Functions Including Pivot Tables and Lookups or have the equivalent knowledge and experience. Overview Upon successful completion of this course, students will be able to enhance productivity and efficiency by streamlining workflow, collaborate with others, and audit and analyse data. This course is designed for students desiring to gain skills necessary to create macros, collaborate with others, audit and analyse data, incorporate multiple data sources, and import data. WORKING WITH MULTIPLE WORKSHEETS AND WORKBOOKS * Working with Named Ranges * Link Cells Across Worksheets and Workbooks * Use 3D References to Calculate Across Worksheets * Consolidate Data USE FORMULA AUDITING AND ERROR CHECKING * Reveal Formulas * Trace Cell Precedents and Dependents * Locate Errors in Formulas * Watch and Evaluate Formulas REVIEWING AND PROTECTING WORKBOOKS * Control Data Entry via Data Validation * Protect Workbook Access * Protect Worksheets and Cell Content * Add and Edit Comments * Prepare a Workbook for Distribution * Modify Excel's Default Settings USING MACROS TO AUTOMATE WORKBOOK FUNCTIONALITY * Create Macros via Recording * Run Macros via Buttons and Shortcuts * Assign Macros to the Quick Access Toolbar and Ribbon * Assign Macros to Objects * View Macro Code FORECASTING AND ANALYSIS DATA * Use Conditional Formatting to Highlight, Sort and Filter Key Data * Advanced Conditional Formatting using Formulas * Create Sparklines to Visualise Data * Add Trendlines to Charts to Visualise and Forecast Trends * Use Data Tables and Scenarios to Project Potential Outcomes * Use Goal Seek to Calculate Outcomes * Forecast Data Trends Using Solver

Duration 3 Days 18 CPD hours This course is intended for This course is primarily designed for students who want to gain the skills necessary to use VBA to automate tasks in Excel such as collecting data from external sources, cleaning, and manipulating data. The target student may also want to learn how to create custom worksheet functions to streamline worksheet formulas and make complex worksheets easier to support, maintain, and understand. Overview In this course, you will develop and deploy VBA modules to solve business problems. You will: Identify general components of VBA and their appropriate use in solving business solutions. Record VBA macros to automate repetitive tasks. Use reference tools built into Excel to get help on VBA programming language and objects used in the Excel VBA environment. Write VBA code to create a custom worksheet function. Eliminate, avoid, or handle errors in VBA code, and optimize its performance. Control how and when macros run. Develop UserForm objects to create custom dialog boxes and windows. Use VBA to read and write data from local files and cloud services. Use VBA to clean and transform data. Run programs and commands outside of Excel and share VBA projects with other users VBA (Visual Basic for Applications) enables you to enhance and extend the capabilities of Microsoft© Excel© and other applications in the Microsoft© Office application suite. You can use VBA to perform tasks that would be difficult or impossible to do using only worksheet functions, and you can automate a wide range of tasks involving the collection, processing, analysis, and visualization of data. This course will give you a good foundation for understanding, creating, and using VBA in your own Excel workbooks, show you how to work with data across different applications, and how to package the macros and functions you create so you can back them up, move them to other computers, and share them with other users Prerequisites To ensure your success in this course, you should be an experienced Excel user who is comfortable creating and working with Excel workbooks, including tasks such as entering worksheet formulas, using absolute and relative addressing, formatting cells, and creating pivot tables and charts. This level of skill could be acquired by taking the Microsoft Excel for Office 365? (Desktop or Online) courses, Parts 1, 2, and 3 1 - USING VBA TO SOLVE BUSINESS PROBLEMS * Topic A: Use Macros to Automate Tasks in Excel * Topic B: Identify Components of Macro-Enabled Workbooks * Topic C: Configure the Excel VBA Environment 2 - AUTOMATING REPETITIVE TASKS * Topic A: Use the Macro Recorder to Create a VBA Macro * Topic B: Record a Macro with Relative Addressing * Topic C: Delete Macros and Modules * Topic D: Identify Strategies for Using the Macro Recorder 3 - GETTING HELP ON VBA * Topic A: Use VBA Help * Topic B: Use the Object Browser to Discover Objects You Can Use in VBA * Topic C: Use the Immediate Window to Explore Object Properties and Methods 4 - CREATING CUSTOM WORKSHEET FUNCTIONS * Topic A: Create a Custom Function * Topic B: Make Decisions in Code * Topic C: Work with Variables * Topic D: Perform Repetitive Tasks 5 - IMPROVING YOUR VBA CODE * Topic A: Debug VBA Errors * Topic B: Deal with Errors * Topic C: Improve Macro Performance 6 - CONTROLLING HOW AND WHEN MACROS RUN * Topic A: Prompt the User for Information * Topic B: Configure Macros to Run Automatically 7 - DEVELOPING CUSTOM FORMS * Topic A: Display a Custom Dialog Box * Topic B: Program Form Events 8 - USING VBA TO WORK WITH FILES * Topic A: Use VBA to Get File and Directory Structure * Topic B: Use VBA to Read Text Files * Topic C: Use VBA to Write Text Files 9 - USING VBA TO CLEAN AND TRANSFORM DATA * Topic A: Automate Power Query * Topic B: Transform Data Using VBA and Workbook Functions * Topic C: Use Regular Expressions * Topic D: Manage Errors in Data 10 - EXTENDING THE PROGRAMMING ENVIRONMENT BEYOND THE WORKBOOK * Topic A: Run Other Programs and Commands * Topic B: Share Your VBA Projects

Duration 1 Days 6 CPD hours This course is intended for To ensure success, students should have completed Excel Essentials and Excel Functions Including Pivot Tables and Lookups or have the equivalent knowledge and experience. Overview Upon successful completion of this course, students will understand the programming environment of Visual Basic for applications and know how to create custom procedures and functions that can expand their ability to use Excel more effectively. This course is intended for the experienced Excel users that want to gain knowledge of visual basic for applications (VBA). INTRODUCTION TO MACROS * Recording Macros * Absolute and Relative * How to Run a Macro BASICS OF CODE WRITING * The VBA Environment (the ?VBE? window) * Essential Tips, Tricks, Shortcuts * Understanding VBA and Best Practice CRUCIAL VBA OBJECTS AND STRUCTURES * Variables and Data Types * Conditionals (If, Select Case) * Loops (For, Do) INTERACTION * Interact with Data, Sheets, the Excel Application * Interact with User (Message Box, Input Box) ERRORS * Run * Editing * Debuging * Handeling Errors ADDITIONAL COURSE DETAILS: Nexus Humans Excel - VBA Bootcamp training program is a workshop that presents an invigorating mix of sessions, lessons, and masterclasses meticulously crafted to propel your learning expedition forward. This immersive bootcamp-style experience boasts interactive lectures, hands-on labs, and collaborative hackathons, all strategically designed to fortify fundamental concepts. Guided by seasoned coaches, each session offers priceless insights and practical skills crucial for honing your expertise. Whether you're stepping into the realm of professional skills or a seasoned professional, this comprehensive course ensures you're equipped with the knowledge and prowess necessary for success. While we feel this is the best course for the Excel - VBA Bootcamp course and one of our Top 10 we encourage you to read the course outline to make sure it is the right content for you. Additionally, private sessions, closed classes or dedicated events are available both live online and at our training centres in Dublin and London, as well as at your offices anywhere in the UK, Ireland or across EMEA.

This one-day workshop is ideal for those looking to work with existing databases as well as creating a new Access database. You will learn effective ways to enter and extract data and convert data into a well-presented format for reports. This course will help participants: * Plan and create a database * Sort and filter records * Use field data types * Work with tables * Create and manage a query * Create and work with forms, and use the form wizard * View, create and print reports 1 ACCESS INTRODUCED * Planning a database * Creating a blank database * Opening an existing database * Security warnings * The navigation pane * Previewing database components * Closing an Access database 2 TABLES INTRODUCED * Viewing and navigating table data * Navigating using keyboard shortcuts * Editing table data * Adding records to tables * Selecting and deleting records * Finding and replacing data * Filtering tables * Summing table data 3 QUERIES INTRODUCED * Opening a query in datasheet view * Opening a query in design view * Adding fields to a query * Sorting query results * Modifying datasheet view * Saving and closing queries 4 FORMS INTRODUCED * Working with form data * Sorting records * Filtering records * Working in design and layout views * Changing object attributes 5 REPORTS INTRODUCED * Opening and viewing reports * Viewing a report in design view * Previewing and printing report data * Creating reports with auto report * Creating reports with the report wizard * Changing field attributes 6 CREATING A NEW DATABASE * Creating database tables * Creating table fields * Selecting field data types 7 TABLE RELATIONSHIPS * Introducing relationships * Establishing table relationships * The one-to-many relationship type * Establishing referential integrity 8 CREATING QUERIES * Streamline query criteria using wildcards * Creating summary queries * Creating queries based on criteria * Running date queries * Formatting field properties * Building queries from multiple tables * Summing in queries 9 CREATING FORMS * Creating a new form * Inserting form fields * Working with the property sheet * The command button wizard * The command button wizard * Programming without typing * Creating forms with embedded sub forms * Creating forms with the sub form wizard * Creating forms with the form wizard 10 CREATING REPORTS * What are report bands? * Managing report controls * Basing reports on multiple tables * Basing reports on queries * Sorting and grouping records

Most people only use a fraction of Excel's capabilities. This workshop shows what you've been missing! This course will help participants: * Nest formulas * Get the most from pivot tables * Use conditional formatting * Write array formulas * Explore the lookup functions * Calculate by criteria * Use 'goal seek' and 'scenario manager' for what-if analysis * Record macros 1 NESTING FORMULAS * Principles of nesting formulas together * Using IF with AND or OR to answer questions * Nesting an AND function in an IF * Nesting an OR function in an IF 2 ADVANCED PIVOT TABLE REPORTS * Grouping dates, numerical and text items * Running percentage analyse * Running analyses to compare data * Inserting Field calculations * Finishing off with a user-friendly dashboard 3 ADVANCED CONDITIONAL FORMATTING * Colour table rows based on criteria in it * Applying colour to approaching dates * Exploring the different rule types 4 LOOKUP FUNCTIONS * Going beyond the VLOOKUP function * Lookups that retrieve data from left or right * The versatile INDEX and MATCH functions * Retrieving data from columns with duplicates 5 CALCULATE BY CRITERIA * Using SUMIFS to sum by criteria * Finding an average by criteria with AVERAGEIFS * Use SUMPRODUCT to multiply then add different values 6 WHAT-IF ANALYSIS * Use Goal Seek to meet targets * Forecast reports with the Scenario Manager 7 RECORDING MACROS * Macro security * Understanding a Relative References macro * Recording, running and editing macros * Saving files as Macro Enabled Workbooks * Introduction to VBA code * Making macros available across workbooks * Add a macro button to the Quick Access toolbar

This one-day course focuses on issues such as writing formulas and accessing help while writing them, and taking formulas to the next level by nesting one inside another for a powerful formula result. It also looks at ways of analysing data with reports, summarised by varying criteria. A range of time-saving tips and tricks are shared. This course will help participants: * Calculate with absolute reference * Group worksheets * Link to tables * Use the function library effectively * Get to grips with the logical IF function * Use conditional formatting * Create pivot table reports * Use data validation * Master the VLOOKUP function 1 CALCULATING WITH ABSOLUTE REFERENCE * The difference between a relative and absolute formula * Changing a relative formula to an absolute * Using $ signs to lock cells when copying formulas 2 GROUPING WORKSHEETS * Grouping sheets together * Inputting data into multiple sheets * Writing a 3D formula to sum tables across sheets 3 LINKING TO TABLES * Linking to a source table * Using paste link to link a table to another file * Using edit links to manage linked tables 4 THE FUNCTION LIBRARY * Benefits of writing formulas in the function library * Finding the right formula using insert function * Outputting statistics with COUNTA and COUNTBLANK * Counting criteria in a list with COUNTIFS 5 LOGICAL IF FUNCTION * Outputting results from tests * Running multiple tests for multiple results * The concept of outputting results from numbers 6 CONDITIONAL FORMATTING * Enabling text and numbers to standout * Applying colour to data using rules * Managing rules * Copying rules with the format painter 7 VIEW SIDE BY SIDE * Comparing two Excel tables together * Comparing two sheets together in the same file 8 PIVOT TABLE REPORTS * Analysing data with pivot tables * Managing a pivot table's layout * Outputting statistical reports * Controlling number formats * Visualising reports with pivot charts * Inserting slicers for filtering data 9 DATA VALIDATION * Restricting data input with data validation * Speeding up data entry with data validation 10 VLOOKUP FUNCTION * Best practices for writing a VLOOKUP * A false type lookup * A true type lookup * Enhance formula results with IFNA 11 PRINT OPTIONS * Getting the most from print * Printing page titles across pages * Scaling content for print

This one-day workshop will give you a better understanding of the components and operations of an Access database. It is designed to build on a user's existing skills and includes useful action queries to allow greater manipulation of a database. This workshop will help participants: * Ensure the integrity of their databases * Manage field properties * Use the query functions effectively * Save time with the query expression builder * Create different types of query more quickly * Design better forms * Link expressions in forms * Create better and more useful reports * Import and export tables more easily 1 TABLE RELATIONSHIP INTEGRITY * Identifying relationships * Identifying criteria for data integrity * Applying referential integrity * Managing relationship join types 2 TABLE FIELD PROPERTIES * Field properties overview * Using input mask field * Using default value fields * Using field validation rules 3 QUERY FUNCTIONS * Running aggregate function calculations * Running sum, average, count, max and min functions * Grouping calculated data 4 QUERY CALCULATIONS * Using query operators and expressions * Adding calculated fields to a query * Using the query expression builder 5 ACTION QUERIES * Creating make table queries * Creating append queries * Creating update queries * Creating delete queries 6 DESIGNING FORMS * Adding form controls * Aligning and arranging form controls * Adding pictures and labels to forms * Adding new fields to a form * Controlling tab order * Adding command buttons * Adding a combo box control * Formatting data using conditional formatting 7 FORM EXPRESSIONS (CALCULATIONS) * Using the form expression builder * Working with a property sheet within a form * Linking expressions within a form 8 WORKING WITH REPORTS * Creating reports with the report wizard * Inserting report fields * Formatting fields * Inserting report headers and footers * Working with a property sheet within a report 9 GROUPED REPORTS * Creating groups with the report wizard * Sorting grouped data * Grouping alphabetically * Grouping on date intervals * Creating sub reports * Adding calculations to groups 10 IMPORTING AND EXPORTING TABLES * Importing tables into Access * Exporting tables from Access * Importing and linking data in Access