将一个对象传递给一个函数或子程序
问题描述:
我想通过excel工作簿关闭一个子程序,而不必再次打开和关闭它。将一个对象传递给一个函数或子程序
我想这
Dim ExcelApp As New Excel.Application
Dim ws As Object
Public Function cert()
Set ws = ExcelApp.Workbooks.Open(FileDir).Sheets(1)
If CheckForMutipleUIC(ws) = True Then <---- this is where I call the Function
....
该函数打开这个
Public Function CheckForMutipleUIC(ByVal ws As Excel.Workbook) As Boolean
' Dim ExcelApp As New Excel.Application
' DataFleet = CurrentProject.Path & "\Extract\Fleet.xls"
' Set ws1 = ExcelApp.Workbooks.Open(DataFleet).Sheets(1)
UICCheck = ws.Range("GD2").Value
For Each Cell In ws.Range("GD2:GD10000").cells
If Cell.Text <> "" Then
If Cell.Value <> UICCheck Then
Err.Raise 513, "VICILauncher-DataValidation-CheckForMultipleUIC", "Multiple UICS Found in extract. Please Make sure the correct UIC is being Extracted"
End If
End If
Next
CheckForMutipleUIC = True
Exit Function
但我想我没有通过Excel对象正确。应用程序对象如何通过关闭和重新打开来传递给子对象。
答
尝试下面的代码,解释代码的注释里:
Option Explicit
Dim ExcelApp As New Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Dim FileDir As String
'======================================================================
Public Function cert() '<-- NOT SURE why this is a Function and not a Sub ?
' set the workbook object
Set wb = ExcelApp.Workbooks.Open(FileDir)
Set wb = Workbooks.Open(FileDir) ' <-- why not use this if this is inside Excel VBA ?
' set the worksheet object
Set ws = wb.Sheets(1)
If CheckForMutipleUIC(ws) = True Then ' pass the worksheet object to the Function
'....
End If
End Function
'======================================================================
Public Function CheckForMutipleUIC(ByVal ws As Worksheet) As Boolean
Dim Cell As Range
Dim UICCheck
UICCheck = ws.Range("GD2").Value
For Each Cell In ws.Range("GD2:GD10000").Cells
' If Cell.Text <> "" Then
If Cell.Value2 <> "" Then ' <-- use Value 2 instead of Text
If Cell.Value <> UICCheck Then
Err.Raise 513, "VICILauncher-DataValidation-CheckForMultipleUIC", "Multiple UICS Found in extract. Please Make sure the correct UIC is being Extracted"
End If
End If
Next
CheckForMutipleUIC = True
End Function
答
你没有包括你可能得到的错误信息。但只是查看发布的代码,看起来您将工作表'ws'传递到需要工作簿的CheckForMutipleUIC函数中。尝试更改以下内容。
Public Function CheckForMutipleUIC(ByVal ws As Excel.Workbook) As Boolean
到
Public Function CheckForMutipleUIC(ByVal ws As Excel.Worksheet) As Boolean
答
您可能需要使用的ByRef(和正确的声明如已经提到的):
Public Function CheckForMutipleUIC(ByRef ws As Excel.Worksheet) As Boolean
+0
雅我修正了这个问题,现在当我使用它时,每个单元格中的每个单元格都会变成空白。该对象似乎没有被传递给这个函数。 – Quint
你说你想传递一个'工作簿'。 **但是**然后你在这里将它设置为'Worksheet':'Set ws = ExcelApp.Workbooks.Open(FileDir).Sheets(1)'。然后在你的函数里面有'(ByVal ws As Excel.Workbook)',**,但是在再次出现'UICCheck = ws.Range(“GD2”)。Value',它是一个'Worksheet'对象。那么是哪一个呢 ? 'ws'是'Worksheet'还是'Workbook'? –
你是对的我想通过excel表功能 – Quint
我试了一下,它运行出错,但一步一步通过'对于每个单元'的单元格来空白 – Quint