Return to VBA Code Examples

VBA Matrix – Create & More

This tutorial will show you how to  create a matrix using an Array in VBA.

A matrix is a rectangular array of numbers with the same amount of Rows and Columns. You can create a matrix in VBA by populating Array variables and using VBA Loops.

Creating a Matrix in VBA

Running the procedure above will return a simple matrix to Range(“A1:C3”) to your worksheet.

vba matrix simple

Convert Single Row Vector into a Matrix

Consider the column of numbers below. You may at some stage wish to convert a column of numbers to a matrix.

vba matrix convert

Using the function below, we can create a matrix from the list of numbers.

We can create the matrix in our Excel sheet, by calling the function above.

vba vector to matrix

Convert a Matrix into a Single Row Vector

Alternatively, you may want to convert a Matrix into a Single row. Consider the Matrix below.

vba-matrix-2dim

Using the function below, we can convert this into a single dimension array.

We can now call this function using the procedure below.

This procedure will return the following result.

vba matrix to vector

Using the WorksheetFunction.MMULT to create a Matrix Array

Consider the following spreadsheet.

vba-matrix-empty-interest

We can use the procedure below to calculate the interest amount for the grid above based on the interest rate and the amount the person wants to borrow.

vba matrix filled

You will notice in the sheet above, that procedure above populates the cells with values rather than formulas – see C4 in the above graphic – it has the value 200 in it, not a formula.  Using the WorksheetFunction method always returns a static value to the worksheet and not a formula.  This means that should the interest rate or loan amount change, the corresponding values in the filled in matrix WILL NOT change.

Instead of using the WorksheetFunction.MMULT, you can use VBA to apply the MMULT Function to a cell using the FormulaArray method.

Notice that now when the spreadsheet is populated, a formula is used in the cells.

vba matrix formula array

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!