What Is the Primary Purpose of DoCmd.OpenForm in Microsoft Access VBA? Video Quiz D1.3

Time for a quick Access VBA quiz. This one covers a handful of beginner developer concepts that come up constantly once you start putting buttons, forms, and a little code into your database. See how many you get right before checking the answers.

The questions focus on opening forms with VBA, where button code belongs, what optional arguments mean, filtering a form as it opens, and why reusable procedures are your friend. In other words, the stuff that keeps you from copying the same three lines of code into seventeen different buttons. We've all been there.

Question 1: What is the primary purpose of the DoCmd.OpenForm command in Access VBA?

A. Open a specified form
B. Create a new table
C. Print the active report
D. Import records from Excel

Answer: A. Open a specified form.

DoCmd.OpenForm does exactly what its name suggests: it tells Access to open a form. You provide the form's name, and Access displays it for the user. As you get more comfortable with the command, you can add arguments to control things such as the view, data-entry mode, window mode, and which records should be displayed.

Question 2: Where would you normally place VBA code that should run when a user clicks a command button on a form?

A. In the button's On Click event procedure
B. In the table's primary key property
C. In the form's Record Source query
D. In the Navigation Pane

Answer: A. In the button's On Click event procedure.

A command button has an On Click event specifically for this purpose. When the user clicks that particular button, Access runs the code assigned to its event procedure. The procedure will normally have a name ending in _Click. That is where you might put a simple OpenForm command, validation logic, a confirmation message, or a call to another reusable procedure.

Question 3: In a VBA command's argument list, what do square brackets around an argument usually mean?

A. The argument is optional
B. The argument must contain a date
C. The argument is a table name
D. The argument can only be used in a query

Answer: A. The argument is optional.

When you look at Access VBA documentation and see an argument inside square brackets, that means you can leave it out. Access will use its normal default behavior. This is why a simple command can be as short as opening a form by name, while the same command can also be expanded later when you need more control.

Question 4: When opening a form, what is a WhereCondition commonly used for?

A. Showing only records that meet specified criteria
B. Renaming the form before it opens
C. Changing the form's design permanently
D. Creating a relationship between tables

Answer: A. Showing only records that meet specified criteria.

A WhereCondition filters the form as it opens. For example, if the user is looking at a customer in a list and clicks a button, you can open the customer form directly to that customer's record instead of opening the form to every customer in the database. This is one of the most useful reasons to use OpenForm from a button.

Question 5: Why is it often better to put repeated VBA instructions into a separate Sub procedure?

A. It keeps the shared code in one place
B. It automatically creates a new table
C. It prevents users from editing records
D. It removes the need for event procedures

Answer: A. It keeps the shared code in one place.

If several buttons need to perform the same task, putting that task in one separate Sub procedure makes your database easier to maintain. If the process changes later, you update it once instead of tracking down duplicate code scattered across multiple forms and event procedures. Less duplicate code means fewer opportunities for one button to get forgotten and quietly do something different. Access is quite good at quietly letting you create that kind of problem.

If you got all five, congratulations, you know where your towel is. If any of these questions gave you trouble, watch the embedded video and review the concepts. These are foundational skills for working with forms and VBA in Microsoft Access.

Live long and prosper,
RR

When Does the After Update Event Run for a Text Box in Microsoft Access? Video Quiz D1.6

Time for a quick Access developer quiz. This one covers a few small but important details that come up constantly when you are working with forms and VBA: event procedures, opening forms at new records, Yes/No controls, and knowing exactly when a text box's After Update event runs. These are the kinds of details that separate a form that behaves nicely from one that seems possessed by the invisible monster.

Try answering each question before reading the answer. If you want to make it a proper quiz, give yourself about three seconds per question. No peeking. Well, minimal peeking.

{{YOUTUBE_EMBED_PLACEHOLDER}}

Question 1: Which Access option makes the Code Builder the default choice when you create an event procedure?

A. Compile On Demand
B. Require Variable Declaration
C. Always Use Event Procedures
D. Show Hidden Objects

The answer is C. Always Use Event Procedures. This setting is found in Access Options. When it is enabled, Access assumes you want VBA code when you create an event handler. Otherwise, Access may ask whether you want to use a macro, expression, or code builder every time. If you are writing VBA regularly, that extra question gets old fast.

Question 2: Which DataMode argument opens a form directly at a new blank record with DoCmd.OpenForm?

A. acFormEdit
B. acFormReadOnly
C. acFormAdd
D. acFormDS

