Solved: Call a function from personal.xlsb

[cuccu]

Hi,
From a workbook named Test.xlsm I am trying to call a function defined in Personal.xlsb but there is a Run-time error '424': Object required.
Please point me out why it's wrong?

In Test.xlsm:

Sub the_Main()
Dim arr() As String
arr = PERSONAL.XLSB!List_xlsx_Files()
End Sub


In Personal.xlsb:
Public Function List_xlsx_Files() As String()
Dim Directory As String
Dim filename As String
Dim IndexSheet As Worksheet
Dim rw As Long

'Change the directory below as needed
Directory = "C\ABC\"
'Directory = ActiveWorkbook.Path
'Directory = FolderPath
If Right(Directory, 1) <> "\" Then
Directory = Directory & "\"
End If

rw = 1

'Set IndexSheet = ThisWorkbook.ActiveSheet

Dim temp() As String

filename = Dir(Directory & "*.xlsx")
Do While filename <> ""
rw = rw + 1
filename = Dir
Loop
ReDim temp(rw - 2)

rw = 1

filename = Dir(Directory & "*.xlsx")
Do While filename <> ""
'IndexSheet.Cells(rw, 1).Value = filename
temp(rw - 1) = Directory + filename
'Run DeleteBlankSheets(filename)
rw = rw + 1
filename = Dir
Loop

List_xlsx_Files = temp

Set IndexSheet = Nothing
End Function

[geekgirlau]

arr = Application.Run("Personal.xlsb!List_xlsx_Files")

[Bob Phillips]

Sub the_Main()
Dim arr As Variant
arr = Application.Run("PERSONAL.XLSB!List_xlsx_Files")
End Sub




Public Function List_xlsx_Files() As Variant
Dim Directory As String
Dim filename As String
Dim IndexSheet As Worksheet
Dim rw As Long
Dim temp As Variant

'Change the directory below as needed
Directory = "C:\personal\bob\_development\Excel\Ribbon\" '"C:\ABC\"
If Right(Directory, 1) <> "\" Then
Directory = Directory & "\"
End If

ReDim temp(1 To 1000)
rw = 0
filename = Dir(Directory & "*.xlsm")
Do While filename <> ""

rw = rw + 1
temp(rw) = Directory + filename
filename = Dir
Loop
ReDim Preserve temp(1 To rw)

List_xlsx_Files = temp

Set IndexSheet = Nothing
End Function

[cuccu]

Thank both of you a lot.