+ Reply to Thread
Results 1 to 10 of 10

Compile error: Sub or Function not defined.

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    Compile error: Sub or Function not defined.

    Hello,

     Dim sImportFile As String, sFile As String
        Dim sThisBk As Workbook
        Dim vfilename As Variant
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Set sThisBk = ActiveWorkbook
        sImportFile = Application.GetOpenFilename( _
        fileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")
        If sImportFile = "False" Then
            MsgBox "No File Selected!"
            Exit Sub
             
        Else
            vfilename = Split(sImportFile, "\")
            sFile = vfilename(UBound(vfilename))
            Application.Workbooks.Open Filename:=sImportFile
             
            Set wbBk = Workbooks(sFile)
            With wbBk
                If SheetExists("Submit Payroll Input") Then
                    Set wsSht = .Sheets("Submit Payroll Input")
                    wsSht.Copy After:=sThisBk.Sheets("Sheet1")
                Else
                    MsgBox "There is no sheet with name :Raw_Data in:" & vbCr & .Name
                End If
                wbBk.Close savechanges:=False
            End With
        End If
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    I get Compile error in highlighted code. I have many such codes on the same spreadsheet under Userform1 button and it works fine. I added this code in Userform2 and I get this error message.

    any idea why I am getting this error message.

    Thanks

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Compile error: Sub or Function not defined.

    I'm assuming SheetExists is a custom vba function as I'm not aware of any built in functions of that name. Therefore the most likely error is that your custom vba function is set to private or only functions for userform1. Either add the code for the sheetexists function into the module the above code is from or declare it as a public function.

  3. #3
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    Re: Compile error: Sub or Function not defined.

    well. ? I am not good with coding. I just know tweaks of existing code. This is something new i learned today.
    Could you please advise me,as to How to declare it as a public function.?

    Note : Same data works fine in Userform1 but does not work in Userform2.

    Thanks

  4. #4
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    Re: Compile error: Sub or Function not defined.

    I even tried to change the name of Userform2 to Userform1 , but no help.

  5. #5
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Compile error: Sub or Function not defined.

    Private:
    Private Function Test_Function1()
    'code
    End Function
    Public:
    Public Function Test_Function2()
    'code
    End Fucntion
    in essence. It's hard to provide much input without seeing any of the forms/code/project...

  6. #6
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715
    Here is the file
    Attached Files Attached Files
    Keep the Forum clean :


    1. Use [ code ] code tags [ /code ]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. Show appreciation to those who have helped you by clicking * Add Reputation below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  7. #7
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    Re: Compile error: Sub or Function not defined.

    I tried Private function as your suggested, but received some strange error message. It was a big message to type back in note pad.
    Please advice.

    Thanks

  8. #8
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Compile error: Sub or Function not defined.

    Change the line in the code of userform1:
    Private Function SheetExists(sWSName As String) As Boolean
    to
    Public Function SheetExists(sWSName As String) As Boolean
    and change the line in the code of userform2:
    If SheetExists("Stock Input") Then
    to
    If UserForm1.SheetExists("Stock Input") Then

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Compile error: Sub or Function not defined.

    Put this in a standard module (Insert>Module).
    Public Function SheetExists(sWSName As String) As Boolean
     Dim ws As Worksheet
        On Error Resume Next
        Set ws = Worksheets(sWSName)
      If Not ws Is Nothing Then SheetExists = True
    End Function
    If posting code please use code tags, see here.

  10. #10
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    715

    Re: Compile error: Sub or Function not defined.

    nice!!!!

    Both your code worked..

    Thanks Norie
    Thanks Yudlugar

    Regards
    Shiva

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Compile Error: Sub or Function not Defined
    By grazian2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2013, 04:27 PM
  2. [SOLVED] Compile error: sub or function not defined
    By Anita7 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2012, 08:50 PM
  3. compile error: sub or function not defined
    By eaglefana101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-17-2012, 01:18 AM
  4. Compile error: Sub or Function not defined
    By Nanga in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-23-2011, 11:05 AM
  5. [SOLVED] Help With - Compile Error: Sub or Function Not Defined
    By MWS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2006, 02:55 PM

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