w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
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

  1. Proper indenting: Every time you use a programming structure the encloses another block of code - such as For, If, 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
            Exit For
        End If
    Next k
  2. 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, ...)
  3. 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.
  4. 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.
  5. AFAIK your first 25 rows "only" assign two ranges to an array and check if these are the same size. Using the syntax x = 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 non-blank row!
    Also, if you want to assign an array to a range, it works the other way round, too: StartRange.Resize(NumberOfRows) = x.
  6. Hardcoding 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 less cluttered
  7. "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
  8. No need to use Call Sub(x, y) - Sub x, y is equivalent to it in VBA
  9. 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

Option Explicit

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
scoped variables

Public Sub SubDoingSomething() 'Use a name that tells the reader what the
sub does


    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

End Sub

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")
    End If

    mVec1 = Range("StartVector1").Resize(count1).Cells
    mVec2 = Range("StartVector2").Resize(count2).Cells

End Sub

Private Function GetRowLenght(rng As Range)
    If rng.Offset(1) = "" Then
        GetRowLength = 1
        GetRowLength = rng.End(xlDown).Row - rng.Row + 1
    End If
End Function

© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.