Trying to use udf in add-in

[SauQ]

Hi everyone,

I am still a learner in excel / vba. I am using XP pro and Microsoft
office 2003.

I have copied this UDF code (Private Function ExportToXML_C) that
exports my current data in an excel spreadsheet into an XML format.

Private Function ExportToXML_C(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 = ActiveWorkbook.Worksheets("BorangC2007")
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, "<" & sName & ">"
For i = 2 To lRows
If Trim(Cells(i, 1).Value) = "" Then Exit For


For j = 1 To lCols

If Trim(Cells(i, j).Value) <> "" Then

Print #iFileNum, Trim(Cells(i, j).Value)

DoEvents 'OPTIONAL
End If
Next j

Next i

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


This codes together with some others codes i have were saved as an Add-
in.
The issue is the above udf only works in excel at cell say E1(as a
UDF). I am trying to incorporate the function into another vba module
like below but I keep getting "Run-time error 1004 Macro cannot be
found".


Sub Run_ExportToXML_C()

ThisWorkbook.Sheets("BorangC2007").Copy
After:=ActiveWorkbook.Sheets("FormC")


With Sheets("BorangC2007")
..Range("D2").Formula = "=IF(ISBLANK('FormC'!R[1]C),"""",UPPER('FormC'!
R[1]C))"
..Range("D3").Formula = "=IF(ISBLANK('FormC'!RC[9]),"""",UPPER('FormC'!
RC[9]))"
..Range("D4").Formula = "=IF(ISBLANK('FormC'!
R[1]C),"""",TEXT(SUBSTITUTE('FormC'!R[1]C,""-"",""""),""0""))"

..Range("E1").Application.Run "ExportToXML_C(""D:\My Documents\Form C
2007.xml"",RC[-4])"

End With
End Sub

Both Private Function ExportToXML_C and Sub Run_ExportToXML_C() are in
the same add-in.
Any help to make it work in vb is very much appreciated.

 

[Joel]

In excel ExportToXML_C is a function just as if you used trim(). You dont
need any quotes or application Run. Because your two pararmeter are strings,
they need to be in a single pair of double quotes. Notice I remove the
double set of double quotes.

Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")

 

[SauQ]

Thanks Joel !

I have tried ...

Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")

and the run-time error message doesn't pop up anymore, however when I
run the Sub Run_ExportToXML_C(), my data didn't get converted to XML
as intended i.e I only got a blank converted XML sheet.

any ideas / solutions ?

Many thanks to anyone who can help me on this.

In excel ExportToXML_C is a function just as if you used trim().  You dont
need any quotes or application Run.  Because your two pararmeter are strings,
they need to be in a single pair of double quotes.  Notice I remove the
double set of double quotes.

Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")

Hi everyone,

I am still a learner in excel / vba.  I am using XP pro and Microsoft
office 2003.

I have copied this UDF code (Private Function ExportToXML_C) that
exports my current data in an excel spreadsheet into an XML format.

Private Function ExportToXML_C(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 = ActiveWorkbook.Worksheets("BorangC2007")
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, "<" & sName & ">"
For i = 2 To lRows
If Trim(Cells(i, 1).Value) = "" Then Exit For

    For j = 1 To lCols

        If Trim(Cells(i, j).Value) <> "" Then

           Print #iFileNum, Trim(Cells(i, j).Value)

           DoEvents 'OPTIONAL
        End If
    Next j

Next i

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

This codes together with some others codes i have were saved as an Add-
in.
The issue is the above udf only works in excel at cell say E1(as a
UDF).  I am trying to incorporate the function into another vba module
like below but I keep getting "Run-time error 1004 Macro cannot be
found".

Sub Run_ExportToXML_C()

ThisWorkbook.Sheets("BorangC2007").Copy
After:=ActiveWorkbook.Sheets("FormC")

With Sheets("BorangC2007")
..Range("D2").Formula = "=IF(ISBLANK('FormC'!R[1]C),"""",UPPER('FormC'!
R[1]C))"
..Range("D3").Formula = "=IF(ISBLANK('FormC'!RC[9]),"""",UPPER('FormC'!
RC[9]))"
..Range("D4").Formula = "=IF(ISBLANK('FormC'!
R[1]C),"""",TEXT(SUBSTITUTE('FormC'!R[1]C,""-"",""""),""0""))"

