Hey,
Just from my experience I would say read these books to get a basic idea of the following:
-types of variables
-Basic syntax, control flow and loops, subs, functions, enumeration etc
-Number system that Excel uses, a lot of subtle problems can be caused by floating point operations
-Object hierarchy (Application.Workbook.Worksheet), a basic idea of what objects are out there (ranges, worksheets, charts, workbooks etc)
After that I think it is best to just try and solve a bunch of simple problems, which the format makes easy and very visual. Generally it is helpful to build up a little function library to supplement the built in VBA functions. Probably a better list out there but here are some of the most basic:
Easy Fun Stuff:
-Make a bunch of cells colourful (not too many), try to make circles, triangles and squares (try to make them move!)
-Zoom your spreadsheet out all the way, square up your cells and use each cell as a "pixel" to do this
This helps with notation, and figuring out how best to address ranges.
Range Functions:
-Last/First Row/Column: The classic one line method LastRow=cells(rows.Count,2).end(xlup).row has a few problems
-Real Used Range: the built in used range is poor, so you can look at why its poor and how to make it better
-Try coding your own Intersect function and compare to the actual function
-Lots of the built in functions do not handle multiple area ranges well, try "fixing them" for instance a function that counts all the rows (or unique rows) in a multiple area range
-Function that returns the "first" (leftmost, topmost) or "last" (bottommost, rightmost) area from a range
Array Functions:
-Unique values from a list: Try this out using the Dictionary object, it is a good introduction
-A function to check if two arrays have the same dimensions
-A function to check if two arrays are equal
-Element wise operations: Allow two arrays to be compared element by element, should input like (Array, Value, Operator ("+,-,*,/")
-Number of elements in an array (include possibility of arrays of arrays or "jagged" arrays)
-Reverse an array "in place"
-Check if an element exists in an array: This has many possible solutions, some more efficient then others given certain types of array, look into "filter"
String Functions:
-Extend mid, left, right and instr to words. Ie have a WMID function that will produce output like WMID("the cat ate the dog",2,1)="cat ate"
-Have a word count function (one way of doing this would be to remove all duplicate spaces then count the spaces and add 1)
-Find the fastest way to delete duplicate spaces (for medium size strings there IS a better way than Application.trim)
Date Function:
-Code the "workday" function yourself
-Sometimes it is useful to get a date from a user so make a little form with some flexible constraints and robust error checking to make sure a valid date for your needs is entered
File Functions:
-Look into the FSO, and use it to code simple open, move, copy, and select functions for files/folders
-Write a function to check if a certain path exists
Regular Expressions:
-Familiarize yourself with some common and simple cases such as patterns for a wide range of telephone numbers (a classic example lots of solutions online)
-Use regex to write a function that removes certain characters from a range of cells
Solve all these sorts of problems, and when you are coding something, and run into a problem you expect to see again, take a minute to separate the problem, code it well and put it your function library. When you learn more, you can revisit these building blocks so to speak and improve them!
