Getting an Error that Function is not Defined

I have the following code and macro, but when I run it I'm getting an error saying that the Function is not defined, although it is.

I got this code off of Experts Exchange.

Here is the Sub that calls the macro:
  Private Sub cmdUploadXMLFile_Click()
     Sheets("Sheet2").Select
     Call XML
 End Sub

Open in new window


Here is the Macro:
Sub XML() '
' XML Macro

Call ExportToXML("H:\NRTEcho\ACTIVE\GROPER\eBillStatusChanges.xml", "Row")

End Sub

Open in new window


Here is the Function that the macro calls:
Public Function ExportToXML(FullPath As String, RowName _
   As String) As Boolean
On Error GoTo ErrorHandler
 
 Dim colIndex As Integer
 Dim rwIndex As Integer
 Dim asCols() As String
 Dim oWorkSheet As Worksheet
 Dim sName As String
 Dim lCols As Long, lRows As Long
 Dim iFileNum As Integer


 Set oWorkSheet = ThisWorkbook.Worksheets(2)
 sName = oWorkSheet.Name
 lCols = oWorkSheet.Columns.Count
 lRows = oWorkSheet.Rows.Count

 ReDim asCols(lCols) As String

 iFileNum = FreeFile
 Open FullPath For Output As #iFileNum

 For i = 0 To lCols - 1
     'Assumes no blank column names
     If Trim(Cells(1, i + 1).Value) = "" Then Exit For
     asCols(i) = Cells(1, i + 1).Value
 Next i

 If i = 0 Then GoTo ErrorHandler
 lCols = i

 Print #iFileNum, "<?xml version=""1.0""?>"
 Print #iFileNum, "<" & sName & ">"
 For i = 2 To lRows
 If Trim(Cells(i, 1).Value) = "" Then Exit For
 Print #iFileNum, "<" & RowName & ">"
   
     For j = 1 To lCols
         
         If Trim(Cells(i, j).Value) <> "" Then
         Print #iFileNum, "  <" & asCols(j - 1) & "><![CDATA[";
         Print #iFileNum, Trim(Cells(i, j).Value);
         Print #iFileNum, "]]></" & asCols(j - 1) & ">"
         DoEvents 'OPTIONAL

         End If
     Next j
     Print #iFileNum, " </" & RowName & ">"
 Next i

 Print #iFileNum, "</" & sName & ">"
 ExportToXML = True
ErrorHandler:
 If iFileNum > 0 Then Close #iFileNum
 Exit Function
 End Function

Open in new window



What am I doing wrong?

Thanks,
gdunn59
LVL 1
gdunn59Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Change the function to Pubic from Private.
Martin LissOlder than dirtCommented:
Oops Public lol.
gdunn59Author Commented:
Martin Liss,

Still getting the same error after making that change.

It is happening when it gets to the Macro:

Sub XML()
Call ExportToXML("H:\NRTEcho\ACTIVE\GROPER\eBillStatusChanges.xml", "Row")
End Sub

Thanks,
gdunn59
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Martin LissOlder than dirtCommented:
Ok.

So there's no confusion, why is the name of the function that's not defind?

Where is it located? A sheet, a module?

Where is it called from? A sheet, a module?
gdunn59Author Commented:
Martin,

Did you mean "what, not why, is the name of the function"?

It is the function that is being called from the macro:

Sub XML() '
' XML Macro

Call ExportToXML("H:\NRTEcho\ACTIVE\GROPER\eBillStatusChanges.xml", "Row")

End Sub

Here is the Actual Function:

Public Function ExportToXML(FullPath As String, RowName _
   As String) As Boolean
On Error GoTo ErrorHandler
 
 Dim colIndex As Integer
 Dim rwIndex As Integer
 Dim asCols() As String
 Dim oWorkSheet As Worksheet
 Dim sName As String
 Dim lCols As Long, lRows As Long
 Dim iFileNum As Integer


 Set oWorkSheet = ThisWorkbook.Worksheets(2)
 sName = oWorkSheet.Name
 lCols = oWorkSheet.Columns.Count
 lRows = oWorkSheet.Rows.Count

 ReDim asCols(lCols) As String

 iFileNum = FreeFile
 Open FullPath For Output As #iFileNum

 For i = 0 To lCols - 1
     'Assumes no blank column names
     If Trim(Cells(1, i + 1).Value) = "" Then Exit For
     asCols(i) = Cells(1, i + 1).Value
 Next i

 If i = 0 Then GoTo ErrorHandler
 lCols = i

 Print #iFileNum, "<?xml version=""1.0""?>"
 Print #iFileNum, "<" & sName & ">"
 For i = 2 To lRows
 If Trim(Cells(i, 1).Value) = "" Then Exit For
 Print #iFileNum, "<" & RowName & ">"
   
     For j = 1 To lCols
         
         If Trim(Cells(i, j).Value) <> "" Then
         Print #iFileNum, "  <" & asCols(j - 1) & "><![CDATA[";
         Print #iFileNum, Trim(Cells(i, j).Value);
         Print #iFileNum, "]]></" & asCols(j - 1) & ">"
         DoEvents 'OPTIONAL

         End If
     Next j
     Print #iFileNum, " </" & RowName & ">"
 Next i

 Print #iFileNum, "</" & sName & ">"
 ExportToXML = True
ErrorHandler:
 If iFileNum > 0 Then Close #iFileNum
 Exit Function
 End Function

It is located in Sheet1 and is being called from the Macro "XML".
Martin LissOlder than dirtCommented:
Yes I did mean what and not why.

I don't see what's wrong but let me suggest two things.

Try compiling the code and see what if any errors show up, and

Call ExportToXml directly rather than calling XML.
Martin LissOlder than dirtCommented:
Have you been able to resolve this? If not then please attach your workbook.
gdunn59Author Commented:
Martin,

If I compile the code, I'm still getting the error that the Sub or Function is not defined.  The error is happening on the following code:

Sub XML()
'
' XML Macro
'
Call ExportToXML("H:\NRTEcho\ACTIVE\GROPER\eBillStatusChanges.xml", "Row")

End Sub

Open in new window



Could it be because I don't have a specific Reference?

Thanks,
gdunn59
Martin LissOlder than dirtCommented:
Add the sheet name in the call, e.g.
Sub XML()
'
' XML Macro
'
Call Sheet1.ExportToXML("H:\NRTEcho\ACTIVE\GROPER\eBillStatusChanges.xml", "Row")

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gdunn59Author Commented:
Martin,

Adding the Sheet1 worked, but now I get the following error when opening the XML file that was created with the Function ExportToXML:

The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.


--------------------------------------------------------------------------------

XML document must have a top level element. Error processing resource 'file:///H:/NRTEcho/ACTIVE/GROPER/eBillStatusChanges....


Any clues????

Thanks,
gdunn59
Martin LissOlder than dirtCommented:
I'll try to help if I can but I think that that should be a new question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.