Problems with Excel 2007 user defined functions

[clifftech]

I have a spreadsheet with user defined functions that I developed and work fine in Excel 2003 but will not work in Excel 2007.

I just started using Excel 2007 (not my choice) so are there settings in 2007 that I need to make for using UDF or is there something in the code that is wrong?

Here is the function:


Function ListWS(sWB As String, i As Integer) As String
'This function is used to list the worksheets for a given spreadsheet

ListWS = Workbooks(sWB & ".xls").Worksheets(i).Name

End Function


Thanks for the help

 

[ck1999]

Has the workbook been converted to excel 2007 if so the file extension should be xlsx or xlsm?

It works on my computer

ck1999

 

[clifftech]

After playing with the spreadsheet for about a day I finally figured out the file extension makes a difference. This is a big difference from 2003.

Now I am having difficulty importing the spreadsheet to Access so I guess it will take another day to find out the differences!

Thanks for your help.

 

[HughLerwill]

Could be that one day you will want to use the new Excel file formats. Right now it may be better for you to just setup Excel 2007 so that it saves files in the old .xls format by default. To do that;

Click the Office button at top left - dialog opens
Click the Excel options button bottom right - dialog opens
Click Save(options) - dialog opens
Adjust 'Save files in this format'

 

[clifftech]

The problem I'm having with importing the spreadsheet seems to be an Access 2007 problem. The spreadsheet has about 180 columns and 610 rows. When I import the spreadsheet the table that it generates gets the rows out of sequence. The first row of the resulting table is about row 45 of the spreadsheet with the first 44 rows of the spreadsheet moved to the bottom rows of the table!

I'm looking at issues such as merged cells, etc. but can't figure this one out.

 

[ck1999]

How are you importing the table? Are you importing from access or using code?

ck1999

 

[clifftech]

From Access using the wizard and with VBA code within Access.