..Range("E1").Application.Run "ExportToXML_C(""D:\My Documents\Form C
2007.xml"",RC[-4])"

End With
End Sub

Both Private Function ExportToXML_C and Sub Run_ExportToXML_C() are in
the same add-in.
Any help to make it work in vb is very much appreciated.- Hide quoted text -

- Show quoted text -

 

[Joel]

I have two comments.

1) I hate using On error statements because when you do have errors it masks
these errors. I comment out the On error statements when I have problems so
that the real error messages get displayed.

2) The active worksheets may not be the correct worksheet. When you call a
function from a worksheet it is automatically the active worksheet. When yu
call the same function from VBA it may not be the active worksheet. You have
statments like the following

If Trim(Cells(i, j).Value) <> "" Then

I would do the following in Sub Run_ExportToXML_C() :

sheets("xyz").activate '< add, change worksheet name
Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")

Thanks Joel !

I have tried ...

Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")

and the run-time error message doesn't pop up anymore, however when I
run the Sub Run_ExportToXML_C(), my data didn't get converted to XML
as intended i.e I only got a blank converted XML sheet.

any ideas / solutions ?

Many thanks to anyone who can help me on this.

In excel ExportToXML_C is a function just as if you used trim(). You dont
need any quotes or application Run. Because your two pararmeter are strings,
they need to be in a single pair of double quotes. Notice I remove the
double set of double quotes.

Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")

Hi everyone,

I am still a learner in excel / vba. I am using XP pro and Microsoft
office 2003.

I have copied this UDF code (Private Function ExportToXML_C) that
exports my current data in an excel spreadsheet into an XML format.

Private Function ExportToXML_C(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 = ActiveWorkbook.Worksheets("BorangC2007")
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, "<" & sName & ">"
For i = 2 To lRows
If Trim(Cells(i, 1).Value) = "" Then Exit For

For j = 1 To lCols

If Trim(Cells(i, j).Value) <> "" Then

Print #iFileNum, Trim(Cells(i, j).Value)

DoEvents 'OPTIONAL
End If
Next j

Next i

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

This codes together with some others codes i have were saved as an Add-
in.
The issue is the above udf only works in excel at cell say E1(as a
UDF). I am trying to incorporate the function into another vba module
like below but I keep getting "Run-time error 1004 Macro cannot be
found".

Sub Run_ExportToXML_C()

ThisWorkbook.Sheets("BorangC2007").Copy
After:=ActiveWorkbook.Sheets("FormC")

With Sheets("BorangC2007")
..Range("D2").Formula = "=IF(ISBLANK('FormC'!R[1]C),"""",UPPER('FormC'!
R[1]C))"
..Range("D3").Formula = "=IF(ISBLANK('FormC'!RC[9]),"""",UPPER('FormC'!
RC[9]))"
..Range("D4").Formula = "=IF(ISBLANK('FormC'!
R[1]C),"""",TEXT(SUBSTITUTE('FormC'!R[1]C,""-"",""""),""0""))"

..Range("E1").Application.Run "ExportToXML_C(""D:\My Documents\Form C
2007.xml"",RC[-4])"

End With
End Sub

Both Private Function ExportToXML_C and Sub Run_ExportToXML_C() are in
the same add-in.
Any help to make it work in vb is very much appreciated.- Hide quoted text -

- Show quoted text -

 

[SauQ]

I have tried as your suggest and it works. Thanks heap!
I am still puzzled as to why Private Function ExportToXML_C only seems
to work when excel sheet is physically activated / selected (I did try
with ….

Sheets("xyz").Select
Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml",
"1:1")

earlier and got it converting data to XML correctly but not …

With Sheets("xyz")
.Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml", "1:1")
End with

