about error 450: Wrong number of arguments or invalid property assignment-VBForums
Results 1 to 10 of 10

Thread: about error 450: Wrong number of arguments or invalid property assignment

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2006
    Posts
    14

    about error 450: Wrong number of arguments or invalid property assignment

    Hi,
    What I did was simply to change date 'MMM-YY' based on user inputs. The code goes like this:

    Sub ChngMonth()
    MMMYY = InputBox("Enter The Last Data Month (mmm-yy):")
    If MMMYY = "" Then Exit Sub
    ddmmmyy = "15-" & MMMYY
    For i = 0 To 330 Step 30
    j = 12 - i / 30
    Worksheets("Monthlist").Cells(1, j) = "'" & format(CDate(ddmmmyy) - i, "mmm-yy")
    Next
    End Sub

    When I run it, I will have 450 for 'format()' statement. But the funny thing is I don't always get that error. For some workbooks, I do; the others, I don't. I guess it has something to do with the way the book was set up; or some options that I didn't select. If the problem is with "Wrong number of arguments", how come for some workbooks it works, some not, using the same statement.
    Thanks in advance.
    Feipe

  2. #2
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: about error 450: Wrong number of arguments or invalid property assignment

    Excel VBA question moved to Office Development
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  3. #3
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,044

    Re: about error 450: Wrong number of arguments or invalid property assignment

    Put "Option Explicit" at the very top of your module.

    Change the names of your variables ... MMMYY seems dangerous to me ... and properly define them ... something like:

    Dim str_MMMYY As String
    Dim str_ddmmmyy As String

    That way there is no question of a Namespace overlap.

    I'll look at the rest of your code later.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2006
    Posts
    14

    Re: about error 450: Wrong number of arguments or invalid property assignment

    Art,
    I changed the code the way you recommended but I still got '450'.
    Thanks again.
    Feipe

    option explicit

    Sub ChngMonth()
    Dim str_MMMYY, str_ddmmmyy As String
    Dim i, j As Integer

    str_MMMYY = InputBox("Enter The Last Data Month (mmm-yy):")
    If str_MMMYY = "" Then Exit Sub
    str_ddmmmyy = "15-" & str_MMMYY
    For i = 0 To 330 Step 30
    j = 12 - i / 30
    Worksheets("Monthlist").Cells(1, j) = "'" & format(CDate(str_ddmmmyy) - i, "mmm-yy")
    Next
    End Sub

  5. #5
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,044

    Re: about error 450: Wrong number of arguments or invalid property assignment

    One minor problem (but it doesn't generate any errors):
    Code:
        Dim str_MMMYY, str_ddmmmyy As String  '< Leaves str_MMMYY as VARIANT!
        Dim i, j As Integer                   '< Leaves i as VARIANT!
    I plugged in this code into a new clean workbook with a default blank sheet "Sheet1", and it correctly puts 12 monthly headers across the top row of the sheet from right to left starting with Mar-06 at the right and ending with Apr-05 at the left. It runs fine in both Excel '97 and Excel 2003:
    Code:
    Option Explicit
    
    Sub ChngMonth()
        Dim str_MMMYY As String, str_ddmmmyy As String
        Dim i As Integer, j As Integer
    
        str_MMMYY = InputBox("Enter The Last Data Month (mmm-yy):")
        
        'TEST TEST TEST TEST
        str_MMMYY = "March 2006"
        'END TEST
        
        If str_MMMYY = "" Then Exit Sub
        str_ddmmmyy = "15-" & str_MMMYY
        
        Debug.Print str_ddmmmyy
        
        For i = 0 To 330 Step 30
            j = 12 - (i / 30)
            MsgBox Format(CDate(str_ddmmmyy) - i, "mmm-yy")
            Worksheets("Sheet1").Cells(1, j) = "'" & Format(CDate(str_ddmmmyy) - i, "mmm-yy")
        Next
    End Sub
    Last edited by Webtest; Feb 22nd, 2006 at 09:14 AM.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  6. #6

    Thread Starter
    New Member
    Join Date
    Feb 2006
    Posts
    14

    Re: about error 450: Wrong number of arguments or invalid property assignment

    Art,
    Thanks for the attempt! Please read my first email: "...funny thing is...". I have no problem with a fresh book, even using my code. The problem is with the existing books, or some of them. Could you try your code or mine in your existing books and see what happens?
    Highly appreciate it.
    feipe.

  7. #7
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,044

    Re: about error 450: Wrong number of arguments or invalid property assignment

    Let's find out exactly what is broken ...

    Right after the "j = 12 - (i / 30)" line, add the following to YOUR code:
    Code:
    'Test access to the desired sheet name
    MsbBox ActiveWorkbook.Sheets("Monthlist").Name
    MsbBox ActiveWorkbook.Worksheets("Monthlist").Name
    'Test access to the desired Format function
    MsgBox Format(CDate(str_ddmmmyy) - i, "mmm-yy")
    Which line reports the error?
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  8. #8
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,044

    Re: about error 450: Wrong number of arguments or invalid property assignment

    Try explicitly forcing loading the VALUE property:
    Code:
    Worksheets("Sheet1").Cells(1, j).Value = "'" & Format(CDate(str_ddmmmyy) - i, "mmm-yy")
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  9. #9

    Thread Starter
    New Member
    Join Date
    Feb 2006
    Posts
    14

    Re: about error 450: Wrong number of arguments or invalid property assignment

    I tried almost all of them before I came to the forum. None of them worked since it's an error during compilation. It won't even take 'On Error Goto Resume', let alone anything else.
    Thanks again, please don't spend too much time on it. It's not worth it. I'll try to figure out a way of avoiding VBA Format().
    Thanks anyway.
    Feipe

  10. #10
    New Member
    Join Date
    Mar 2007
    Posts
    1

    Re: about error 450: Wrong number of arguments or invalid property assignment

    Hi buddy,

    I got a solution to this problem. I got a similar problem in one of the worksheet in the workbook. So the code

    year(now) was working in some userforms but not in other userform. So i checked whether any of the object's name on the userform is identical with the function name "year".

    infact I have defined name and caption property of a label as 'year', hence when i was using year function in code for userform_activate() I was getting the error "450, wrong type of arguments", but after changing the name and caption property of that label from 'year' to 'yy', this code started working.

    Hence I suggest you to check in your worksheet or userform, whether you have defined any property having the name identical to the function for which you are getting this error. If yes then change that property to something else other than the function's name and the function should work for you...

    Thanks nd regards
    Shekhar
    Love all serve all!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.