The answer is C. acFormAdd. Opening a form with acFormAdd takes the user directly to a new blank record, ready for data entry. This is useful when the purpose of a button is clearly "add a new customer," "create an order," or something similar. There is no reason to make the user open the form, land on an old record, and then hunt for the new-record button.

Question 3: Which event should contain VBA that runs after a user changes a text box value and commits that change?

A. After Update
B. On Click
C. On Current
D. On Open

The answer is A. After Update. The After Update event runs after the value in that particular control has changed and the change has been committed. Usually, that happens when the user leaves the control or otherwise causes Access to save its new value.

This matters because On Click does not tell you that the user changed anything. They might click in a text box, click it again, stare at it thoughtfully, and leave without touching a character. No change occurred. After Update is the event to use when your code depends on a real change to the control's value.

Question 4: For a Yes/No control named ShipSameAsBilling, which VBA condition correctly tests whether it is checked?

A. If ShipSameAsBilling Then
B. If ShipSameAsBilling = "Yes" Then
C. If ShipSameAsBilling Is Text Then
D. If CheckBox(ShipSameAsBilling) Then

The answer is A. If ShipSameAsBilling Then. A Yes/No control already evaluates as True or False, so VBA can use it directly in an If statement. You can write If ShipSameAsBilling = True Then if you really want to, but it is extra typing without adding anything useful. Save those keystrokes for something exciting, like correcting a typo in a field name.

Question 5: Suppose your form copies billing address fields into shipping address fields only while ShipSameAsBilling is checked. The user unchecks that box and later changes the billing city. What should the Billing City After Update code do?

A. Clear the shipping city automatically
B. Leave the shipping city unchanged
C. Copy the billing city anyway, then uncheck the box again
D. Save the record and close the form

The answer is B. Leave the shipping city unchanged. Once the user unchecks Same as Billing, they are telling you that the shipping address is now independent. They may be sending the order to a different office, a customer, a relative, or a secret volcano lair. Your billing-city After Update code should first check whether the box is still checked. Only then should it copy the billing city to the shipping city.

That simple condition prevents a very common form-design mistake: overwriting data the user deliberately customized. The After Update event is powerful because it lets your form respond right after a meaningful edit, but it still needs to respect the user's choices.

If you got all five, congratulations, you have successfully calmed the ancient Access mind machine. If not, watch the embedded video for the quiz walkthrough, and check out Access Developer Level 1, Lesson 6 for more work with the After Update event and form automation.

Live long and prosper,
RR

Can You Open Related Contacts From a Customer Form in Microsoft Access? Video Quiz X2.4

Time for a quick Access forms quiz. This one covers opening related records, pulling values from open forms, continuous-form labels, and one very important detail: making sure a new parent record is saved before you try to add its child records. See if you can get all five before checking the answers.

Give yourself a few seconds for each question. If you need more time, pause the video. No shame in that. Access forms can be wonderfully useful, right up until an unsaved customer record sends your related contact form wandering off into the laboratory unsupervised.

Question 1: What should an Access command button configured to open a related ContactF from CustomerF normally do?

A. Open every contact record with no filter
B. Open only contacts whose CustomerID matches the current customer
C. Copy all contact records into CustomerT
D. Create a new table relationship each time it is clicked

Answer: B. The button should open only the contacts whose CustomerID matches the CustomerID on the customer currently displayed in CustomerF. The whole point is to show that customer's contacts, not make the user search through contacts belonging to every customer in the database. The table relationship already exists. You do not need to rebuild it every time someone clicks a button. That would be a bit dramatic.

Question 2: Which expression correctly retrieves the value of CustomerID from an open form named CustomerF?

A. =CustomerF!CustomerID
B. =Forms!CustomerID!CustomerF
C. =Forms!CustomerF!CustomerID
D. =Forms!CustomerF_CustomerID

Answer: C, =Forms!CustomerF!CustomerID. The Forms collection represents forms that are currently open. You specify the form name first, then the control name whose value you want. If your form or control names contain spaces, use brackets, such as Forms![Customer F]![Customer ID]. Of course, avoiding spaces in object and control names makes life easier, but that particular horse left the barn in a lot of older databases.

Question 3: What condition is required for a Default Value expression to read a control from another form with the Forms collection?

A. The source form must be open
B. The source form must be in Design View
C. Both forms must use the same record source
D. The source control must be an AutoNumber field