Some IT experts in my dept said the codes in Private Function
ExportToXML_C is only
meant to work in Excel and not through VB. I don't quite get it
though.

Anyway, many thanks to you for the effort and time on posting this
reply.

I have two comments.

1) I hate using On error statements because when you do have errors it masks
these errors.  I comment out the On error statements when I have problems so
that the real error messages get displayed.

2) The active worksheets may not be the correct worksheet.  When you call a
function from a worksheet it is automatically the active worksheet.  When yu
call the same function from VBA it may not be the active worksheet.  Youhave
statments like the following

If Trim(Cells(i, j).Value) <> "" Then

I would do the following in Sub Run_ExportToXML_C() :

sheets("xyz").activate    '< add, change worksheet name
Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")

Thanks Joel !

I have tried ...

Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")

and the run-time error message doesn't pop up anymore, however when I
run the Sub Run_ExportToXML_C(), my data didn't get converted to XML
as intended i.e I only got a blank converted XML sheet.

any ideas / solutions ?

Many thanks to anyone who can help me on this.

In excel ExportToXML_C is a function just as if you used trim().  You dont
need any quotes or application Run.  Because your two pararmeter arestrings,
they need to be in a single pair of double quotes.  Notice I remove the
double set of double quotes.
Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")
:
Hi everyone,
I am still a learner in excel / vba.  I am using XP pro and Microsoft
office 2003.
I have copied this UDF code (Private Function ExportToXML_C) that
exports my current data in an excel spreadsheet into an XML format.
Private Function ExportToXML_C(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 = ActiveWorkbook.Worksheets("BorangC2007")
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, "<" & sName & ">"
For i = 2 To lRows
If Trim(Cells(i, 1).Value) = "" Then Exit For
    For j = 1 To lCols
        If Trim(Cells(i, j).Value) <> "" Then
           Print #iFileNum, Trim(Cells(i, j).Value)
           DoEvents 'OPTIONAL
        End If
    Next j
Next i
Print #iFileNum, "</" & sName & ">"
ExportToXML_C = True
ErrorHandler:
If iFileNum > 0 Then Close #iFileNum
Exit Function
End Function
This codes together with some others codes i have were saved as an Add-
in.
The issue is the above udf only works in excel at cell say E1(as a
UDF).  I am trying to incorporate the function into another vba module
like below but I keep getting "Run-time error 1004 Macro cannot be
found".
Sub Run_ExportToXML_C()
ThisWorkbook.Sheets("BorangC2007").Copy
After:=ActiveWorkbook.Sheets("FormC")
With Sheets("BorangC2007")
..Range("D2").Formula = "=IF(ISBLANK('FormC'!R[1]C),"""",UPPER('FormC'!
R[1]C))"
..Range("D3").Formula = "=IF(ISBLANK('FormC'!RC[9]),"""",UPPER('FormC'!
RC[9]))"
..Range("D4").Formula = "=IF(ISBLANK('FormC'!
R[1]C),"""",TEXT(SUBSTITUTE('FormC'!R[1]C,""-"",""""),""0""))"
..Range("E1").Application.Run "ExportToXML_C(""D:\My Documents\Form C
2007.xml"",RC[-4])"
End With
End Sub
Both Private Function ExportToXML_C and Sub Run_ExportToXML_C() are in
the same add-in.
Any help to make it work in vb is very much appreciated.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Joel]

Tell your IT experts they are wrong. I occasionally test my UDF functions
using a VBA subroutines but it is nice that you can use the same function in
two differrent ways. What does your IT experts recommend. Write two
functions that have to do the same thing. Then when you make a change you
have to make the same change in two different sets of code. Doesn't make
sense.

It is not the functions that don't work when they are not selected it is the
cell references that may not work correctly. that is why I try to use the
worksheet name along with the cell reference like

with sheets("Sheet1")
.Range("A1")

end with

Functions you need to be more flexiblebecause you want them to work in more
than one sheet. Therefore, when you call one of these functions from another
function or worksheet you need to select the sheet.

