Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 19th, 2006, 01:18 PM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Error: Argument not optional

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
Reply With Quote
  #2 (permalink)  
Old January 19th, 2006, 01:26 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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

Function getAllVendorName(vendorColumnNum as Integer) As Dictionary

Mike

Mike
EchoVue.com
Reply With Quote
  #3 (permalink)  
Old January 19th, 2006, 01:32 PM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you for a quick reply!

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

Reply With Quote
  #4 (permalink)  
Old January 19th, 2006, 02:18 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old January 19th, 2006, 02:40 PM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old January 19th, 2006, 03:28 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old January 20th, 2006, 04:28 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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


Reply With Quote
  #8 (permalink)  
Old January 20th, 2006, 04:32 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
  #9 (permalink)  
Old January 20th, 2006, 08:50 AM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"

Reply With Quote
  #10 (permalink)  
Old January 20th, 2006, 08:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error: Invalid postback or call back argument kalyanykk ASP.NET 2.0 Basics 0 August 26th, 2008 06:03 AM
Error BC30455 Argument not specified for parameter dick3425 Visual Basic 2005 Basics 0 November 21st, 2007 09:10 AM
Argument not Optional aziaraphale Excel VBA 5 August 1st, 2007 02:53 AM
compile error: argument not optional loverman210 Beginning VB 6 3 June 8th, 2007 07:59 AM



All times are GMT -4. The time now is 12:59 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.