Answer: A. The source form must be open. Access can only retrieve a value with an expression like Forms!CustomerF!CustomerID if CustomerF is currently loaded. If someone opens the contact form directly instead of opening it from the customer form, there may be no open customer form from which to retrieve the default CustomerID.

Question 4: In a Continuous Forms view, where should you place column labels so they appear once above the repeating records?

A. In the Detail section
B. In the Form Footer
C. In a bound text box
D. In the Form Header

Answer: D, in the Form Header. Controls in the Detail section repeat once for every record. That is exactly what you want for fields like FirstName, LastName, Phone, and Email. Labels that should appear only once, like column headings, belong in the Form Header. Putting them in Detail means every record gets its own copy, which is not a heading. It is just visual clutter with ambitions.

Question 5: A user enters a new customer, and the record selector shows the pencil icon. Before opening a form to add that customer's related contact, what should happen?

A. Refresh only the contact form
B. Save or commit the customer record
C. Use DLookup to retrieve the new CustomerID
D. Change CustomerID to a Short Text field

Answer: B. Save or commit the customer record first. The pencil icon means the current record is dirty, meaning it has been edited or entered but has not yet been saved. Until Access commits that customer record, an AutoNumber CustomerID may not be available for the related contact record. Save the parent first, then open the child form and pass along the CustomerID.

How did you do? Five out of five earns you Director of the Facility Beneath the Facility. Missed a couple? No worries. These are exactly the sorts of little form details that separate a database that mostly works from one that behaves properly when real people start clicking buttons.

Watch the embedded quiz video for the full questions and answers, and if you want to go deeper into passing values between forms, filtered related forms, default values, and parent-child record handling, check out Access Expert Level 2, Lesson 4.

Live long and prosper,
RR

Microsoft Access Let Users Choose Which Fields Appear on a Continuous Form

One of the nice things about a continuous form is that you can make it look exactly the way you want. One of the annoying things about a continuous form is that when you hide a control in the middle of a row, Access leaves a big ugly gap behind. Your users may only want to see a few columns, but you still want the form to look intentional and not like somebody knocked out a tooth.

The solution is to give users a set of check boxes that determine which fields they want to see, then use a little VBA to both hide unwanted controls and slide the remaining controls to the left. The result behaves a little like a configurable datasheet, but you retain all the advantages of a continuous form: headers, footers, buttons, totals, formatting, and complete control over the interface.

Datasheet View is still a perfectly good option in many situations. Users can hide, resize, reorder, sort, and filter columns without you having to write a single line of code. If all you need is a spreadsheet-style grid, a datasheet may be the better answer.

But continuous forms are better when you want a polished interface. You can place command buttons in the header, put totals in the footer, use custom colors and formatting, add calculated fields, and make the form look like part of your application instead of a raw table that escaped into the wild.

The basic idea is straightforward. For every optional field on the form, create a corresponding unbound check box. If the check box is checked, that field is displayed. If it is unchecked, the field is hidden. You should also hide the field's matching label at the same time, otherwise you end up with headings that have no data below them, which is only slightly less confusing than the original problem.

A consistent naming convention makes this much easier. For example, a text box named FirstName can have a check box named FirstNameCHK and a label named FirstNameLabel. The exact names are up to you, but consistency matters. Future You will appreciate it, and Future You is often crankier than Present You.

Set the Default Value of each check box to Yes or No depending on whether that field should normally appear when the form opens. You might always show Customer ID, for example, but leave something like Credit Limit hidden by default unless the user specifically asks to see it.

The VBA routine has two jobs. First, it assigns each field's Visible property from the value of its check box. Conceptually, it is as simple as setting the field and its label to visible when the check box is true, and invisible when it is false.

That alone hides the data, but it does not close the gaps. The second job is where the useful part happens. The routine keeps a variable that represents the next available horizontal position on the form. In other words, it tracks where the next visible column should begin.

Start that position with the Left value of the first optional field. Then work through your fields in display order. For each field that is selected, move both the control and its label to the current position. After placing the field, increase the current position by that control's width. Then move on to the next field.

So if First Name is visible, it occupies the first available space and advances the position by its width. If Last Name is hidden, it does not advance the position at all. State, Customer Since, Credit Limit, or whatever comes next gets placed immediately after the previous visible field. No blank spaces. No missing teeth.

You will want to use the controls' actual Width values rather than guessing at measurements. Access stores these measurements internally in twips, and there is no need to sit there manually calculating them like you are measuring a kitchen floor with spaghetti noodles. A quick temporary VBA routine can read each control's Width property and help you create constants for the layout routine.

