|I am getting the error "Type mismatch: array or user-defined type expected" in this VBA code|
This answer will probably not solve your issue (see my comment) - but
let me nonetheless give you a few best practices that might make
programming in VBA easier and maybe prevent such errors in the first place
- in your next project.
Try to incorporate the following into your programming
- Proper indenting: Every time you use a programming structure the
encloses another block of code - such as
While, indent the enclosed code block one level further. E.g.
your first few lines of code should look like
For k = 1 To 100
If Worksheets("Sheet1").Range("A2").Cells(k, 1).Value <> "" Then
n = n + 1 'counts the number of data points
- You are already using
Option Explicit, which is great.
However, you should also properly
Dim each variable in the
procedure/function calls - e.g.
Sub Fitted_Data(yf as Double,
- You're using a total of 12 variables in your main procedure. This is a
very strong indicator, that your routine is doing too much! Better break it
up in to small sub routines and maybe use a few module wide variables - see
the example below.
- The variable names are absolutely meaningless - which makes it hard to
debug for you - and almost impossible for outsiders to understand what your
code is doing.
- AFAIK your first 25 rows "only" assign two ranges to an array and check
if these are the same size. Using the syntax
StartRange.Resize(NumberOfRows).Cells you can achieve this with much
less code - and it executes much faster.
Same thing goes finding the first blank row - instead of looping, use
StartRange.End(xlDown) - this will return you the last
Also, if you want to assign an array to a range, it works the other way
StartRange.Resize(NumberOfRows) = x.
Worksheets("Sheet1").Range("A2") will lead to
problems when the user changes the worksheet structure, e.g. rename the
sheet or insert rows/columns. Better assign the cells A2 and B2 names, e.g.
StartVector1 and then access them with
Range("StartVector1"). Much more robust - and your code is
- "Don't repeat yourself" (DRY). If you see yourself doing the
same code twice, make it a separate procedure - e.g your code to count the
number of data points
- No need to use
Call Sub(x, y) -
Sub x, y is
equivalent to it in VBA
- Excel function can also be used in VBA. This is especially handy for
matrix function. E.g. to transpose an array, you could use this code:
transposedX = worksheetFunctions.Transpose(x)
Here's the code structure with the first few
Private mVec1() As Double 'Better give a better name representing the
target content of variable
Private mVec2() As Double 'I use m as a prefix to indicate module wide
Public Sub SubDoingSomething() 'Use a name that tells the reader what the
BuildZP Z, n, m 'use proper variable names here
NLRegress Z, y, a, n, m 'and maybe use some more module wide variables
that you don't need to pass
MultiplyMatrixByVector Z, a, yf
Private Sub LoadVectors()
Dim count1 As Long, count2 As Long
count1 = GetRowLength(Range("StartVector1"))
count2 = GetRowLength(Range("StartVector2"))
If count1 <> count2 Then
MsgBox ("Unequal number of x and y values")
mVec1 = Range("StartVector1").Resize(count1).Cells
mVec2 = Range("StartVector2").Resize(count2).Cells
Private Function GetRowLenght(rng As Range)
If rng.Offset(1) = "" Then
GetRowLength = 1
GetRowLength = rng.End(xlDown).Row - rng.Row + 1