我在 line 收到“無效的程序呼叫或引數”錯誤copy = Application.WorksheetFunction.CountIfs(parameter_Rng, parametername, routing_Rng, routingname)。我確實添加了這個新行
Dim startrow as Long
startrow = getrowindex(sysnum, "SD Revision", "Test-Config")
但這是一個非常常見的行，我在整個代碼中多次使用它，并且函式回傳整數變數沒有問題。這是我的代碼目前的樣子：
Global Parameter As Long, RoutingStep As Long, parameter_Rng As Range, routing_Rng As Range
Public Sub Main()
Dim wb As Workbook, ws As Worksheet, dict As Object, sysrow As Integer, sysnum As String, wsName As String, wbSrc As Workbook, SDtab As Worksheet
Dim spectyp As Long, specmin As Long, specmax As Long\
Dim cell As Range, syswaiver As Long, axsunpart As Long
Dim wavelength_col As Long, sweeprate_col As Long, coherence_length_col As Long, power_col As Long, kclock_depth_col As Long, kclock_count_col As Long
Dim wavelength_value As Double, coherencelength_value As Double\
Dim wavelength_rowindex As Double, wavelength_rowindex_1 As Double, coherencelength_rowindex As Double
Dim startrow As Long
Parameter = getcolumnindex(SDtab, "PARAMETER")
startrow = getrowindex(sysnum, "SD Revision", "Test-Config-OCT")
RoutingStep = getcolumnindex(SDtab, "Routing Step 1")
Set parameter_Rng = SDtab.Range(SDtab.Cells(startrow, Parameter), SDtab.Cells(SDtab.Rows.Count, Parameter).End(xlUp))
Set routing_Rng = SDtab.Range(SDtab.Cells(startrow, RoutingStep), SDtab.Cells(ws.Rows.Count, RoutingStep).End(xlUp))
End Sub
Function getrowindex(WDnum As String, parametername As String, routingname As String, Optional partialFirst As Boolean = False, Optional partialSecond As Boolean = False)
Dim ws As Worksheet, rowname As Range, addr As String, copy As Long, Output As Integer
Set ws = ThisWorkbook.Worksheets(WDnum)
Set rowname = ws.Columns(Parameter).Find(What:=parametername, Lookat:=IIf(partialFirst, xlPart, xlWhole), LookIn:=xlFormulas, MatchCase:=True)
copy = Application.WorksheetFunction.CountIfs(parameter_Rng, parametername, routing_Rng, routingname)
If copy > 1 Then
Output = MsgBox("This row combination " & parametername & " and " & routingname & " appears in multiple lines. Check before running again.", vbCritical)
Stop
Else
If Not rowname Is Nothing Then
addr = rowname.Address
If partialSecond Then routingname = "*" & routingname & "*"
Do
If rowname.Offset(0, 1).Value Like routingname Then
getrowindex = rowname.Row
Exit Do
End If
Set rowname = ws.Columns(Parameter).FindNext(after:=rowname)
Loop While rowname.Address <> addr
ElseIf rowname Is Nothing Then
Output = MsgBox(parametername & " row could not be found. Check before running again.", vbCritical)
Stop
End If
End If
End Function
uj5u.com熱心網友回復：
您可以重寫getrowindex，使其不依賴于這兩個全域范圍：
Function getrowindex(WDnum As String, parametername As String, routingname As String, _
Optional partialFirst As Boolean = False, Optional partialSecond As Boolean = False)
Dim ws As Worksheet, rowname As Range, addr As String, copy As Long, Output As Integer
Dim rngParam As Range, rngRouting As Range
Set ws = ThisWorkbook.Worksheets(WDnum)
Set rowname = ws.Columns(parameter).Find(What:=parametername, Lookat:=IIf(partialFirst, xlPart, xlWhole), _
LookIn:=xlFormulas, MatchCase:=True)
If Not rowname Is Nothing Then
addr = rowname.Address
If partialSecond Then routingname = "*" & routingname & "*"
Do
If rowname.EntireRow.Columns(RoutingStep).Value Like routingname Then
If rngParam Is Nothing Then 'first match? Check for additional matches...
Set rngParam = ws.Range(rowname, ws.Cells(Rows.Count, parameter))
Set rngRouting = rngParam.EntireRow.Columns(RoutingStep)
If Application.WorksheetFunction.CountIfs(rngParam, parametername, rngRouting, routingname) > 1 Then
MsgBox "This row combination " & parametername & " and " & routingname & _
" appears in multiple lines. Check before running again.", vbCritical
Stop
End If
End If
getrowindex = rowname.Row
Exit Do
End If
Set rowname = ws.Columns(parameter).FindNext(after:=rowname)
Loop While rowname.Address <> addr
Else
MsgBox parametername & " row could not be found. Check before running again.", vbCritical
Stop
End If
End Function
不過，您的整個代碼有點復雜 - 您使用全域變數會使這變得非常難以維護和/或排除故障......
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/518032.html
標籤：擅长vba