Using constants for widths is fine for a small form with a handful of fields. It keeps the layout logic easy to read and makes the routine predictable. If you later resize a control, though, remember that the saved width in your code must be updated too. This is one of those tiny maintenance details that is easy to forget until one column suddenly overlaps another.

Run the repositioning routine whenever a user clicks or updates one of the selector check boxes. It is also a good idea to call the same routine from the form's Open or Load event. That way, if certain fields default to hidden, the form opens in its compact layout immediately instead of briefly showing gaps until the user clicks something.

If the form can potentially display many fields, consider the width of the form itself. You may want to turn off Auto Resize and allow a horizontal scroll bar, especially if users can select more columns than will comfortably fit on screen. Otherwise, Access may happily stretch the form wider than you intended.

This technique can also be expanded in some useful directions. You can save each user's preferred field selection, provide different layouts for different job roles, add a Reset to Default button, or store layout information in a table rather than hard-coding it in VBA. Once you start managing lots of forms and columns, a reusable layout system becomes much easier to maintain than a pile of one-off procedures.

For a small continuous form, though, the simple approach works beautifully: let users choose fields with check boxes, hide the controls and labels together, and reposition every visible field using its Left and Width properties. It gives users flexibility without sacrificing the carefully designed look of your form.

The embedded video includes the full walkthrough, including the VBA implementation, reading control widths, wiring up the events, and seeing the fields slide into place as users make their selections.

Live long and prosper,
RR

Microsoft Access Error 3021 No Current Record Causes and Fixes

Microsoft Access Run-time Error 3021, "No current record," is one of those errors that can make you look at your code and say, "But the recordset definitely has records in it." And it might. This error does not necessarily mean your query returned nothing. More often, it means VBA is trying to use a record when the recordset pointer is not actually sitting on a valid record.

The important distinction is between the records contained in a recordset and the record that is currently selected. A recordset may contain one record, one hundred records, or none at all. But when you try to read a field such as RS!FirstName, edit a field, delete a record, or otherwise work with it, VBA needs to be positioned on a real record. If it is before the first record or past the last one, Access raises Error 3021.

Think of a recordset like a stack of customer cards. Normally, VBA has its finger on one card, and that card is the current record. When you ask for a field value, VBA reads it from that card. But after certain navigation commands, VBA can wind up with its finger on the space before the first card or after the last card. There may be plenty of cards in the stack, but there is no card currently selected.

Those two positions are represented by the BOF and EOF properties. BOF means Beginning Of File, or before the first record. EOF means End Of File, or after the last record. Neither of those positions represents an actual record, so field references are unsafe there.

The most obvious cause of Error 3021 is an empty recordset. For example, suppose you open a recordset to find a customer with an ID that does not exist. Opening the recordset usually succeeds just fine. You still have a valid recordset object. It simply contains zero records. The error occurs when your next line assumes a customer was found and tries to read a field.

That is why you should check a recordset immediately after opening it if there is any chance the query could return no rows. For an empty DAO recordset, both BOF and EOF will normally be True. The familiar check is If RS.BOF And RS.EOF Then. If that condition is true, tell the user no matching record was found, exit the procedure, or take whatever action makes sense in your database. Just do not continue on and ask for a field value from a record that does not exist. That is like trying to read the phone number from a blank divider card in an old filing cabinet.

However, an empty query is only one possible cause. The recordset can be full of data and still have no current record. A very common example happens when code calls MoveNext one time too many. If you are on the last record and move next again, the records do not disappear. Your recordset still contains all of them. But VBA is now positioned after the final record, so EOF becomes True and there is no current record to read.

This is especially easy to do in loops. If your loop processes a record, moves to the next one, and then tries to use a field before checking whether EOF is True, you have just walked off the end of the bridge. The bridge may have been perfectly fine. You simply took one step too far.

Here is the useful diagnostic difference: an empty recordset normally has BOF=True and EOF=True. A recordset that contains records but has moved past the end normally has BOF=False and EOF=True. On the other side, if you call MovePrevious too many times, you can end up with BOF=True and EOF=False. In all three cases, there is no current record. Only one of those cases means there were no records in the first place.

Another frequent trouble spot is FindFirst. Suppose you have a recordset full of customers and search for one specific CustomerID. The recordset may contain thousands of valid records, but the particular customer you requested might not be there. After using FindFirst, check the recordset's NoMatch property before attempting to use any fields. If NoMatch is True, your search failed. Handle it intentionally instead of pretending it worked and then wondering why Access is yelling at you.

