New Spreadsheet v1.68

Autoplay, reporting, all those clever things it does. Discuss.

Moderator: kirkm

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

New Spreadsheet v1.68

Postby kirkm » Mon Apr 14, 2014 3:26 pm

Hello all,

It's been nine months since the last macro spreadsheet and we're delighted to finally have a new one for you. It's also the reason for the seeming lack of activity for so long - as Tel & I pushed everything else to one side and strove to make the best Britburn yet !

The biggest change is the addition of the UK Album charts - a total of over 7000 new entries, most of them with full track listings, composers, durations, chart runs etc. But that's not all - to accompany the release a set of images and lyrics is also available - and to set the ball rolling, a post of the Top 3 albums 1956-1977 has been prepared and will be on the NGs over the next few days.

The album chart sheet is fully integrated with all the usual functions and click events. Other changes and feature are less obvious, but an almost complete re-working 'under the bonnet' has taken place, paving the way to eventually move from Excel to a new, better platform. We are still thwarted by issues with an .xlsm version and suggest you use the basic one, rather than Save As .xlsm.

All usual on-going data additions to date are included and entries to end of 1989 can now be considered permanent.

We're always keen to know of problems, and mindful of incompatibility with the later Windows and Excel versions. Please
let us know if anything doesn't work. It's quite possible bugs will arise. They'll be fixed immediately and logged in this forum. If you find data errors (or want to add something) any Backend editing you do now offers to send your changes to the our website (if you're online) where they can be added for everyone in the next version.

Hopefully you'll notice a speed improvement and general improved functionality. The Resource Check option is now much more solid and comprehensive. Checksum users will see a batch option to process a number of years at once.

Finally, check out the File Server as the LP images are loaded and also lyric files for those who prefer them in local folders. A non -macro version will also be there in a few days.

I'd like to thank Tel for the many hundreds of hours of hard and diligent work he's put into this - he's also been the main beta tester and devised a system for checking over 500 individual spreadsheet functions. Whew! And finding some issues we could fix before release.

http://www.britburn.co.uk/files/spreadsheet/BritburnSpreadsheetv1.68.zip

Cheers - Kirk

RonnieV
Top Forty
Posts: 32
Joined: Fri Aug 06, 2010 11:53 am

Re: New Spreadsheet v1.68

Postby RonnieV » Tue Apr 15, 2014 11:02 pm

One word...."brilliant!"

Thanks for the tremendous amount of work you guys put in this!

Cheers,
Ronnie

BoroButch
Number One
Posts: 71
Joined: Sat Jun 09, 2007 10:01 pm
Contact:

Re: New Spreadsheet v1.68

Postby BoroButch » Wed Apr 16, 2014 7:38 pm

Hi Kirk,

Thanks for latest spreadsheet, unfortunately I'm getting an error when I click on a song or prefix.

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

Re: New Spreadsheet v1.68

Postby kirkm » Thu Apr 17, 2014 3:47 am

BoroButch wrote: unfortunately I'm getting an error when I click on a song or prefix.


Well, that's no good!! Can you tell us more.. what the error says, and if your User Config is 'Active' ?

BoroButch
Number One
Posts: 71
Joined: Sat Jun 09, 2007 10:01 pm
Contact:

Re: New Spreadsheet v1.68

Postby BoroButch » Thu Apr 17, 2014 10:19 am

Yes spreadsheet is active. The error says "Invalid procedure call or argument", then when I click on debug I get this:
Which is in the bbPrefix (code), the error points to the line in red.

'Get Serial & subnumber
objRegExp.Pattern = "\d+"
If (objRegExp.Test(dat(1)) = True) Then
Set colMatches = objRegExp.Execute(dat(1))
pSerial = colMatches(0)
On Error Resume Next 'Ignore if subnumber absent
pSubNumber = colMatches(1)
On Error GoTo 0
End If

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

Re: New Spreadsheet v1.68

Postby kirkm » Thu Apr 17, 2014 10:43 am

Blimey... what OS and Excel version are you using? Normally that will not error as the line above it
is designed to ignore it. The variable pSubNumber is nothing (or an empty string) if colMatches(1) doesn't exist.
This has not shown up as a problem for us, and no-one else has it afaik. There'll be a reason and we'll figure it out. I'd like to test a few things on the same software as you though.

BoroButch
Number One
Posts: 71
Joined: Sat Jun 09, 2007 10:01 pm
Contact:

Re: New Spreadsheet v1.68

Postby BoroButch » Thu Apr 17, 2014 11:03 am

I've got Windows 7 and Excel 2010

BoroButch
Number One
Posts: 71
Joined: Sat Jun 09, 2007 10:01 pm
Contact:

Re: New Spreadsheet v1.68

