将VBA转换为Google Apps脚本

debugcn 发表于 Dev

史蒂夫·K

我在下面有VBA代码。我不知道它是如何工作的，但我想将其转换为Google表格。

我希望有人可以：

向我讲解VBA的工作，以便我也许可以说出足够的理由来将其作为Google Apps脚本进行编程，

或者

向我展示如何通过Google表格实现相同的VBA功能。

Function getData(targetName As String, targetSheet As String, targetDate)
Application.Volatile True
Dim res As Double
Dim col As Integer
Dim cell As Range
Dim names As Range

Dim lastrow As Long

With ThisWorkbook.Worksheets(targetSheet)
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    col = Application.Match(targetDate, .Range("4:4"), 0)
    If col = 0 Then
        getData = CVErr(xlErrNA)
        Exit Function
    End If
    Set names = .Range(.Cells(4, "A"), .Cells(lastrow, "A"))

    For Each cell In names
        If cell = targetName Then
            res = res + cell.Offset(, col - 1)
        End If
    Next cell
End With

getData = res
End Function

这是使用功能的示例excel文件的链接：https : //www.dropbox.com/s/h5vcjv9tlh1vvg7/Resources%20and%20Projects%20Full%20Example.xlsm

确切地

尽管我对Google Apps脚本不熟悉，但我可以在第一部分为您提供帮助。

函数的要点似乎是累加所有值，在这些值中，name在A列中找到的匹配项targetName作为参数传入，而date在第4行中找到的匹配项targetDate（也是参数）匹配。（行由决定name，列由决定date。）然后将总值返回为double。

这是一行一行的注释。

Function getData(targetName As String, targetSheet As String, targetDate)
Application.Volatile True 'I don't see a reason for this line
Dim res As Double
Dim col As Integer
Dim cell As Range
Dim names As Range

Dim lastrow As Long

With ThisWorkbook.Worksheets(targetSheet) 'All ranges start with ThisWorkbook.'targetSheet'
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Get the last row with data in column A to 'lastrow'
    col = Application.Match(targetDate, .Range("4:4"), 0) 'Find 'targetDate' in row 4 and set it to 'col'
    If col = 0 Then 'Couldn't find 'targetDate'
        getData = CVErr(xlErrNA) 'Function returns the appropriate error
        Exit Function 'Exit the function after setting the return value
    End If
    Set names = .Range(.Cells(4, "A"), .Cells(lastrow, "A")) 'Setting the range from A4 to A'lastrow' to 'names'

    For Each cell In names 'Looping through every 'cell' in the 'names' range
        If cell = targetName Then 'If the 'cell' value matches the 'targetName' passed in as a parameter
            res = res + cell.Offset(, col - 1) 'Add the value from the column with the 'targetDate' to 'res'
        End If
    Next cell
End With

getData = res 'Return the total
End Function

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。