When Error 3021 appears, click Debug. Do not just close the error message and definitely do not slap On Error Resume Next on top of everything. That is not fixing the flat tire. That is turning up the radio so you do not hear the thump-thump-thump anymore.

Once Access highlights the failing line, look at what that line is trying to do. It will often be reading a field, assigning a field, editing a record, or deleting one. Then open the Immediate Window in the VBA editor and check the recordset's state. Enter ? RS.BOF and ? RS.EOF. If you just used FindFirst, check ? RS.NoMatch as well. Those three little properties can tell you very quickly whether you have an empty query, a failed search, or code that moved beyond the available records.

The real debugging question is: How did my pointer get here? Did the query return no rows? Did a loop call MoveNext one extra time? Did FindFirst fail? Did code move backward before the first record? Once you identify how the recordset got into that position, the fix is usually simple.

Error 3021 becomes much less mysterious once you remember that it is about position, not just record count. Verify that a valid record is current before reading or changing fields. Check for an empty recordset after opening it, check NoMatch after searches, and test your BOF and EOF boundaries while navigating. The embedded video includes a full walkthrough of these situations and how to inspect them while your VBA code is paused in Debug mode.

Live long and prosper,
RR

Why Do Date Time Fields Sort From Oldest To Newest In Microsoft Access? Video Quiz B1.8

Time for a quick Access sorting and filtering quiz. These are beginner-level questions, but they cover a few things that trip people up all the time, especially text fields containing numbers, Date/Time sorting, and what happens when you apply more than one filter.

Give yourself a moment to answer each question before looking at the answer. If you need more than three seconds, no worries. The computer will not revoke your bridge access. Probably.

Question 1: True or False: Filtering a table deletes the records that do not match the filter.

Answer: False. A filter only changes which records you can currently see. The records that do not match the filter are still in the table. Access is just hiding them from the datasheet temporarily.

This is an important distinction. Filtering is safe for narrowing down a datasheet so you can focus on one customer, one state, one date range, or whatever group of records you need to work with. It does not delete anything. If you want to remove records permanently, that is a completely different operation, and one you should approach with a little more caution.

Question 2: A field is stored as Short Text and contains these values: 5, 6, 9, and 555. What is its ascending sort order?

Answer: 5, 555, 6, 9.

The key phrase is Short Text. Even though the values look like numbers, Access is treating them as characters. Text sorts character by character, not based on mathematical value. Since 555 starts with the character 5, it comes after 5 but before anything beginning with 6.

This is one of the most common beginner mistakes in Access. If a field is supposed to hold quantities, prices, invoice numbers you need to calculate with, or other truly numeric values, it should generally be a Number field rather than Short Text. Text fields are fine for things that look numeric but are really identifiers, such as ZIP codes, phone numbers, or account codes that may contain leading zeroes.

Question 3: Why does an Access Date/Time field sort correctly from oldest to newest?

Answer: Dates are internally stored as numeric values, with earlier dates being smaller.

Access stores dates and times as numbers behind the scenes. Earlier dates have smaller values, so sorting a Date/Time field from oldest to newest works much like sorting a Number field from smallest to largest.

The date portion represents whole days, and the time portion is stored as a fraction of a day. That is why Access can do useful date math. You can add days, subtract dates to find the number of days between them, and sort appointments chronologically without having to alphabetize month names or manually arrange anything.

For example, January 15 comes before February 2 because its internal date value is smaller, not because Access is looking at the letters in "January." If dates were stored as plain text, sorting could get messy very quickly. Nobody wants April, August, December, February, January. That is not a calendar. That is a cry for help.

Question 4: You filter a customer table for State = New York, then add another filter for City = Buffalo. Which records will be shown?

Answer: Only customers whose State is New York and whose City is Buffalo.

When you apply filters on separate fields, Access combines the conditions. In this case, a record has to meet both requirements: the State must be New York and the City must be Buffalo.

Filtering becomes especially useful when you start narrowing down larger tables. You might filter for active customers, then add a filter for a particular salesperson, and then perhaps limit the records to a certain date range. Each additional filter reduces the visible records to those that match all of the active criteria.

Question 5: After you save a table with a filter applied, what normally happens when you close and reopen that table?

Answer: The filter definition is saved, but it does not automatically reapply.

Access can remember the last filter you used with the table, but normally it opens the table showing all records. The saved filter is still available, and you can turn it back on when needed. If you regularly need the same filtered view, however, a query is often the better long-term solution.

