asked on

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

Martin Liss

Change the function to Pubic from Private.

Martin Liss

Oops Public lol.

gdunn59

ASKER

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

Martin Liss

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?

gdunn59

ASKER

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 Liss

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 Liss

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

gdunn59

ASKER

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

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gdunn59

ASKER

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 Liss

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