change reference for 'visual basic for application' in VBA editor?

[yoyo2000]

I can't run a vba script in Excel because of the refering dll is too
old,I check it in vba editor -> tools -> references,and found the one
I'm currently using is in 'C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6',I want to change the reference to msvbvm60.dll in
C:\WINNT\System32,but it said that current reference is in use and I
can't change the reference,could any guy here give me a help?

 

[Guest]

The reference to :\Program Files\Common Files\Microsoft Shared\VBA\VBA6
is not a reference to msvbvm60.dll - it's a reference to the VBA library.

You can see the references yourself with this code

Sub x()
Dim a As Reference

For Each a In ThisWorkbook.VBProject.References
Debug.Print a.FullPath
Next

End Sub

This is the output:

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
C:\Program Files\Microsoft\Office_02\Office10\EXCEL.EXE
C:\WINNT\System32\stdole2.tlb
C:\Program Files\Common Files\Microsoft Shared\office10\mso.dll
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB


So, what makes you think the reference is "old"????

 

[yoyo2000]

Oh,in fact,I got an vba script,it couldn't be run correctly,a error msg:

run time error '1004'
unable to get Correl property of the worksheetfunction class

when I clicked DEBUG button,it jumped to the error line(in the second
snapshot).

I got an suggestion that the cause of this error was the 'visual basic
for application' was too old,and there were several these 'visual basic
for application' in vba editor-> tools -> references,I could try to pick
one to make that vba script run favoringly.

Any suggestion please?
Thanks in advance


+-------------------------------------------------------------------+
|Filename: screenshot4343.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=3857 |
+-------------------------------------------------------------------+

 

[NickHK]

yoyo,
Not that I use it but Correl is part of Excel not VBA/VB.
It works fine for me with XL2K.

Does it work on the work sheet ?
Do you have References marked "MISSING" ?

NickHK

 

[yoyo2000]

Actually,correl function could be run properly in my excel sheet
mode,but that script couldn't,could you please help to have a look?

The excel file including that script is in attachment,each time I
opened it,press Ctrl+Shift+S,it runs favoringly on the former half,but
a run time error '1004' comes then.

 

[NickHK]

yoyo,
Most people will not download unknown workbooks from the net, for obvious
reasons.
You need to post a stripped down version of the section of code that you
need help with.

NickHK

 

[yoyo2000]

Well，below is the sub procedure including that error function

Sub CorrelMatrix()
'Will calculate either Correlation matrix or covariance matrix
depending
' on switch Correl_NOT_Covar
'Will write output to "Correlations" WorkSheet
'Init_Variables

'Check if RFR = 0 ig 20041221
Dim bRFRzero As Boolean
bRFRzero = False
If (Sheets("Settings").Cells(10, 2) <= 0) And
(Sheets("Settings").Cells(12, 2) <= 0) Then bRFRzero = True
' Dim iRFR As Integer (made PUBLIC saybe others can useo that descr
stats and
iRFR = 1
If bRFRzero Then iRFR = 2

IW.Activate
Dim Correl_NOT_Covar As Boolean
Correl_NOT_Covar = True 'True 'False
Dim INM As Variant, C_Matrix As Variant, r_significance As Variant,
c1() As Variant, c2() As Variant
Dim SecNames As Variant 'INM is whole input matrix

Dim Rtemp As Range
Dim i As Integer, j As Integer, k As Integer, ur As Integer, UC As
Integer
Dim lr As Integer, LC As Integer
Dim c As Double, r As Double, temp As Double
Dim HomoskedasticitySignificance()


Set Rtemp = Range(Cells(FirstRow, 3), Cells(LastRow, LastColumn +
1))
INM = Rtemp

'Find Matrix INM indices
lr = LBound(INM, 1): LC = LBound(INM, 2): ur = UBound(INM, 1): UC =
UBound(INM, 2)
ReDim Preserve INM(1 To ur, 1 To UC)
ReDim r_significance(1 To UC, 1 To UC)
ReDim C_Matrix(1 To UC, 1 To UC)
ReDim HomoskedasticitySignificance(1 To UC, 1 To UC)

