Excel Tutorials and Access VBA Functions

Meadinkent.co.uk provides the following well documented help and tutorials for Microsoft Excel and Access. This site will help you to learn and use many features of Excel and shows you how to code visual basic functions for Access, keeping you productive with these two office applications. A detailed Excel help index is also available.


Excel Help and Tutorials

Here are many helpful Excel tutorials containing information on many features and functions of Excel, including functions and calculations, IF conditions and selecting, formatting cells and values, dates and times, sharing data with other programs, reference, navigation, graphs, and short cuts.

Functions and Calculations

• Sum, Average and Count – the most basic functions
• Financial functions such as compound interest and loan repayments
• A detailed example of calculating loan repayments
• Investment appraisal using NPV and IRR
• Weighted averages and assessing risk
• Frequency an array function to count ranges of values

IF, conditions and selecting

• DSum, DAverage – database functions
• VLookup, Match to find a particular value within a range of data
• If, And, Or, Rank, Errors – logical functions and preventing errors
• A macro and an Advanced Filter to select records
• Selecting values from drop down lists and boxes
• Conditional SUM formulae and a costing model using SumProduct
• Using Arrays to apply selective rules to functions

Formatting Cells and Values

• Text, Abs and formatting cells with numbers or dates
• Conditional formatting of cells (2 pages)
• A VBA function to return cell colors
• Summing the values of cells which are a particular color
• Protecting cells and worksheets
• Displaying number values as words
• Extracting text from strings and phrases
• Configuring the appearance of Excel at startup
• A VBA function to display cell contents

Dates and Times

• Date and Time functions and calculations
• Calculating hours worked and shift enhancements
• An office holiday leave planner (SumProduct + Conditional Formatting)
• Calculating the difference between two Dates

Sharing data with other programs

• A simple macro to create web pages from Excel
• A more complicated macro to make formatted web pages from Excel
• Creating XML data files from Excel and Access using VBA
• Linking tables and charts in a Word document to an Excel spreadsheet
• Using PowerPoint to display information from Excel (2 pages)
• Basic examples of displaying XML / XSL files in a web browser
• An example of a cascading style sheet for web page design
• Using a macro to export text files from tables and forms

Reference, Input and Navigation

• Graphs which grow dynamically to include new data
• Hyperlink navigation and buttons on worksheets
• Two forms to input data and add it to a list using VBA macros
• Using Indirect to enter cell references as text within formulas
• Using Offset and Choose to select particular cells or values

Auditing and other stuff

• Audit tools for checking a spreadsheet
• Testing and mapping spreadsheets to check for design errors
• Adding a macro to a toolbar and keyboard shortcuts
• Improving your graphs using pictures and better axis labels
• Tips for accountants on using Excel

Access Visual Basic Modules

The following are examples of various visual basic functions and procedures that can be used in Access databases. Some of these are linked to particular event properties on forms and reports. Most can be copied and pasted into modules and then called as needed. Be sure to always thoroughly test all of the code in your databases.

• Reports manager
• Using the Open Dialog box
• Creating web pages
• Various bits of code
• Status bar messages
• Complex criteria in queries
• Report template
• Various items in report properties
• Email distribution
• Program variables
• Generate XML files

Tags for this post>> Access | Excel