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

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?


--
yoyo2000
------------------------------------------------------------------------
yoyo2000's Profile: http://www.excelforum.com/member.php...o&userid=15489
View this thread: http://www.excelforum.com/showthread...hreadid=471308

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

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"????


--
www.alignment-systems.com


"yoyo2000" wrote:


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?


--
yoyo2000
------------------------------------------------------------------------
yoyo2000's Profile: http://www.excelforum.com/member.php...o&userid=15489
View this thread: http://www.excelforum.com/showthread...hreadid=471308

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

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 |
+-------------------------------------------------------------------+

--
yoyo2000
------------------------------------------------------------------------
yoyo2000's Profile: http://www.excelforum.com/member.php...o&userid=15489
View this thread: http://www.excelforum.com/showthread...hreadid=471308

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

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" wrote in
message ...

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 |
+-------------------------------------------------------------------+

--
yoyo2000
------------------------------------------------------------------------
yoyo2000's Profile:
http://www.excelforum.com/member.php...o&userid=15489
View this thread: http://www.excelforum.com/showthread...hreadid=471308

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

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.


--
yoyo2000
------------------------------------------------------------------------
yoyo2000's Profile: http://www.excelforum.com/member.php...o&userid=15489
View this thread: http://www.excelforum.com/showthread...hreadid=471308

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

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" wrote in
message ...

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.


--
yoyo2000
------------------------------------------------------------------------
yoyo2000's Profile:
http://www.excelforum.com/member.php...o&userid=15489
View this thread: http://www.excelforum.com/showthread...hreadid=471308

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

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


--
yoyo2000
------------------------------------------------------------------------
yoyo2000's Profile: http://www.excelforum.com/member.php...o&userid=15489
View this thread: http://www.excelforum.com/showthread...hreadid=471308