Part 6: Using Macros to Streamline your Runescape Excel Hiscores Tracker

Welcome to part six of my guide on using Excel to track your Runescape Hiscores. We've already created three tables, which record daily XP, work out your XP to the next level and determine your overall averages. This time, we will be running some macros to make them even more convenient to consult.

Please note that once you add macros into the equation, you will need to save your Excel workbook in a different mode. You'll be prompted to do this when you come to save it anyway, but the correct filename is: Excel Macro-Enabled.

Runescape Excel Hiscores Tracker: The Story So Far

This guide is written in the assumption that your workbook looks exactly like mine. For that to be the case, then you will have had to follow the previous entries. All of the data referred to and the tables created are outlined in them.

I've been asked if this could be used for other on-line games too. Yes! As long as the hiscores of that game are on a website somewhere, you can use the techniques in this guide to create a tracker for those too. If you encounter any difficulties in creating a World of Warcraft Excel Hiscores Tracker or whatever, then just comment here and I'll see what I can do to help.

Part 1: How to Make a Runescape Hiscores Tracker in Microsoft Office Excel - Collecting data ready to feed your tables and charts.

Part 2: Averages Table in your Runescape Excel Hiscores Tracker - Creating the first table, which worked out your XP averages from highest to lowest skill. There was also a bonus section answering some formatting questions, mostly from those new to Excel.

Part 3: Calculating XP to Next Level in your Runescape Excel Hiscores Tracker - Creating a table to calculate the XP needed until your next level.

Part 4: Adding Activity Logs and More to your Runescape Excel Hiscores Tracker - Using the contents of websites to add data into Excel, notably a couple of ways to add your Runescape Activity Logs.

Part 5: Tracking Daily XP in your Runescape Excel Hiscores Tracker - Letting Excel calculate how much XP you had gained in Runescape today, as well as recording the levels gained and your rank changes.

But many of these tables are currently being sorted by hand. Let's change all of that by inserting a few macros!

Adding Macros to your Excel Runescape Hiscores Tracker

If you have never used macros before, then you will first have to access the tools to use them.

• Click the Excel logo in the top left-hand corner of your workbook.
• Click the button 'Excel Options' from the bottom right-hand corner of the pop up menu.
• Tick the box next to 'Show Developer Tab in the Ribbon'.
• Press 'Ok'.

Did you imagine the level up fireworks then? If so, then your reward for this level is to access macros. Congrats!

Using a Macro to Sort the Data in our Runescape Hiscores Averages Table

Until now, we have been either using a sort filter or else manually rearranging the data in our Hiscores Averages table. Half the time, we might mess up and accidentally sort the wrong columns. Retyping all those formulas is a pain, isn't it? It's time to make our life a lot easier.

• Open up the worksheet with your Averages Table in it.
• Click on the 'Developer' tab from the workbook's top toolbar.
• Open 'Macro Security' and select 'Enable all macros (not recommended; potentially dangerous code can run)'. The warning is correct, but we need it open before we can create one! We'll disable it again later.
• Press OK.
• Open 'Record Macro'.
• In 'Macro Name', type 'SortAverages'.
• Ensure that 'this workbook' is selected under 'store macro in'.
• Add a description if you want to.
• Press OK.

• Select the 'Home' tab from the top toolbar.
• Highlight cells Q2 to S26, ie the data under the skills, level and XP columns of your Averages table.
• Select 'sort and filter' from the 'Home' toolbar.
• Select 'custom sort'.
• Sort by Column S; sort on Values; order them Largest to Smallest.
• Press 'OK'.
• Return to the 'Developer' tab.
• Press 'Stop Recording' from the 'Code' category.

Congratulations! You just created your first macro. Ok, you can't see it yet, but it's saved in the background waiting for you to use. In short, Excel has just recorded actions that you took. In future, it can simply replay them.

Of course, this is no use, if you haven't got anything to press to cause it to replay.

