Using Index Function in For Loop. Pararmeter error

Question

1.00/5 (1 vote)

See more:

Hello:

I wrote the following function:

VB

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:

VB

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:

VB

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

Posted 22-Sep-13 9:19am

qbndl8

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Valery Possoz · Answer 1 · 2013-09-22T10:29:00

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:

VB

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.