在For循环中使用索引函数。 Pararmeter错误 [英] Using Index Function in For Loop. Pararmeter error

问题描述

您好：



我写了以下函数：

 公共 功能 EEPosSheets（索引作为 < span class =code-keyword> Long ， ByRef  EmpSheets  As  Excel.Worksheet，  ByRef  PosSheets  As  Excel.Worksheet）
 
 ' 此函数索引所有员工和职位表 
 ' 在安装过程中在各种循环中使用 
 '  @param EEPosSheets，是要索引的工作表 
 
 选择 案例指数
 
 案例  1  
 EmpSheets = xlWSAllEEAnnul 
 PosSheets = xlWSAllPositionAnnul 
 
 案例  2  
 EmpSheets = xlWSAllEEHourly 
 PosSheets = xlWSAllPositionHourly 
 
 案例  3  
 EmpSheets = xlWSAllEESalary 
 PosSheets = xlWSAllPositionSalary 
 
 结束 选择 
 
  Throw  新 ArgumentOutOfRangeException（ 索引）
 
 结束 功能 

我试图在Fo上使用该功能r循环如下：

  Sub  copyFormulas（）
 
  Dim  eeRefSheets  As  Excel.Worksheet 
 
 对于 i 作为 长 =  1     3  步骤  1  
 
 eeRefSheets = EEPosSheets（i）
 
 使用 eeRefSheets 
  Dim  lngLr  As  长 
 
 lngLr = .Cells.Find（What：=   *，SearchDirection：= Excel.XlSearchDirection.xlPrevious，SearchOrder：= Excel.XlSearchOrder.xlB yRows）。
 
 .Range（  B6：AH6）。自动填充（.Range（  B6：AH& lngLr），Excel.XlAutoFillType.xlFillDefault）
 
 结束 使用 
 
 下一步 i 
 
 结束  Sub  

但我收到以下错误：

 错误  1 参数不指定 参数'  EmpSheets的'公共功能EEPosSheets（索引为长，ByRef EmpSheets为Microsoft.Office.Interop.Excel.Worksheet，ByRef PosSheets为Microsoft.Office.Interop.Excel.Worksheet）为对象 

不知道为什么，我宣布eeRefSheetes为我的参数

解决方案

你好，< br $> b $ b

一开始你的功能

 EEPosSheets 

不起作用。无论索引是什么，它总是抛出异常...

你需要在case语句中移动扩展名：

 选择 案例索引
 
 案例  1  
 EmpSheets = xlWSAllEEAnnul 
 PosSheets = xlWSAllPositionAnnul 
 
  Case   2  
 EmpSheets = xlWSAllEEHourly 
 PosSheets = xlWSAllPositionHourly 
 
 案例  3  
 EmpSheets = xlWSAllEESalary 
 PosSheets = xlWSAllPositionSalary 
 
 案例 其他 
 投掷 新 ArgumentOutOfRangeException（ 索引）
 结束 选择 

其次你的方法需要3个参数

 EEPosSheets（Index As Long， ByRef EmpSheets作为Excel.Worksheet，ByRef PosSheets As Excel.Worksheet）

所以当你调用它时你需要传递3个参数

 EEPosSheets（i，sheet1，sheet2）

第三，函数应该返回一些东西（某种东西是某种物体。 ..）

 Public Function EEPosSheets（Index As Long，ByRef EmpSheets As Excel.Worksheet，ByRef PosSheets As Excel.Worksheet）as something 

否则使用子

 Public Sub EEPosSheets（Index As Long，ByRef EmpSheets As Excel.Worksheet，ByRef PosSheets As Excel.Worksheet）

祝你好运。



Valery。

Hello:

I wrote the following function:

Public Function EEPosSheets(Index As Long, ByRef EmpSheets As Excel.Worksheet, ByRef PosSheets As Excel.Worksheet)

    'This function indexes all of the Employee and Position sheets
    'to use in various loops during he instal process
    '@param EEPosSheets, are the sheets to index

    Select Case Index

        Case 1
            EmpSheets = xlWSAllEEAnnul
            PosSheets = xlWSAllPositionAnnul

        Case 2
            EmpSheets = xlWSAllEEHourly
            PosSheets = xlWSAllPositionHourly

        Case 3
            EmpSheets = xlWSAllEESalary
            PosSheets = xlWSAllPositionSalary

    End Select

    Throw New ArgumentOutOfRangeException("Index")

End Function

I am trying to use that function on a For Loop as this:

Sub copyFormulas()

    Dim eeRefSheets As Excel.Worksheet

    For i As Long = 1 To 3 Step 1

        eeRefSheets = EEPosSheets(i)

        With eeRefSheets
            Dim lngLr As Long

            lngLr = .Cells.Find(What:="*", SearchDirection:=Excel.XlSearchDirection.xlPrevious, SearchOrder:=Excel.XlSearchOrder.xlByRows).Row

            .Range("B6:AH6").AutoFill(.Range("B6:AH" & lngLr), Excel.XlAutoFillType.xlFillDefault)

        End With

    Next i

End Sub

But I am getting the following error:

Error   1   Argument not specified for parameter 'EmpSheets' of 'Public Function EEPosSheets(Index As Long, ByRef EmpSheets As Microsoft.Office.Interop.Excel.Worksheet, ByRef PosSheets As Microsoft.Office.Interop.Excel.Worksheet) As Object

Not sure why, I am declaring eeRefSheetes as my parameter

解决方案

Hello,

For a start you function

EEPosSheets

does not work. It will always throw an exception whatever the index is...
You need to move the extension inside the case statement:

Select Case Index

    Case 1
        EmpSheets = xlWSAllEEAnnul
        PosSheets = xlWSAllPositionAnnul

    Case 2
        EmpSheets = xlWSAllEEHourly
        PosSheets = xlWSAllPositionHourly

    Case 3
        EmpSheets = xlWSAllEESalary
        PosSheets = xlWSAllPositionSalary

    Case Else
        Throw New ArgumentOutOfRangeException("Index")
End Select

Secondly your method expects 3 parameters

EEPosSheets(Index As Long, ByRef EmpSheets As Excel.Worksheet, ByRef PosSheets As Excel.Worksheet)

So when you call it you need to pass 3 parameters

EEPosSheets(i, sheet1, sheet2 )

Thirdly a function should return something (somthing is an object of some kind...)

Public Function EEPosSheets(Index As Long, ByRef EmpSheets As Excel.Worksheet, ByRef PosSheets As Excel.Worksheet) As something

otherwise use a sub

Public Sub EEPosSheets(Index As Long, ByRef EmpSheets As Excel.Worksheet, ByRef PosSheets As Excel.Worksheet)

Good luck.

Valery.

这篇关于在For循环中使用索引函数。 Pararmeter错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！