Postby BoroButch » Thu Apr 17, 2014 11:18 am

It might be something my end, because I've just tried spreadsheet v1.41 and I'm getting same error. :(

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

Re: New Spreadsheet v1.68

Postby kirkm » Thu Apr 17, 2014 1:55 pm

And 1.41 was previously OK ? We'll check it out here over the weekend with Win7 and 2010 and make sure its OK. Or can someone with that combination advise if they have this issue or not?

BoroButch
Number One
Posts: 71
Joined: Sat Jun 09, 2007 10:01 pm
Contact:

Re: New Spreadsheet v1.68

Postby BoroButch » Fri Apr 18, 2014 11:28 pm

It's a long story and I'm not sure what I did but it seems to working okay now. I've had a few problems with my system recently and I think it's time for a full system recovery but I hate doing that so I'll live with it for now.
Anyway the good news is the spreadsheet is not the problem.

Triangulator
Number One
Posts: 113
Joined: Mon Jun 25, 2007 7:16 pm
Location: UK

Re: New Spreadsheet v1.68

Postby Triangulator » Sat Apr 19, 2014 9:36 am

Hi Guys, thanks for the new SS, just fired it up and got a compile error: "Can't find project or library"

I'm all using Windoze 7 and Excel 2010 - might be a link there somewhere!

Sheets("Britburn").Select
Application.WindowState = xlMaximized

'Check temp dir
If MyFolderExists(ReadCell("Config", celTempFolder)) = False Then
Worksheets("Config").Range(celTempFolder) = Environ("Temp")
End If

(The Environ variable was highlighted)

Anyways, I'll plod on and see if there's something I'm not doing right somewhere!

Cheers,
Triangulator

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

Re: New Spreadsheet v1.68

Postby kirkm » Sat Apr 19, 2014 11:09 am

Hi Robert,

The 2010/W7 combination I have to get my son to test (I've W7 but only Excel 2007). Looks like that is something else to tweak.

There'll be a revised spreadsheet tomorrow with the Album Import problem fixed, and another small change to Checksums. WIll sort this Environ thing at the same time... thanks for letting me know!

Cheers - Kirk

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

Re: New Spreadsheet v1.68

Postby kirkm » Sun Apr 20, 2014 4:37 am

Hi Robert,

Unfortunately the Britburn Spreadsheet is only compatible with 32-bit versions of MS Excel, and possibly not with Office 2010 or newer at all.

As The Britburn Spreadsheet is written in Visual Basic for Applications and Microsoft have basically discontinued support/development of this environment, this is unlikely to be resolved.

We are contemplating migrating the spreadsheet to VB.Net, but this will be an incredibly large project to undertake and we have limited manpower available.

http://en.wikipedia.org/wiki/Visual_Bas ... plications
With Office 2010, Microsoft has introduced VBA7 which now contains a true pointer data type: LongPtr. This new data type allows referencing 64-bit address space. The 64-bit install of Office 2010 does not support common controls of MSComCtl (TabStrip, Toolbar, StatusBar, ProgressBar, TreeView, ListViews, ImageList, Slider, ImageComboBox) or MSComCt2 (Animation, UpDown, MonthView, DateTimePicker, FlatScrollBar) so legacy 32-bit code ported to 64-bit VBA code that depends on these common controls will not function. The 32-bit version of Office 2010 is unaffected by this issue.[11] VBA7 includes no 64-bit version of the common controls, so it leaves developers with no means to migrate VBA applications to 64-bits. Microsoft suggests contacting the software vendor for 64-bit versions of VBA controls.


We have performed preliminary testing on the spreadsheet in an Office 2010 32-bit environment and found no issues.

Glinty
Number One
Posts: 161
Joined: Sat Jun 09, 2007 5:47 pm

Re: New Spreadsheet v1.68

Postby Glinty » Sun Apr 20, 2014 11:06 am

Hi Kirk,

I can confirm that, apart from the insert problem, the spreadsheet works fine on Windows 8 and Office Home and Student 2010 (not sure if this is 32 or 64 bit).

Thanks
Glyn

PS Just found the info, it is 32 Bit.

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

Re: New Spreadsheet v1.68

Postby kirkm » Sun Apr 20, 2014 11:59 am

Thanks Glyn, that supports our findings - that 32 bit systems are OK and 64 bit not OK. But I think 64 bit users also get the 32 bit programme ? Not sure...
The Insert (and Checksum) tweaks will be in v 1.681 which Terry is testing as I write this and we'll advice here when it goes up. Expected today.

Nice to know it flys with Windows 8 though.. that's a bit hard to do this end...


Return to “The Spreadsheet”

Who is online

Users browsing this forum: No registered users and 1 guest