• Click the cell where you want to insert a 'Sort' button. (I'm going for cell Q30.)
• Click 'Insert' in the 'Developer' tab.
• Select an icon or button. (I'm going for the very first that you see. It's called 'Button (Form Control)'.
• Type 'Sort' on the button. (You can pretty it up in the 'Home' tab, where all of the fonts and colours are.)
• Right-click the button and select 'Assign Macro'.
• Select 'SortAverages' from the list.
• Press OK.

Whenever you now press that button, it will replay your actions from earlier. The columns will be sorted.

Using a Macro to Sort the Data in our Runescape XP to Next Level Table

I had a sort filter already on my XP to Next Level Table. Before I start my macro, I'm going to remove that.

• Highlight cells N1 to O26.
• Click the 'Sort and Filter' button on the 'Home' toolbar.
• Select 'Filter' from the pull down menu.

Remember that I only did that because I had a filter active on it. If you didn't, then skip the above step. It is removed when the icon beside 'Filter' isn't highlighted.

In the first image, it's active. In the second image, it's not. You want it off.

Click on the 'Developer' tab.
• Open 'Record Macro'.
• In 'Macro Name', type 'SortXPNextLvl'.
• Ensure that 'this workbook' is selected under 'store macro in'.
• Add a description if you want to.
• Press OK.

• Select the 'Home' tab from the top toolbar.
• Highlight the XP to Next Level table.
• Select 'sort and filter' from the 'Home' toolbar.
• Select 'custom sort'.
• Sort by Column O (XP to Next Level); sort on Values; order them Smallest to Largest.
• Press 'OK'.
• Return to the 'Developer' tab.
• Press 'Stop Recording' from the 'Code' category.

Now you just have to add a button in precisely the same way as you did before, except that you assign your latest recorded macro to it.

My worksheet now looks like this:

Your buttons probably look a lot more artistic than mine! LOL

Using Excel Macros to Record Yesterday's Runescape Hiscore Data

In part five, I told you that macros could be used to track your daily XP on Runescape. This is the fabled option three, which will copy data to populate columns C, D and E on your Daily Tracker.

• Open your worksheet with the daily tracker table on it.
• If you hid the previous data, click B and F to highlight their respective columns. Right-click and select 'unhide'.
• Record a macro, entitled 'DailyXP', of you manually transferring the relevant data. (This was outlined as Option 1 in part five.)
• Stop recording.
• Hide your columns C, D and E again. (Highlight them, right-click and select 'hide'.)

You have now set a macro to do all of that sorting for you. You could insert a button, in the same way that you did to sort the other two tables, in you wished. However, we can make this even more automatic by asking Excel to run it for us.

Instructing a Macro to Run at a Certain Time

It is possible to ask Excel to run a macro at a specific time. This has obvious benefits for recording daily XP. It doesn't require you to remember to transfer your data.

Unfortunately there is a limitation too - the workbook has to be open at the time. For those people with enough Green awareness to switch their computer off at night, then this could constitute a problem. If you habitually play Runescape with your Excel Hiscores Tracker open, and finish at the same time each day/night, then there should be no problem at all.

• Click on the 'Developer' tab on Excel's top toolbar.
• Double-click 'View Code' in the 'Controls' category.

This area of Excel should come with a warning saying, 'Here there be dragons'. But we're all Runescape players, so a mere dragon holds no danger. Apply a mental dragonfire shield and sip some imaginary anti-fire potion and explore the coding section of Microsoft Office Excel.

On the left-hand side, you will see two lists under the heading VBAProject and the name of your workbook. Microsoft Excel Objects individually lists all of the worksheets. Modules has all of the macros that you've recorded.

If you double-click Module1, you'll see the Sort Averages code, the Sort XP to the Next Level code and the DailyXP code. All of these were created just now, as we recorded ourselves doing them. When Excel repeats our actions, it's merely reading what's in this module. This can be edited, if you wished.

That's just for information. To set our macro to run at a certain time, we need to place a bit of code in another sheet.

• Double-click 'ThisWorkbook' from the 'Microsoft Excel Objects' list.
• Copy and paste the following code into the pad that pops up:

Private Sub Workbook_Open()
Application.OnTime TimeValue("23:00:00"), "DailyXP"

End Sub

• Close the Visual Basic area by clicking on the X in the top right-hand corner.
• Save and close your Excel workbook.

We've closed the workbook because macros like these are only saved when you reopen the workbook once. Thereafter it will run without the need to close and open it again. So go ahead and do open up your Runescape Excel Hiscores Tracker again.

The code that we've inserted will cause the macro to run at 11pm every night. It takes the time from your own computer clock, so we don't have to worry about timezones here. But 11pm might not be the ideal time for you.

The part of that controlling time is ("23:00:00"). It runs in 24 hour clock - hour:minutes:seconds. If you want to change it, then open up the Visual Basic section again and double-click 'ThisWorkbook' to display the pad. Then you merely edit the time to one of your choosing.

Macros in our Excel Runescape Hiscores Tracker

There are a variety of ways in which macros can be used and set in Excel. If you can think of something else that you wish could be done automatically, then a macro can probably help. There are hundreds of websites devoted solely to walking you through macros, so just do a search to see if it's possible to do what you want to do. My recommendation is always to experiment!

I hope that you've found this section of the guide useful. Next time, I will be showing you how to add some XP and price calculators to your Runescape Hiscores Tracker.