I have written a lot of code in Python, and I am very used to the syntax, object structure, and so forth of Python because of it.
What is the best online guide or resource site to provide me with the basics, as well as a comparison or lookup guide with equivalent functions/features in VBA versus Python.
For example, I am having trouble equating a simple List in Python to VBA code. I am also have issues with data structures, such as dictionaries, and so forth.
What resources or tutorials are available that will provide me with a guide to porting python functionality to VBA, or just adapting to the VBA syntax from a strong OOP language background?
Answer
VBA is quite different from Python, so you should read at least the "Microsoft Visual Basic Help" as provided by the application you are going to use (Excel, Access…).
Generally speaking, VBA has the equivalent of Python modules; they're called "Libraries", and they are not as easy to create as Python modules. I mention them because Libraries will provide you with higher-level types that you can use.
As a start-up nudge, there are two types that can be substituted for list and dict.
list
VBA has the type Collection. It's available by default (it's in the library VBA). So you just do a
dim alist as New Collection
and from then on, you can use its methods/properties:
.Add(item)( list.append(item) ),.Count( len(list) ),.Item(i)( list[i] ) and.Remove(i)( del list[i] ). Very primitive, but it's there.
You can also use the VBA Array type, which like python arrays are lists of same-type items, and unlike python arrays, you need to do ReDim to change their size (i.e. you can't just append and remove items)
dict
To have a dictionary-like object, you should add the Scripting library to your VBA project¹. Afterwards, you can
Dim adict As New Dictionary
and then use its properties/methods:
.Add(key, item)( dict[key] = item ),.Exists(key)( dict.has_key[key] ),.Items()( dict.values() ),.Keys()( dict.keys() ),
and others which you will find in the Object Browser².
¹ Open VBA editor (Alt+F11). Go to Tools→References, and check the "Microsoft Scripting Runtime" in the list.
² To see the Object Browser, in VBA editor press F2 (or View→Object Browser).