'Will calculate all correlations between c1 and c2
ReDim c1(1 To ur) ' ur = mumber of returns for one security
ReDim c2(1 To ur) ' ur = number of retutns for one security

'Calculate correlations or covariances and significance
For i = iRFR To UC 'uc = number of securities
For k = 1 To ur 'Fill the 1 col array of sec i
c1(k) = INM(k, i) 'First vertor
Next k
For j = i To UC 'fill 2nd vector
For k = 1 To ur 'Fill the 1 col array of sec i
c2(k) = INM(k, j) '2nd vector
Next k
r = Application.WorksheetFunction.Correl(c1, c2)
't statistic for correlation:
If i <> j Then
If r <= 0.999999999 Then
r_significance(i, j) = Abs(r * ((ur - 2) / (1 - r ^
2)) ^ 0.5)
Else
r_significance(i, j) = 10
End If
Else
r_significance(i, j) = 10 'very large t
End If
'Probability:
r_significance(i, j) =
Application.WorksheetFunction.TDist(r_significance(i, j), ur - 2, 2)
r_significance(j, i) = r_significance(i, j)
If Correl_NOT_Covar Then
C_Matrix(i, j) = r
Else
C_Matrix(i, j) =
Application.WorksheetFunction.Covar(c1, c2)
End If

On Error Resume Next
If i <> j Then
HomoskedasticitySignificance(i, j) =
S_LMTestHeteroscedasticityVariants(c1(), c2())
HomoskedasticitySignificance(j, i) =
S_LMTestHeteroscedasticityVariants(c2(), c1())
Else
HomoskedasticitySignificance(i, j) = 1
End If
'If Err.Number <> 0 Then MsgBox "CorrelMatrix Error Number:
" & Err.Number & " " & Error(Err.Number)


Next j
Next i

CW.Activate
co = 5 ' sets column offset public var
ro = 5 ' sets row offset public var
If Correl_NOT_Covar Then
CW.Cells(ro, co - 1) = "CORRELATION Matrix:"


Else
CW.Cells(ro, co - 1) = "VARIANCE - COVARIANCE Matrix of r"
End If
CW.Rows(ro).Font.Bold = True
CW.Rows(ro).HorizontalAlignment = xlRight
'CW.Cells(ro, co - 1).Font.Bold = True

' CW.Cells(ro - 3, co - 1 + UC + 2 + 1) = "SIGNIFICANCE OF LM TEST
FOR CONSTANT VARIANCE"
' CW.Cells(ro - 3, co - 1 + UC + 2 + 1).Font.Bold = True


For i = 1 To UC
'Titles and labels

'These are Chartscript names:
If i = 1 Then
ShortSymbols(1) = "RFR"
CW.Cells(ro, i + co) = "RFR"

ElseIf i = 2 Then
ShortSymbols(2) = "BENCH"
CW.Cells(ro, i + co) = "BENCH"

ElseIf i = UC Then
ShortSymbols(UC) = "PORT"
CW.Cells(ro, i + co) = "PORT"
Else
If i >= 10 Then
ShortSymbols(i) = "SYS_" & i
CW.Cells(ro, i + co) = ShortSymbols(i)
Else
ShortSymbols(i) = "SYS_0" & i
CW.Cells(ro, i + co) = ShortSymbols(i)
End If
End If


CW.Cells(ro - 1, i + co) = Symbols(i) 'Symbols() is Public
variable set by Initvariables
CW.Cells(ro - 1, i + co).HorizontalAlignment = xlRight

CW.Cells(ro - 2, i + co) = SymbolLists(i)
CW.Cells(ro - 2, i + co).HorizontalAlignment = xlRight
'CW.Cells(ro - 1, i + co + UC + 3) = Symbols(i) 'Titles fot
Homoscedasticity significance