I have tried as your suggest and it works. Thanks heap!
I am still puzzled as to why Private Function ExportToXML_C only seems
to work when excel sheet is physically activated / selected (I did try
with ….

Sheets("xyz").Select
Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml",
"1:1")

earlier and got it converting data to XML correctly but not …

With Sheets("xyz")
.Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml", "1:1")
End with

Some IT experts in my dept said the codes in Private Function
ExportToXML_C is only
meant to work in Excel and not through VB. I don't quite get it
though.

Anyway, many thanks to you for the effort and time on posting this
reply.

I have two comments.

1) I hate using On error statements because when you do have errors it masks
these errors. I comment out the On error statements when I have problems so
that the real error messages get displayed.

2) The active worksheets may not be the correct worksheet. When you call a
function from a worksheet it is automatically the active worksheet. When yu
call the same function from VBA it may not be the active worksheet. You have
statments like the following

If Trim(Cells(i, j).Value) <> "" Then

I would do the following in Sub Run_ExportToXML_C() :

sheets("xyz").activate '< add, change worksheet name
Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")

Thanks Joel !

I have tried ...

Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")

and the run-time error message doesn't pop up anymore, however when I
run the Sub Run_ExportToXML_C(), my data didn't get converted to XML
as intended i.e I only got a blank converted XML sheet.

any ideas / solutions ?

Many thanks to anyone who can help me on this.

In excel ExportToXML_C is a function just as if you used trim(). You dont
need any quotes or application Run. Because your two pararmeter are strings,
they need to be in a single pair of double quotes. Notice I remove the
double set of double quotes.

Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")

:
Hi everyone,

I am still a learner in excel / vba. I am using XP pro and Microsoft
office 2003.

I have copied this UDF code (Private Function ExportToXML_C) that
exports my current data in an excel spreadsheet into an XML format.

Private Function ExportToXML_C(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 = ActiveWorkbook.Worksheets("BorangC2007")
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, "<" & sName & ">"
For i = 2 To lRows
If Trim(Cells(i, 1).Value) = "" Then Exit For

For j = 1 To lCols

If Trim(Cells(i, j).Value) <> "" Then

Print #iFileNum, Trim(Cells(i, j).Value)

DoEvents 'OPTIONAL
End If
Next j

Next i

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

This codes together with some others codes i have were saved as an Add-
in.
The issue is the above udf only works in excel at cell say E1(as a
UDF). I am trying to incorporate the function into another vba module
like below but I keep getting "Run-time error 1004 Macro cannot be
found".

Sub Run_ExportToXML_C()

ThisWorkbook.Sheets("BorangC2007").Copy
After:=ActiveWorkbook.Sheets("FormC")

With Sheets("BorangC2007")
..Range("D2").Formula = "=IF(ISBLANK('FormC'!R[1]C),"""",UPPER('FormC'!
R[1]C))"
..Range("D3").Formula = "=IF(ISBLANK('FormC'!RC[9]),"""",UPPER('FormC'!
RC[9]))"
..Range("D4").Formula = "=IF(ISBLANK('FormC'!
R[1]C),"""",TEXT(SUBSTITUTE('FormC'!R[1]C,""-"",""""),""0""))"

..Range("E1").Application.Run "ExportToXML_C(""D:\My Documents\Form C
2007.xml"",RC[-4])"

End With
End Sub

Both Private Function ExportToXML_C and Sub Run_ExportToXML_C() are in
the same add-in.
Any help to make it work in vb is very much appreciated.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[SauQ]

So much clearer now.

Many thanks again, Joel.
Regards
SauQ

Tell your IT experts they are wrong.  I occasionally test my UDF functions
using a VBA subroutines  but it is nice that you can use the same function in
two differrent ways.  What does your IT experts recommend.  Write two
functions that have to do the same thing.  Then when you make a change you
have to make the same change in two different sets of code.  Doesn't make
sense.

It is not the functions that don't work when they are not selected it is the
cell references that may not work correctly.  that is why I try to use the
worksheet name along with the cell reference like

