VBA code skips function without error

Question

I'm working on a code which generates a covariance matrix for some data, specifically 2 columns (i.e a 2x2 matrix). What's happening is that the code worked before perfectly and now it doesn't without any significant change (just made a function with it). The code runs, shows the MsgBox and when it has to calculate the matrix it just goes to the end skipping all the lines.

Code:

Function GetBaseRate(ranges() As Variant, Days() As Variant) As Object
  ' Get Data from Data Base
  ' Use this array to render the bucket titles
  Dim Covariance As Object
  Dim Cov As Variant
  Set Covariance = CreateObject("Scripting.Dictionary")
  Set sh = ThisWorkbook.Worksheets("AUX")
  ' Get Covariance for each range
  limit = UBound(ranges)
  MsgBox limit
  For i = 0 To limit
    MsgBox ranges(i) & " - " & Days(i)
    Next i
    For i = 0 To limit
        sh.UsedRange.ClearContents
        ' query for each day
        MsgBox ranges(i) & " - " & Days(i)
        Query = "SELECT TASA FROM [NVSSQLBI].[MESA].[dbo].[CURVA_BASE_CLP_ON_FECHA] WHERE DIAS ='" & Days(i) & "' AND FECHA > '" & DateAdd("yyyy", -5, Date) & "' ORDER BY FECHA DESC"
        Query2 = "SELECT TASA FROM [NVSSQLBI].[MESA].[dbo].[CURVA_BASE_CLF_ON_FECHA] WHERE DIAS ='" & Days(i) & "' AND FECHA > '" & DateAdd("yyyy", -5, Date) & "' ORDER BY FECHA DESC"
        aux = BDconexion2(Query)
        aux2 = BDconexion2(Query2)
        Count = 1
        ' CLP
        For Each element In aux
            sh.Cells(Count, 1).Value = element
            Count = Count + 1
        Next element
        Count = 1
        ' UF
        For Each element In aux2
            sh.Cells(Count, 2).Value = element
            Count = Count + 1
        Next element
        ' Generate Cov
         Cov = VarCov(Range("A1:B" & Count - 1))
        'Debug.Print Cov(0, 0) & " - "
         Covariance.Add CStr(ranges(i)), Cov
    Next i
    GetBaseRate = Covariance
End Function

It make a sql query to a database and puts the data into a Worksheet for later calculation of it's covariance matrix. The code which calculates the matrix is this one:

Function VarCov(rng As Range) As Variant
' Returns covariance matrix
  Dim i As Integer
  Dim j As Integer
  Dim colnum As Integer
  Dim Matrix() As Double

  colnum = rng.columns.Count
  ReDim Matrix(colnum, colnum)

  For i = 1 To colnum
      For j = 1 To colnum
          Matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.columns(i), rng.columns(j))
      Next j
  Next i
  VarCov = Matrix
End Function

I have no idea why It does not work, I'm working with the previous version and It doesn't fail at all. After debugging, the fail point is located at

 Matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.columns(i), rng.columns(j))

line which used to work perfectly, and now It does not throw any error just goes to the line after the function (a MsgBox saying "Done")

J. Garth · Accepted Answer · 2017-01-20 16:26:43Z

1

Try adding the sheet object to the range that you are passing to the VarCov function. i.e. - try changing

Cov = VarCov(Range("A1:B" & Count - 1))

to

Cov = VarCov(sh.Range("A1:B" & Count - 1))

assuming that sh is the worksheet on which the range is located.

answered Jan 20, 2017 at 16:26

J. Garth

8037 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vityata · Accepted Answer · 2017-01-20 15:58:53Z

0

Can you write this and see what you are getting in the immediate window?

  debug.print colnum
  For i = 1 To colnum
      For j = 1 To colnum
          Matrix(i - 1, j - 1) = Application.WorksheetFunction.Covar(rng.columns(i), rng.columns(j))
          debug.print i
          debug.print j
          debug.print Matrix(i - 1, j - 1)
          debug.print "---------"
      Next j
  Next i

answered Jan 20, 2017 at 15:58

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

3 Comments

Nano Aguayo Over a year ago

It doesn't print anything, I did debug it using MsgBox or Debug.Print that's the weird thing. No error is displayed

Vityata Over a year ago

Probably then colnum is 1?

Nano Aguayo Over a year ago

It is 2, every iteration

Collectives™ on Stack Overflow

VBA code skips function without error

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related