27 January, 2022

Using Excel as Music Player

Do you like to listen in the background to your favourite music while working outside Excel, e.g. in Word, or on your emails? If so, you can use Excel as a music player. To prepare for that you need to create a music library, collection of your music files, located e.g. in MyMusic subdirectory, as in my example.

Having done that, next create in your Excel worksheet a list of Hyperlinks to those files (by using CTRL+K shortcut for each of your music files). The list can be arranged in any order and contain as many hyperlinks as needed. Here is a short example of my list:


To play your list, create the macro shown below. Just copy it to a module of your workbook's VBAProject (open it with ALT+F11 shortcut).

Sub PlayAllMusic()
Dim i As Integer
Dim sFile As String
Application.DisplayAlerts = False  'Turns off alerts
sFile = Range("A1").Value
i = 1
Do While sFile <> ""
    Application.StatusBar = "Loading/playing " & sFile  'Displays message on Status Bar
    Worksheets(1).Range("A" & i).Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Application.Wait (Now + TimeValue("00:05:00"))    'Reserved time per one piece of music
    i = i + 1
    sFile = Range("A" & i).Value
Loop
Application.StatusBar = False
Application.DisplayAlerts = True  'Turns back on alerts

End Sub

The macro assumes that your list of hyperlinks starts in cell A1 and contains no empty cells within the list. The TimeValue argument in Wait function reserves five minutes for playing each of the music files. These variables can be changed to fit your specific needs.

The last thing you may need to create is a form Control button, similar to that displayed above. After inserting on your worksheet, right-click the button, format it as you want to, right-click again and select "Assign macro..." option to assign the macro "PlayAllMusic" to it.

I'm using Windows Media Player for playing my music files, but some other players should work fine as well. If everything works as intended, click the button to play your list...


No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.