with sheets("Sheet1")
   .Range("A1")

end with

Functions you need to be more flexiblebecause you want them to work in more
than one sheet.  Therefore, when you call one of these functions from another
function or worksheet you need to select the sheet.

I have tried as your suggest and it works.  Thanks heap!
I am still puzzled as to why Private Function ExportToXML_C only seems
to work when excel sheet is physically activated / selected (I did try
with ….

Sheets("xyz").Select
    Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml",
"1:1")

earlier and got it converting data to XML correctly but not …

With Sheets("xyz")
.Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml", "1:1")
End with

Some IT experts in my dept said the codes in Private Function
ExportToXML_C is only
meant to work in Excel and not through VB.  I don't quite get it
though.

Anyway, many thanks to you for the effort and time on posting this
reply.

I have two comments.
1) I hate using On error statements because when you do have errors itmasks
these errors.  I comment out the On error statements when I have problems so
that the real error messages get displayed.
2) The active worksheets may not be the correct worksheet.  When youcall a
function from a worksheet it is automatically the active worksheet.  When yu
call the same function from VBA it may not be the active worksheet.  You have
statments like the following
If Trim(Cells(i, j).Value) <> "" Then
I would do the following in Sub Run_ExportToXML_C() :
sheets("xyz").activate    '< add, change worksheet name
Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")
:
Thanks Joel !
I have tried ...
Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")
and the run-time error message doesn't pop up anymore, however when I
run the Sub Run_ExportToXML_C(), my data didn't get converted to XML
as intended i.e I only got a blank converted XML sheet.
any ideas / solutions ?
Many thanks to anyone who can help me on this.
In excel ExportToXML_C is a function just as if you used trim().  You dont
need any quotes or application Run.  Because your two pararmeterare strings,
they need to be in a single pair of double quotes.  Notice I remove the
double set of double quotes.
Range("E1") = ExportToXML_C("D:\My Documents\Form C 2007.xml","1:1")
:
Hi everyone,
I am still a learner in excel / vba.  I am using XP pro and Microsoft
office 2003.
I have copied this UDF code (Private Function ExportToXML_C) that
exports my current data in an excel spreadsheet into an XML format.
Private Function ExportToXML_C(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 = ActiveWorkbook.Worksheets("BorangC2007")
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, "<" & sName & ">"
For i = 2 To lRows
If Trim(Cells(i, 1).Value) = "" Then Exit For
    For j = 1 To lCols
        If Trim(Cells(i, j).Value) <> "" Then
           Print #iFileNum, Trim(Cells(i, j).Value)
           DoEvents 'OPTIONAL
        End If
    Next j
Next i
Print #iFileNum, "</" & sName & ">"
ExportToXML_C = True
ErrorHandler:
If iFileNum > 0 Then Close #iFileNum
Exit Function
End Function
This codes together with some others codes i have were saved as an Add-
in.
The issue is the above udf only works in excel at cell say E1(asa
UDF).  I am trying to incorporate the function into another vba module
like below but I keep getting "Run-time error 1004 Macro cannot be
found".
Sub Run_ExportToXML_C()
ThisWorkbook.Sheets("BorangC2007").Copy
After:=ActiveWorkbook.Sheets("FormC")
With Sheets("BorangC2007")
..Range("D2").Formula = "=IF(ISBLANK('FormC'!R[1]C),"""",UPPER('FormC'!
R[1]C))"
..Range("D3").Formula = "=IF(ISBLANK('FormC'!RC[9]),"""",UPPER('FormC'!
RC[9]))"
..Range("D4").Formula = "=IF(ISBLANK('FormC'!
R[1]C),"""",TEXT(SUBSTITUTE('FormC'!R[1]C,""-"",""""),""0""))"
..Range("E1").Application.Run "ExportToXML_C(""D:\My Documents\Form C
2007.xml"",RC[-4])"
End With
End Sub
Both Private Function ExportToXML_C and Sub Run_ExportToXML_C() are in
the same add-in.
Any help to make it work in vb is very much appreciated.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -