Getting an Error that Function is not Defined

gdunn59
gdunn59 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Change the function to Pubic from Private.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Oops Public lol.

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Have you been able to resolve this? If not then please attach your workbook.

Author

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
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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

Author

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 dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'll try to help if I can but I think that that should be a new question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial