dindongool20
04-04-2011, 01:06 AM
Last post we talked about how to use Excel as a flexible development environment and touched on the benefits of using Office graphics to build your UI. This post goes into how to use ActiveX controls in Excel and how to contain and manipulate data in tables to drive your application. Using ActiveX Controls
First we need to enable that hidden Developer Tab, Click Office Button, Excel Options and Toggle 'Show Developer tab in the Ribbon' on.
Now click Developer Tab, Insert, on the bottom right corner is a 'More Controls' option. Here you will find a large variety of controls from File Browsers to Calendar controls, buttons, Internet Explorer; you can even register your own custom controls for using in Excel.
We require music, let's search for Windows Media Player and draw it on the grid. This provides us with full media player functionality as well as access to its object model in VBA. The full Media Player functionality is out of scope but you can learn more by looking at the reference here: but we are going to briefly talk about tying our custom UI to control to it.
Let's use the Play button as an example: Right click on the your 'play' button and click 'Assign Macro'. Since I named my play button 'Play' the default name is perfect 'Play_Click', click new. This will open VBA which you can access at any time by pressing Alt + F11.
To begin using our control let's start simply by accessing pressing play by adding the following to your Play_Click method.
Sheet1.WindowsMediaPlayer1.Controls.Play
WindowsMediaPlayer1.Controls will contain almost all the media control functionality we need, but let's add some volume control. On the developer tab insert a scroll bar. Right click the scrollbar and click properties. Volume usually goes from 0-100, set this to min and max. Using the selection pane, rename the scrollbar to 'Volume' and finally double click the scrollbar and add this code:
Sheet1.WMP.settings.Volume = Sheet1.Volume.Value
That is all it takes to control volume. Before we go any further we need music to play, we have two options. Either search directories or grab the information directly from the Media Player. To further demonstrate ActiveX we'll be doing the latter, but first we need a place to store this data, what better place than a Table. Using Tables for data manipulation
Ok, so now we have our basic front end UI, and a basic understanding on how to use ActiveX in our project, let's add a few tables and some data so that this media player can do something. In the application I have 2 main tables, one called library and one called playlist.
To create a table, Select your range and press ctrl + T, or insert Tab,Ttable. I added a table that contains headers for all the useful information I might want to know about media, most important to us now are ID, Title, Artist, and Source URL. To populate this table I used Windows Media Player again, you can browse the directory directly but to further elaborate on how to use ActiveX with Tables I decided to do it this way:
Dim MC, i
Dim LList As ListObject
Set LList = Sheets("Library").ListObjects("Library")
Set MC = Sheets("player").WMP.mediaCollection.getAll
Sheets("settings").ListObjects("playlist").DataBodyRange.Delete
LList.DataBodyRange.Delete
For i = 0 To MC.Count - 1
LList.ListRows.Add().Range.Value = Array(i, _
MC.Item(i).getItemInfo("name"), _
MC.Item(i).getItemInfo("artist"), _
MC.Item(i).getItemInfo("album"), _
MC.Item(i).getItemInfo("sourceURl"), _
MC.Item(i).getItemInfo("duration"), _
MC.Item(i).getItemInfo("genre"), _
MC.Item(i).getItemInfo("filetype"),Office 2010 Key (http://www.msoffice2010key.com/), _
MC.Item(i).getItemInfo("UserPlayCount"), _
MC.Item(i).getItemInfo("AlbumPicture"),Windows 7 (http://www.key-windows-7.de/), _
MC.Item(i).getItemInfo("UserRating"), _
MC.Item(i).getItemInfo("UserLastPlayedTime"), _
MC.Item(i).getItemInfo("FileSize"))
Application.StatusBar = "Now Importing Item " & i & " of " & MC.Count
Next i
First off, manipulating data in a table is much easier than doing the same in a simple range. Tables give you the ability to add calculated columns, reference columns by their name rather than location, add a row to the end as a simple array, and treat the entire table as an object,Microsoft Office 2010 Key (http://www.msoffice2010key.net/), rather than a piece of your grid. This is just a touch of what tables can do for you. You can find more information here.
For the most part you can rely on ListObject.ListRows.Add() which will (by default) return the last ListRow in your table. In the above example I am setting the array by passing the value an array of strings that I get from the Windows Media Player media collection object.
You can learn more about the Table/Listobject OM here.
Now we have a list of songs as populated from our ActiveX control, let's add a few simple functions that will allow us to create playlists. We will start with a playlist Table, located on the Settings Sheet. We need to do 3 simple things with this table: Shuffle, Next, and Previous.
Shuffling a table is very simple now with the update that we have done to the Sort Object model. You can now apply a sort in the UI and with a simple OM call of range.Sort.Apply() it will reapply the saved sort state. Simply add a column with the function =Rand(), sort it and add a VBA method 'shuffle' that does just that.
Previous and Next are made much simpler with the addition of tables in Excel 2003:
Dim PList As ListObject
Set PList = Sheets("Settings").ListObjects("Playlist")
PList.ListRows.Add 1
Intersect(PList.ListRows(PList.ListRows.Count).Ran ge, PList.ListColumns("ID").Range).Copy
Intersect(PList.ListRows(1).Range, PList.ListColumns("ID").Range).Select
Sheets("Settings").Paste
PList.ListRows(PList.ListRows.Count).Delete
This just adds a new row to the top of the ListObject, then moves the last ID to that row. Calculated columns auto fill the rest of the table for me with a few =VLOOKUP([ID],Office 2007 Professional (http://www.office-2007-key.co.uk/),Library,2) to return artist info and the = Rand() for shuffle. Look at the playlist on the settings page for full details.
To get the path of the file, it is another simple VLookup formula: =VLOOKUP(PlayingID,Office 2007 Serial (http://www.office2007key.eu/),Library[#Data],5) where playingID is the first row in my playlist and Library[#Data] is my entire library. I add this to a settings range in my worksheet and launch the Name manager (Ctrl + F3) so that I create a new named range called "loadFile".
Now that we have a playlist, can manipulate it with tables and know where my data/music is located, loading it is simple: Sheets("Player").WMP.URL = Range("loadFile").Value
And that is it! You now have a fully functioning media player built out of Excel! Next post will show you how to polish your UI by customizing the ribbon, adding custom context menus. <div
First we need to enable that hidden Developer Tab, Click Office Button, Excel Options and Toggle 'Show Developer tab in the Ribbon' on.
Now click Developer Tab, Insert, on the bottom right corner is a 'More Controls' option. Here you will find a large variety of controls from File Browsers to Calendar controls, buttons, Internet Explorer; you can even register your own custom controls for using in Excel.
We require music, let's search for Windows Media Player and draw it on the grid. This provides us with full media player functionality as well as access to its object model in VBA. The full Media Player functionality is out of scope but you can learn more by looking at the reference here: but we are going to briefly talk about tying our custom UI to control to it.
Let's use the Play button as an example: Right click on the your 'play' button and click 'Assign Macro'. Since I named my play button 'Play' the default name is perfect 'Play_Click', click new. This will open VBA which you can access at any time by pressing Alt + F11.
To begin using our control let's start simply by accessing pressing play by adding the following to your Play_Click method.
Sheet1.WindowsMediaPlayer1.Controls.Play
WindowsMediaPlayer1.Controls will contain almost all the media control functionality we need, but let's add some volume control. On the developer tab insert a scroll bar. Right click the scrollbar and click properties. Volume usually goes from 0-100, set this to min and max. Using the selection pane, rename the scrollbar to 'Volume' and finally double click the scrollbar and add this code:
Sheet1.WMP.settings.Volume = Sheet1.Volume.Value
That is all it takes to control volume. Before we go any further we need music to play, we have two options. Either search directories or grab the information directly from the Media Player. To further demonstrate ActiveX we'll be doing the latter, but first we need a place to store this data, what better place than a Table. Using Tables for data manipulation
Ok, so now we have our basic front end UI, and a basic understanding on how to use ActiveX in our project, let's add a few tables and some data so that this media player can do something. In the application I have 2 main tables, one called library and one called playlist.
To create a table, Select your range and press ctrl + T, or insert Tab,Ttable. I added a table that contains headers for all the useful information I might want to know about media, most important to us now are ID, Title, Artist, and Source URL. To populate this table I used Windows Media Player again, you can browse the directory directly but to further elaborate on how to use ActiveX with Tables I decided to do it this way:
Dim MC, i
Dim LList As ListObject
Set LList = Sheets("Library").ListObjects("Library")
Set MC = Sheets("player").WMP.mediaCollection.getAll
Sheets("settings").ListObjects("playlist").DataBodyRange.Delete
LList.DataBodyRange.Delete
For i = 0 To MC.Count - 1
LList.ListRows.Add().Range.Value = Array(i, _
MC.Item(i).getItemInfo("name"), _
MC.Item(i).getItemInfo("artist"), _
MC.Item(i).getItemInfo("album"), _
MC.Item(i).getItemInfo("sourceURl"), _
MC.Item(i).getItemInfo("duration"), _
MC.Item(i).getItemInfo("genre"), _
MC.Item(i).getItemInfo("filetype"),Office 2010 Key (http://www.msoffice2010key.com/), _
MC.Item(i).getItemInfo("UserPlayCount"), _
MC.Item(i).getItemInfo("AlbumPicture"),Windows 7 (http://www.key-windows-7.de/), _
MC.Item(i).getItemInfo("UserRating"), _
MC.Item(i).getItemInfo("UserLastPlayedTime"), _
MC.Item(i).getItemInfo("FileSize"))
Application.StatusBar = "Now Importing Item " & i & " of " & MC.Count
Next i
First off, manipulating data in a table is much easier than doing the same in a simple range. Tables give you the ability to add calculated columns, reference columns by their name rather than location, add a row to the end as a simple array, and treat the entire table as an object,Microsoft Office 2010 Key (http://www.msoffice2010key.net/), rather than a piece of your grid. This is just a touch of what tables can do for you. You can find more information here.
For the most part you can rely on ListObject.ListRows.Add() which will (by default) return the last ListRow in your table. In the above example I am setting the array by passing the value an array of strings that I get from the Windows Media Player media collection object.
You can learn more about the Table/Listobject OM here.
Now we have a list of songs as populated from our ActiveX control, let's add a few simple functions that will allow us to create playlists. We will start with a playlist Table, located on the Settings Sheet. We need to do 3 simple things with this table: Shuffle, Next, and Previous.
Shuffling a table is very simple now with the update that we have done to the Sort Object model. You can now apply a sort in the UI and with a simple OM call of range.Sort.Apply() it will reapply the saved sort state. Simply add a column with the function =Rand(), sort it and add a VBA method 'shuffle' that does just that.
Previous and Next are made much simpler with the addition of tables in Excel 2003:
Dim PList As ListObject
Set PList = Sheets("Settings").ListObjects("Playlist")
PList.ListRows.Add 1
Intersect(PList.ListRows(PList.ListRows.Count).Ran ge, PList.ListColumns("ID").Range).Copy
Intersect(PList.ListRows(1).Range, PList.ListColumns("ID").Range).Select
Sheets("Settings").Paste
PList.ListRows(PList.ListRows.Count).Delete
This just adds a new row to the top of the ListObject, then moves the last ID to that row. Calculated columns auto fill the rest of the table for me with a few =VLOOKUP([ID],Office 2007 Professional (http://www.office-2007-key.co.uk/),Library,2) to return artist info and the = Rand() for shuffle. Look at the playlist on the settings page for full details.
To get the path of the file, it is another simple VLookup formula: =VLOOKUP(PlayingID,Office 2007 Serial (http://www.office2007key.eu/),Library[#Data],5) where playingID is the first row in my playlist and Library[#Data] is my entire library. I add this to a settings range in my worksheet and launch the Name manager (Ctrl + F3) so that I create a new named range called "loadFile".
Now that we have a playlist, can manipulate it with tables and know where my data/music is located, loading it is simple: Sheets("Player").WMP.URL = Range("loadFile").Value
And that is it! You now have a fully functioning media player built out of Excel! Next post will show you how to polish your UI by customizing the ribbon, adding custom context menus. <div