'CW.Cells(ro - 1, i + co + UC + 3).HorizontalAlignment =
xlRight

CW.Cells(i + ro, co - 1) = Symbols(i)
CW.Cells(i + ro, co - 1).HorizontalAlignment = xlRight

'CW.Cells(i + ro, co - 1 + UC + 3) = Symbols(i)
'CW.Cells(i + ro, co - 1 + UC + 3).HorizontalAlignment =
xlRight

'Numbers
For j = i To UC
If (r_significance(i, j) < 0.01) And (i <> j) Then
CW.Cells(i + ro, j + co).Font.Bold = True
CW.Cells(j + ro, i + co).Font.Bold = True
If C_Matrix(i, j) > 0 Then
CW.Cells(i + ro, j + co).Font.ColorIndex = ar_red
CW.Cells(j + ro, i + co).Font.ColorIndex = ar_red
Else
CW.Cells(i + ro, j + co).Font.ColorIndex = ar_blue
CW.Cells(j + ro, i + co).Font.ColorIndex = ar_blue
End If
Else
CW.Cells(i + ro, j + co).Font.Bold = False
CW.Cells(j + ro, i + co).Font.Bold = False
CW.Cells(i + ro, j + co).Font.ColorIndex = vbBlack
CW.Cells(j + ro, i + co).Font.ColorIndex = vbBlack
End If

CW.Cells(i + ro, j + co).NumberFormat = "0.0000"
CW.Cells(j + ro, i + co).NumberFormat = "0.0000"
CW.Cells(i + ro, j + co) = C_Matrix(i, j)
CW.Cells(j + ro, i + co) = C_Matrix(i, j)

' CW.Cells(i + ro, j + co + UC + 3).NumberFormat = "0.0000"
' CW.Cells(j + ro, i + co + UC + 3).NumberFormat = "0.0000"
' temp = HomoskedasticitySignificance(i, j)
' CW.Cells(j + ro, i + co + UC + 3) = temp
' CW.Cells(i + ro, j + co + UC + 3) = temp
If HomoskedasticitySignificance(i, j) < 0.1 Then
' CW.Cells(i + ro, j + co + UC + 3).Font.Bold = True
' CW.Cells(j + ro, i + co + UC + 3).Font.Bold = True
' CW.Cells(i + ro, j + co + UC + 3).Font.ColorIndex =
ar_red
' CW.Cells(j + ro, i + co + UC + 3).Font.ColorIndex =
ar_red
'Apply pattern to Correlation Matrix
' CW.Cells(i + ro, j + co).Interior.Pattern = xlGray8
' CW.Cells(j + ro, i + co).Interior.Pattern = xlGray8
' CW.Cells(i + ro, j + co).Interior.ColorIndex = 38
' CW.Cells(j + ro, i + co).Interior.ColorIndex = 38
' If C_Matrix(i, j) > 0 Then .Interior.Pattern =
xlGray16
' CW.Cells(i + ro, j + co + UC + 3).Font.ColorIndex
= ar_red
' CW.Cells(j + ro, i + co + UC + 3).Font.ColorIndex
= ar_red
' Else
' CW.Cells(i + ro, j + co).Font.ColorIndex =
ar_blue
' CW.Cells(j + ro, i + co).Font.ColorIndex =
ar_blue
' End If
Else
' CW.Cells(i + ro, j + co + UC + 3).Font.Bold = False
' CW.Cells(j + ro, i + co + UC + 3).Font.Bold = False
' CW.Cells(i + ro, j + co + UC + 3).Font.ColorIndex =
vbBlack
' CW.Cells(j + ro, i + co + UC + 3).Font.ColorIndex =
vbBlack
End If
Next j
Next i


W_LastCorrelSheetRow = j + ro 'OUTPUT public variable -- 1 row
after last row written

End Sub 'CorrelMatrix