Error: Argument not optional

toshesh · January 19th, 2006, 01:18 PM

Hi everyone
I am trying to run a code shown below which is executed when a button is clicked on excel spreadsheet. I get the error above but have no idea why it occurs. I've been searching sites but no luck...
Could anyone help me please!

Code:

Option Explicit

Sub Separate()
    Dim xlsh As Worksheet
    Dim i As Integer
    Dim vendorColumnNum As Integer
    Set xlsh = ActiveSheet
    Dim vendorList As Dictionary
    Dim fname As String
    Set vendorList = New Dictionary
    MsgBox ActiveWorkbook.Name
    Dim fs As FileSystemObject

    Set fs = CreateObject("Scripting.FileSystemObject")
    Debug.Print fs.GetBaseName(ActiveWorkbook.Name)
    Debug.Print fs.GetExtensionName(ActiveWorkbook.Name)

    vendorColumnNum = 13
    vendorList = getAllVendorName(vendorColumnNum)
'   the red part is where the error message is
    For i = 0 To vendorList.Count - 1
        Debug.Print vendorList.Item(i)
        'Call writeExcelfile(vendorList.item(i), "filename")
    Next i


End Sub
 
Function getAllVendorName(vendorColumnNum) As Dictionary
    Dim xlsh As Worksheet
    Dim i As Integer
    Dim lastRowCount As Integer
    Dim vendorList As Dictionary

    Set xlsh = ActiveSheet
    Set vendorList = New Dictionary

    vendorList.CompareMode = TextCompare
    lastRowCount = xlsh.Cells.SpecialCells(xlCellTypeLastCell).Row

    For i = 8 To lastRowCount
        If (vendorList.Exists(xlsh.Cells(i, vendorColumnNum).Value) = False) Then
            vendorList.Add i, xlsh.Cells(i, vendorColumnNum).Value
        End If
    Next i

    getAllVendorName = vendorList

End Function

echovue · January 19th, 2006, 01:26 PM

Try and change the function declaration to something like...

Function getAllVendorName(vendorColumnNum as Integer) As Dictionary

Mike

Mike
EchoVue.com

toshesh · January 19th, 2006, 01:32 PM

Thank you for a quick reply!

I still get the same error message "Compile error: argument not optional"

echovue · January 19th, 2006, 02:18 PM

Are you using an add in for the Dictionary Data Type? I am afraid I am not familiar with it. If you could point me in the right direction, I could try and duplicate the error on my PC.

Thanks

Mike

Mike
EchoVue.com

toshesh · January 19th, 2006, 02:40 PM

In the second function I am reading cell values from a particular column, specifically column 13. In the red section I am using the dictionary.add method to add data into vendorList

Code:

Function getAllVendorName(vendorColumnNum) As Dictionary
    Dim xlsh As Worksheet
    Dim i As Integer
    Dim lastRowCount As Integer
    Dim vendorList As Dictionary

    Set xlsh = ActiveSheet
    Set vendorList = New Dictionary

    vendorList.CompareMode = TextCompare
    lastRowCount = xlsh.Cells.SpecialCells(xlCellTypeLastCell).Row

    For i = 8 To lastRowCount
        If (vendorList.Exists(xlsh.Cells(i, vendorColumnNum).Value) = False) Then
            vendorList.Add i, xlsh.Cells(i, vendorColumnNum).Value        End If
    Next i

    getAllVendorName = vendorList

End Function

Alternatively you can recreate the error with the following code (i think)

Code:

Sub Separate()
    Dim xlsh As Worksheet
    Dim i As Integer
    Dim vendorColumnNum As Integer
    Set xlsh = ActiveSheet
    Dim vendorList As Dictionary
    Dim fname As String
    Dim lastRowCount As Integer
    Set vendorList = New Dictionary
    'MsgBox ActiveWorkbook.Name
    Dim fs As FileSystemObject

    Set fs = CreateObject("Scripting.FileSystemObject")

    vendorColumnNum = 13
    vendorList = getAllVendorName(vendorColumnNum)
 End Sub

Function getAllVendorName(vendorColumnNum As Integer) As Dictionary
    Dim xlsh As Worksheet
    Dim i As Integer
    Dim lastRowCount As Integer
    Dim vendorList As Dictionary

    Set xlsh = ActiveSheet
    Set vendorList = New Dictionary

    vendorList.CompareMode = TextCompare
    vendorList.Add 1, "test1"
    vendorList.Add 2, "test2"

    getAllVendorName = vendorList

End Function

echovue · January 19th, 2006, 03:28 PM

What version of Excel are you using? Unfortunately, it looks like 2003 no longer has the Dictionary object as an option. I should be home in a few hours and have access to an older version. I'll try it again then.

ANYONE ELSE HAVE ANY IDEAS?

Mike

Mike
EchoVue.com

maccas · January 20th, 2006, 04:28 AM

Mike,

The dictionary obejct is part of the scripting object model. This can be accessed by adding a reference to your project to Microsoft Scripting Runtime (or scrrun.ddl in the System32 folder).

To solve the problem I think that toshesh needs to change the line

Code:

vendorList = getAllVendorName(vendorColumnNum)

to

Code:

Set vendorList = getAllVendorName(vendorColumnNum)

This is because you are assigning an object type with this command line and therefore the Set keyword is required.

As a separate point, the code written seems to be mixing early and late binding with reference to using the Scripting object model. As a general rule I would always stick to one or the other but the code should still work.

Maccas

maccas · January 20th, 2006, 04:32 AM

One more thing: The line

Code:

getAllVendorName = vendorList

in the function getAllVendorName needs to be cahnged to

Code:

Set getAllVendorName = vendorList

as well. Same principal as before.

Maccas

toshesh · January 20th, 2006, 08:50 AM

Thank you all and sorry for the late response.

As for the Excel version, I am using 2003
Dictionary requires the "microsoft scripting runtime" library.

I am very new to VBA and am not sure what you mean by "mixing early and late binding with reference to using the Scripting object model"

maccas · January 20th, 2006, 08:57 AM

Early & late binding relate to the two ways in which you can refernce external code libraries for use in your project. Early binding involves specifically refernecing a code library (Tools -> References ...) whilst Late binding does not require any references.

There are pros and cons between the two methods Early binding is much easier to write code with and makes the code easier to debug when developing. Late binding is a better method to use with projects that are going to be externally distributed (it reduces the cahnces of a library missing error).

I've actually run out of time to discuss how you implement early & late binding but a simple internet search should explian the difference.

Maccas