+ Reply to Thread
Results 1 to 6 of 6

For function for VBA

  1. #1
    Registered User
    Join Date
    01-18-2005
    Posts
    62

    For function for VBA

    Hi,

    I read this tips from this web site:

    Mail Sheet Array using VBA in Microsoft Excel

    • Create a new workbook with the SheetsArray.
    • Save the workbook before mailing it.
    • Delete the file from your hard disk after it is sent.

    Sub Mail_SheetsArray()
    Dim strDate As String
    Sheets(Array("Sheet1", "Sheet3")).Copy
    strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
    ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
    & " " & strDate & ".xls"
    ActiveWorkbook.SendMail "ron@debruin.nl", _
    "This is the Subject line"
    ActiveWorkbook.ChangeFileAccess xlReadOnly
    Kill ActiveWorkbook.FullName
    ActiveWorkbook.Close False
    End Sub

    I am trying to run it in Excel but the error happens with Format function with message: 'Wrong number of arguments or invalid property assignment'

    Could you help me to understand and to make it run.

    Thank you very much

    Nam

  2. #2
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    The code works perfectly fine. Could you tell us which line of code is giving the error.

    - Mangesh

  3. #3
    Registered User
    Join Date
    01-18-2005
    Posts
    62
    Thanks for your support.

    The error in line Format ((Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss") with the following message 'Wrong number of arguments or invalid property assignment'

    Regards,

    Nam

  4. #4
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Even in this line there is no error. It works perfectly fine.
    Maybe try the two seperately, something like
    strDate = Format(Date, "dd-mm-yy")
    first, and then
    strDate = Format(Time, "h-mm-ss")
    and try to isolate the error.

    - Mangesh

  5. #5
    Registered User
    Join Date
    01-18-2005
    Posts
    62
    Hi,

    Thank you for your support.

    I am using Excel 2002. Even though, I try to run this Code, the error also occurs

    Sub TestFormat()

    MsgBox Format(5459.4, "##,###.00")
    End Sub

    I think is there any problem with my VBA in Excel or what should I add in some thing to make Format function work

    Regards,

    Nam

  6. #6
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    That's strange.
    Try to click F1 when your cursor is on FORMAT in the VBA code. Check out the help.

    Also try
    MsgBox Format(5459.4)
    i.e. without any format type. This would tell you if you have a problem with the format function or the format type you specify.
    In the above case, the result should be the plain number 5459.4 itself.

    - Mangesh
    Last edited by mangesh_yadav; 01-20-2005 at 11:47 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1