u/Michaelscarn69- - Your post was submitted successfully. Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread. Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. Include your Excel version and all other relevant information Failing to follow these steps may result in your post being removed without warning. I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns. More on reddit.com

Awesome, thanks! More on reddit.com

I am bracing for the onslaught of down votes I will get for this...

I applaud the effort and intentions, but I see many people make "Cheat Sheets" that are really just excessive re-documentation.

To me, a "Cheat Sheet" (CS from now on) is for commands you do not use as often, have trouble remembering and/or as a consolidated resource (more on this point).

I for example would never add Dim wb As Workbook to a CS as its something I do in practically every macro I write. I don't need a reminder...I couldn't forget how to do this if I wanted to.

Something like (checking if a file exists) I get:

If Dir(“C:\Book1.xlsx”) = “” Then
 MsgBox “File does not exist.”
EndIf

It may be something one does not do often and may have trouble remembering the exact syntax of.

However common methods/properties of common objects that also get displayed to you via intelli-type as you code seem like overkill to document in a CS.

What I think would be more helpful for the majority of people with more than a very basic understanding of VBA is code snippets or less common stuff on a CS.

Ex: In the same space you write each method/property for a collection (maybe less), you could write a complete snippet with all the same parts in it. Some of the lines you don't even need to be commented on. IE: it would be obvious that coll.Count returns the count of elements in the collection.

Ex:

Dim coll As New Collection
Dim cell As Range

For each cell in Range("A1:A5")
	coll.add cell.value2 ' ", Before:=1" or ", After:=1" to place before/after other element
Next

Debug.Print coll.Count
Debug.Print coll (3) 'value by index

coll.Remove (3)

Dim item As Variant
For Each item in coll
	msgbox item
Next

Set coll = New Collection 'remove all items'

13 lines (excluding blank lines between code vs 19 lines in the CS). To me, conveys the same overall info while also showing it in use.

The last case I see a CS being useful for is combining info from many sources to a single source, which this currently does. But once a CS goes beyond a single page, maybe 2 its usefulness really declines. At that point links to resources or something like OneNote becomes a better tool to organize code snippets and documentation.

Personally I would remove all the "obvious" commands from the CS. Consolidate workbook/worksheet into one category for example...ranges, cells, columns and rows into another.

Ex: keep stuff like getting last row, last column, For each loop on range collection/rows/columns. For WB/WS, keep very hidden, protect leaving VBA access, loping WB/WS's, check exists and maybe copy closed. Everything else is so basic it is just taking up space being included.

You also have a lot of very similar entries, for example with Workbook; add to variable, open to variable, set to variable. If you know how to set an object type variable, know how to open a workbook and know how to create a new workbook these can be deduced without documentation.

There are also a bunch of entries for activate. Just like select, its almost never necessary to activate an item in VBA to properly act upon it.

What I would consider including as other categories is ListObjects, Charts, Pivot Tables. I would also consider maybe some snippets for; autofilter on a range/listobject, advanced filter, etc.

Just my take on it. If others find it helpful then keep up the good work.

More on reddit.com

In that 'Master Workbook' I've posted here before, I have a full listing of all formulas with a description and their syntax. Also have loads of other examples of how to use formulas/shortcuts/etc.

I update it pretty regularly as well.

More on reddit.com