DoEvents
In this blog post we discuss the DoEvents method in VBA. DoEvents is a simple command that pauses a loop and allows the operating system to carry out any tasks that have been queued. If you have a loop that…
Category Access Blog
Nested Loops
In this blog post we will be discussing nested loops and how they can be used. A loop inside a loop is termed a nested loop. We’ll make a grid of numbers to illustrate. Sub nestedLoop1() Dim y As Integer…
Loops – Do Until / Do While / Do
In this blog post we will be discussing the Do..Until/While loop. The Do…While loop keeps executing the loop as long as a certain condition is met. Sub doWhile1() Dim i As Long Dim kitchenItems(0 To 5)…
Loops – While Wend
In this blog post we take a look at the While…Wend Loop. The basic syntax of a While loop is: While(someExpression) … Wend The loop will continue to operate as long as someExpression is equal to true. When it becomes…
Loops – For Each
In this blog post we will be explaining the For…Each loop and providing some examples of its usage. Demonstrating The For Each Loop with An Array We can use the For Each loop over a standard array. Sub forEachArray() …
Loops – For Next
In this blog post we will introduce the For…Next Loop and provide some examples of its usage. The standard syntax of a For…Next loop is: For counter = start To end … next i In the code above counter is…
Working With Form Objects
In this blog post we will be taking a look at how you can work with form objects in MS Access. Using the collection Controls of a form, all controls can be cycled through and only those of a particular…
Collections
In this blog post we will be discussing collections and how they can be used in VBA. Sub carParts() ‘A collection is an object that has the ability ‘to store other objects. ‘ ‘Collections have 4…
Working With Arrays 2
In this blog post we will be expanding on the capabilities of arrays in VBA. One of the headaches with arrays is that they are static blocks of memory and are not designed to change in size. If we want…
Working With Arrays 1
In this blog post we introduce arrays and give you some examples of them in action. You can think of an array as a row of boxes with a number on each, 0 to n. When we first declare an…
Artithmetic Operators
In this blog post we will be examining Operators and how they can help you to construct if statements in VBA. An expression is a single or collection of variables and operators that ultimately evaluate to True or False. Here…
The If Statement
In this blog post we discuss the If…Then…Else statements that will allow you to add conditional logic to your posts. The Simple If Statement Sub standardExpressions() Dim a As Integer a = 10 If a = 10 Then Debug.Print “a…
Compilation Explained
In this blog post we will examine compilation and the effect it has on the code you write. Compilation is the act of converting our human readable code ( VBA) into code the computer understands. It may also be that…
Visual Basic Editor Options
In this blog post we take a look at some of the options available in the Visual Basic Editor (press ALT + F11 to open). VBA has a concise set of options and tools which you can set to change…
Immediate Window
In this blog post we look at how you can use the immediate window whilst coding. The immediate window is a fantastic tool for testing and debugging code. Here are a few simple commands (open the immediate window and type…
Debugging
In this blog post we will discuss debugging and provide you with some simple tips for debugging your code. In VBA when we write code, it often doesn’t work how we expect it to or we think it is working…
Writing A Custom Function
In this blog post we will create a custom function that returns someone’s age based upon their date of birth. To write a custom function we need to consider the following: A returned value is needed, so we must use…
Built-In Functions
In this blog post we cover some commonly used built-in functions that VBA coders need to know. String Functions Len(s) – returns the length of String s. Left(s, n) – returns a substring of s that is n chars long…
Data Types In VBA
In this blog post we explain data types in VBA and provide detailed definitions for when they should be used. Firstly a word on VBA variable names; a variable may be named anything you wish as long as it conforms…
Variables
In this blog post we cover declaring variables, naming conventions, scope and constants. When writing code in V.B.A. we often need to do calculations based on values that can change. An example would be working out the area of a…
Objects, Properties and Methods – An Analogy
In this blog post we provide an analogy to help conceptualise objects, properties and methods and the way in which they interact. Most people tend to understand that if you want to change the text in a command button you…
How to Convert a Standalone Macro
In this blog post, we will be showing you how to convert a Macro into VBA code. A Macro is an object that includes a list of instructions, and these instructions translate to VBA code almost directly. Rather than writing…
Basic Tools for Writing Code
In this blog post, we provide you with some basic tools for writing VBA code. The VBA Editor incorporates a number of useful features which help you whilst you are writing code. Here we’ll take a closer look at a…
The VBA Editor Explained
In this blog post, we explain how the VBA editor works. There are four main areas of the editor that you need to know about. The Code Window The Code Window is where all your VBA code will be written.…
Opening The VBA Editor
In this blog post, we will show you how to open the VBA editor. The above image is the VBA Editor with three areas highlighted; the Project Explorer, Code Window and Immediate Window. This is what is known as an…
The Macro Designer
In this post, we will be explaining the Macro Designer. Macros have a very concise builder. There is no need for formatters, physical dimensions or other fancies; a macro is a doing object that is invisible to the user except…
Form Design View Explained
Form Object select button Form Header Form Detail Form Footer There are four main parts to any form. The Form Object select button selects the form window object. The header, detail and footer are all…
Sorting And Filtering Forms
In this post, we will be providing you with some techniques for sorting and filtering records in a form. Just like a datasheet, we can alter the order of the records see. You can sort a form by clicking into…
Using The Like Operator In Queries
In this post, we will be demonstrating how you can use the LIKE operator in queries. Being able to filter queries is an important part of effective data retrieval. Although we often want to find exact matching values, sometimes we…
The Query Designer Explained
In this post, we will be explaining the constituent parts of the query designer. To open the query designer navigate to the Create Tab of the Ribbon and click on the Query Design icon. In the image below we…