A query gives you a reusable saved view of your data. Instead of repeatedly filtering the table for customers in Buffalo, New York, for example, you can create a query that always shows those records. Tables are where the data lives. Queries are where you ask useful questions about it.

How did you do? If you got all five, congratulations, you have successfully sorted the crew and filtered the anomalies. If you missed a few, that is exactly why these quizzes exist. Sorting and filtering are fundamental Access skills, and getting comfortable with them will make working with tables much easier.

You can watch the embedded video for the quiz questions and answers, and check out Access Beginner Level 1, Lesson 8 for more practice with sorting and filtering.

Live long and prosper,
RR

Microsoft Access Error 9 Subscript Out of Range Causes and Fixes

Run-time error 9, "Subscript out of range," is one of those VBA errors that sounds more intimidating than it really is. In plain English, your code asked for something that does not exist. Usually it is an array element, but it can also be a collection item or a key that is missing. It is like reaching for the sixth donut when the box only had five. Disappointing, yes, but at least the problem is fairly straightforward once you know where to look.

A subscript is simply the index or key VBA uses to identify one item in a group. If you have an array with five positions, numbered 1 through 5, then asking for item 3 is perfectly fine. Asking for item 6 is not. VBA cannot retrieve, read, or write an item that was never created, so it stops the code and gives you error 9.

Arrays are probably the most common place Access developers run into this error. An array lets you store related values under one variable name. Instead of separate variables like Color1, Color2, and Color3, you might use one array called Colors with several indexed positions.

For example, if an array is declared with indexes from 1 to 3, the valid positions are Colors(1), Colors(2), and Colors(3). The first valid index is called the lower bound, and the last valid index is called the upper bound. Anything outside those boundaries is out of range.

So if your array runs from 1 through 5, X(5) is valid. X(6) is too high and causes error 9. But do not forget about the other end of the range. X(0) is also invalid because it falls below the lower bound. A lot of people assume their loop counter got too large, and sometimes it did. But it can just as easily be zero, negative one, or some other value below the first valid position.

One of the most common beginner traps involves VBA's Array() function. Arrays created with Array() are normally zero-based. That means an array containing Red, Green, and Blue uses indexes 0, 1, and 2. It has three values, but its highest valid index is 2, not 3.

This is where the classic off-by-one error comes in. You see three items and write a loop that runs from 0 to 3. It works for indexes 0, 1, and 2, then makes one extra trip when the counter reaches 3. At that point, VBA is looking for a fourth item that does not exist. Boom. Error 9.

The best habit you can develop is to stop guessing where an array starts and ends. VBA gives you two functions that do the work for you: LBound returns the lower bound, and UBound returns the upper bound. When looping through an array, use those functions instead of hard-coding values such as 0 to 2 or 1 to 10.

A loop based on LBound(MyArray) To UBound(MyArray) automatically processes every valid item in the array, regardless of whether it starts at zero or one. It also keeps working if you add more values later. Today you may have three colors. Tomorrow you add purple and orange. Let VBA figure out the boundaries instead of trusting your memory, which is usually the least reliable variable in the whole project.

When error 9 appears, click Debug. VBA will normally highlight the exact line that failed. That highlighted line is your best clue. Look for the array index, collection index, or key being requested. Then compare that value with the valid range.

If you are working with an array, the Immediate Window is very handy for this. You can inspect the lower bound, upper bound, and current loop counter to see what is happening. If the array's valid range is 0 through 2 but your loop variable is already 3, you have found the smoking gun.

Dynamic arrays can cause a similar problem if they have not been dimensioned yet. Collections can also produce error 9 when you request an item number or key that is not present. The investigation is still basically the same: what did your code ask for, and does that item actually exist?

One thing error 9 is not is a good excuse to slap On Error Resume Next at the top of the procedure and hope for the best. That is like putting duct tape over the check-engine light. The error may disappear, but the bug is still sitting there waiting to cause trouble somewhere else. Find the bad request and fix the logic that generated it.

So the short version is this: error 9 means VBA was asked for an item outside the available range. Check both the lower and upper bounds, remember that Array() normally starts at zero, and use LBound and UBound so your loops adapt automatically when the array changes.

Once you get used to comparing the requested index with the actual valid range, "Subscript out of range" becomes one of the easier VBA errors to diagnose. Watch the embedded video for the full walkthrough and demonstrations in the VBA editor.

Live long and prosper,
RR