Excel VBA commands

In this post, I will simply list a bunch of useful single (approximately) line VBA for Excel commands.

'Minimize the Excel Application.
'This does not work when a userform is open.
Application.WindowState = xlMinimized


'Get the active cell column as a number
Dim dblCurrentColumn As Double
dblCurrentColumn = Application.ActiveCell.Column


'Get the active cell row as a number
Dim dblCurrentRow As Double
dblCurrentRow = Application.ActiveCell.Column


'Declare an array. This example declares a 6 x 3 array.
Dim vntArray(0 To 5, 0 To 2) As Variant


'The general form is to declare an N x M array as:
Dim vntArray(0 To N-1, 0 to M-1).

How to create a multicolumn list box

This is the first in a series of code explanations I will post on this website.

I have recently had to create a multi-column list box so that I can display a list of customers along with some of their other attributes. To tackle this problem I created arrays that I used to then populate the list box:

'First, I declare the array.
'This one will give me 2 rows and 3 columns:
Dim vntArray(0 To 1, 0 To 2) As Variant

'Next, I populate the array with the customers' info
vntArray(0, 0) = "Smith" 'last name
vntArray(0, 1) = "Bill" 'first name
vntArray(0, 2) = "05/23/1978 'Birth date

vntArray(1, 0) = "Bonds"
vntArray(1, 1) = "Barry"
vntArray(1, 2) = "07/24/1964"

'Now to populate the list box with this array:
With Me.listBox
.ColumnCount = 3
.List = vntArray
End With

This adds the array to the list box and will treat each row as a unit so you essentially have a customer in each row.

Note: you could use a loop to read through an excel worksheet and populate this array automatically (hint: you need to read how big you will require the array to be before populating it), I will explain this in a future post or sooner if I get any requests.

Excel VBA code

I am working on developing code for various management science applications. I have a Master's degree from Stanford University in Management Science and Engineering and enjoy programming. I am using this site to hold all of the knowledge that I am gaining through creating programs in VBA for Excel. So often I find explanations difficult to understand or track down, so I hope this serves as another possibility for those who are searching for answers!

If there is any management science program you would like me to develop, please leave a comment on this blog and I will get back to you to get the details. Right now the plan is for me to charge $10-20 for a new request (possibly more for very complicated projects) and then the program will be available to everyone for $1-5 (again, possibly more depending on the complexity) after it has already been created. I will post links to purchase the code on the right side of the page, just click and order and I will get back to you with the code. Thanks and good luck!