通过 Excel 使用 VBA 计算协方差

作为一名执著的 C# 程序员,当一位学经济的老友从国外找来,让我帮他写完 VBA 作业的时候,一开始,其实我是拒绝的。
各路VB大神,不要嘲笑在下啊,我可是足足用了一个小时才在 Excel 里到了编写 VB 代码的地方 (默认 Office 2013 的那个开发者选项是隐藏的…)
我把一夜写的代码在这里Mark一下,万一以后再有类似的VB任务也不至于抓瞎,哈哈,我是一晚上没睡觉活活憋出来的,以下代码在Excel里计算协方差。
不过话说话来,感觉各种语言都是相同的,学会一种,换成另一种也不会太恐怖。

下载这个 Excel VBA 文件:Spreadsheeting assi

Private Sub btnCancel_Click()
    Unload Me
End Sub

Private Sub btnHelp_Click()
    Dim ET As InternetExplorer
    Set ET = New InternetExplorer
    ET.Visible = True
    ET.Navigate "http://en.wikipedia.org/wiki/Covariance"    'The Help Button URL
    Set ET = Nothing
End Sub

Private Sub btnOk_Click()
    Dim isIncludeTitle As Boolean
    isIncludeTitle = LablesInFirstRow.Value
    Dim isGroupByRow As Boolean
    isGroupByRow = obRows.Value
    Dim currentWorksheet As Worksheet
    Set currentWorksheet = ActiveSheet
    Dim inputRanges As String
    inputRanges = Me.InputRefEdit.Value
    
    'Debug.Print (inputRanges)
        
    Dim range As range
    Set range = currentWorksheet.range(inputRanges)
    
    Dim titleCollection As Variant
    
    Dim dataRange As range
    Dim dataNumber As Integer
        
    If isGroupByRow Then
        
        Set dataRange = range.Rows
        dataNumber = dataRange.Rows.Count
        If dataRange.Count < 1 Then
            ActivateWindows "Excel"
            MsgBox ("Please ensure data selection is current!")
            End
        End If
        
        If isIncludeTitle Then
            titleCollection = dataRange.Columns(1)
            Set dataRange = dataRange.Offset(0, 1).Resize(dataRange.Columns.Count - 1, dataRange.Columns.Count)
        End If
        
    Else
    
        Set dataRange = range.Columns
        dataNumber = dataRange.Columns.Count
        If dataRange.Count < 1 Then
            ActivateWindows "Excel"
            MsgBox ("Please ensure data selection is current!")
            End
        End If
        
        If isIncludeTitle Then
            titleCollection = dataRange.Rows(1)
            Set dataRange = dataRange.Offset(1, 0).Resize(dataRange.Rows.Count - 1, dataRange.Rows.Count)
        End If
                
    End If
    
    
    Dim outputRange As range
    If Me.obOutputRange.Value Then
        Set outputRange = currentWorksheet.range(Me.OutputRangeRefEdit.Value)
    End If
        
        
        
    'Start calculate the Covariance
       
    Dim array1 As Variant
    array1 = dataRange(1)
    Dim array2 As Variant
    array2 = dataRange(2)
    Dim result As Double
    result = Application.WorksheetFunction.Covariance_S(array1, array2)
    
    outputRange.Value = result
        
        
    If isIncludeTitle Then
        dataNumber = dataNumber + 1
    End If
    
    ReDim resultArray(1 To dataNumber, 1 To dataNumber) As Variant
    For i = 1 To dataNumber
        For j = 1 To dataNumber
            If isIncludeTitle And i = 1 And j = 1 Then
                resultArray(i, j) = ""
            ElseIf isIncludeTitle And i = 1 Then
                If isGroupByRow Then
                    resultArray(i, j) = titleCollection(j - 1, 1)
                Else
                    resultArray(i, j) = titleCollection(1, j - 1)
                End If
            ElseIf isIncludeTitle And j = 1 Then
                If isGroupByRow Then
                    resultArray(i, j) = titleCollection(i - 1, 1)
                Else
                    resultArray(i, j) = titleCollection(1, i - 1)
                End If
            Else
                resultArray(i, j) = Application.WorksheetFunction.Covariance_S(dataRange(j - 1), dataRange(i - 1))
            End If
        Next j
    Next i
            

    
    'Out put the result
    
    outputRange.Value = resultArray


    Unload Me
End Sub

一些速记:
VBA中,当前窗口(this)用Me来表示,
我们可以用 Unload 来关闭 VBA 窗口 Unload Me

RefEdit 控件的值,是String,表示所选定Excel的范围
Dim inputRanges As String
inputRanges = Me.InputRefEdit.Value

可以通过 Application 的 ActiveSheet 属性来获取当前 WorkSheet,
Dim currentWorksheet As Worksheet
Set currentWorksheet = ActiveSheet
使用Worksheet对象的Range方法,可以获取表格中选定范围的值

VB语法不区分大小写
声明对象用Dim As
赋值如果是普通的对象用Let = 或者 省略
如果是为复杂类型赋值,则用set =

在立即窗口会显示通过 Print 或 Debug.Print 所输出的值

我们可以通过 VarType() 来获得一个对象的类型,这个返回的是 Integer 类型的对象。
typeNumber = VarType(Me.InputRefEdit.Value)
Debug.Print (typeNumber)

这个整数返回值所对应的类型,可以参见MSDN:
https://msdn.microsoft.com/en-us/library/aa263402(v=vs.60).aspx

常数 值 描述
vbEmpty 0 Empty(未初始化)
vbNull 1 Null(无有效数据)
vbInteger 2 整数
vbLong 3 长整数
vbSingle 4 单精度浮点数
vbDouble 5 双精度浮点数
vbCurrency 6 货币值
vbDate 7 日期
vbString 8 字符串
vbObject 9 对象
vbError 10 错误值
vbBoolean 11 Boolean 值
vbVariant 12 Variant(只与变体中的数组一起使用)
vbDataObject 13 数据访问对象
vbDecimal 14 十进制值
vbByte 17 位值
vbUserDefinedType 36 包含用户定义类型的变量
vbArray 8192 数组

VBA 采用 End 结束子程序
VBA 声明数组: Dim arrayName () as Integer

Range和Array是可以直接转换的:
TargetRange.Value(sourceArray)

Hendry

About Hendry

不经历复杂的简单,只是一种苍白。

发表评论

电子邮件地址不会被公开。