VBA resource for Python programmer

Question

I've written a lot of code in Python, and I'm very used to the syntax, object structure, and so forth of Python because of it.

What's the best online guide or resource site to give me the basics, as well as lookup equivalent functions/features in VBA versus Python.

For example, I'm having trouble equating a simple List in Python with something in VBA... or a dictionary, and so forth.

"VBA for Python Dummies" is what I need. :)

Answer 1

This tutorial isn't 'for python programmers' but I thinkit's a pretty good vba resource:

http://www.vbtutor.net/VBA/vba_tutorial.html

This site goes over a real-world example using lists:

http://www.ozgrid.com/VBA/count-of-list.htm

Answer 2

Probably not exactly what you are looking for but this is a decent VBA site if you have some programming background. It's not a list of this = that but more of a problem/solution

http://www.mvps.org/access/toc.htm

Answer 3

VBA as in what was implemented as part of Office 2000, 2003 and VB6 have been deprecated in favor of .Net technologies. Unless you are maintaining old code stick to python or maybe even go with IronPython for .Net. If you go IronPython, you may have to write some C#/VB.Net helper classes here and there when working with various COM objects such as ones in Office but otherwise it is supposed to be pretty functional and nice. Just about all of the Python goodness is over in IronPython. If you are just doing some COM scripting take a look at what ActiveState puts out. I've used it in the past to do some COM work. Specifically using Python as an Active Scripting language (classic ASP).

Answer 4

"I'm having trouble equating a simple List in Python with something in VBA..."

This isn't the best way to learn the language. In a way, you're giving up large pieces of Python because there isn't something like it in VBA.

If there's nothing like a Python list in VBA, then -- well -- it's something new. And new would be the significant value in parts of Python.

The first parts of the Python Built-in Types may not map well to VBA. That makes learning appear daunting. But limiting yourself to just things that appear in VBA tends to prevent learning.

Answer 5

I think the equivalent of lists would be arrays in terms of common usage. Where it is common to use a list in Python you would normally use an array in VB. However, VB arrays are very inflexible compared to Python lists and are more like arrays in C.

' An array with 3 elements
'' The number inside the brackets represents the upper bound index
'' ie. the last index you can access
'' So a(2) means you can access a(0), a(1), and a(2) '

Dim a(2) As String
a(0) = "a"
a(1) = "b"
a(2) = "c"

Dim i As Integer
For i = 0 To UBound(a)
    MsgBox a(i)
Next

Note that arrays in VB cannot be resized if you declare the initial number of elements.

' Declare a "dynamic" array '

Dim a() As Variant

' Set the array size to 3 elements '

ReDim a(2)
a(0) = 1
a(1) = 2

' Set the array size to 2 elements
'' If you dont use Preserve then you will lose
'' the existing data in the array '

ReDim Preserve a(1)

You will also come across various collections in VB. eg. http://devguru.com/technologies/vbscript/14045.asp

Dictionaries in VB can be created like this:

Set cars = CreateObject("Scripting.Dictionary")
cars.Add "a", "Alvis"
cars.Add "b", "Buick"
cars.Add "c", "Cadillac"

http://devguru.com/technologies/vbscript/13992.asp

Answer 6

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).

Answer 7

This may sound weird, but since I learned data structures in C++, I had a really hard time figuring out how to create them without pointers. There's something about VB6/VBA that makes them feel unnatural to me. Anyway, I came across this section of MSDN that has several data structure examples written in VBA. I found it useful.

Creating Dynamic Data Structures Using Class Modules

Answer 8

While I'm not a Python programmer, you might be able to run VSTO with Iron Python and Visual Studio. At least that way, you won't have to learn VBA syntax.