Move bubbles! Making motion bubble chart in Excel

 

In my last two posts, I completed a series of steps to interpolate time series data, for example, monthly sales or sales performance, output the graph as an image, and create a movie from them.  In the last post, I created and introduced a macro file that further integrates these functions in a more user-friendly manner. This time, I would like to write about the addition of the "Motion Bubble Chart Creation Support Function" that was not done before.

　

Getting additional function

The previous version (called the integrated version) is available at the link.

https://diy360.booth.pm/items/3315820

This time, I have prepared a file (called an additional file) to add to the previous integrated version.

https://diy360.booth.pm/items/3315817

I have also prepared a combined version of the above integrated version and additional files.

I recommend it because it is a little more economical and there is no tedious sheet swapping process when using additional files.

https://diy360.booth.pm/items/3316008

How to use the additional files

Sheet contents and instructions for use

For the additional file, it consists of two sheets. One is "Read first", which is, well, general.

The other is the "Interpolation Results" sheet, which needs to be replaced with the sheet from the integrated version.

The procedure is as follows,

1. Open the integrated version and delete the interpolation result sheet.
2. Open this additional file, right-click on the sheet name, and select Move or Copy to copy the entire sheet to the integrated version above. The destination should be between the "Raw data" sheet and the "Output as mp4 video" sheet.
3. Close the additional file.
4. For the three newly added buttons (Create graph prototype of bubble chart (in case of coordinate variation), Display data label setting, Continuous processing of bubble chart (in case of coordinate variation)), right-click → Register macro → Select the macro of sheet2.OOOOOO (same name as the button)
5. Save and complete.

Data structure (X coordinate, Y coordinate, bubble size in that order)

In the case of the conventional "fixed coordinate version of bubble chart", each item data was lined up in one column.

On the other hand, in the case of this "bubble chart with variable coordinates" (motion bubble chart), column F is the same as before, with sequential numbers and time, but columns G and onward are composed as follows.

There should be a set of three rows each, GHI and JKL, MNO,,, with the X coordinate in the first row, the Y coordinate in the second row, and the size of the bubble in the third row. By keeping this as raw data and linearly interpolating, we can make it move smoothly in terms of X-coordinate, Y-coordinate, and size, respectively.

Introduction of the "Create bubble chart prototype (in case of varying coordinate)" button

By pressing this button, you can create a prototype of the bubble chart (using only the first line of data) to which you want to add movement. The script that works with this button, I won't explain everything, but it's like drawing a chart with only the first bubble with the following script first, and then adding bubbles to it.

    Range("G2:I2").Select 'If you don't select it once, Excel will try to draw the graph with the whole data first, which makes it slower.
    ActiveSheet.Shapes.AddChart2(269, xlBubble3DEffect).Select
    ActiveChart.SetSourceData Source:=Range("Interpolation_result!$G$2:$I$2")
    ActiveChart.FullSeriesCollection(1).Name = "=Interpolation_result!$H$1"

There is a reason why I have drawn the first one and am trying to add to it. At first, I created an empty chart box with AddChart and tried to add bubbles to it one by one, but then I couldn't automatically draw the sphere-like bubbles that are familiar to Excel bubble charts. Even though it's an xBubble3DEffect, it ends up looking like a flat fill. So, I dared to draw the first one with a box and add bubbles to it.

Also, the following is a good way to make the video version look beautiful.

    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = Cells(2, 6) & vbTab
    End With

The point is to add a Tab with vbTab when displaying the Time or No. In fact, with this bubble chart with fluctuating coordinates, when outputting it as a frame of animation, the left-justified text in the box of the graph title that displays Time or No. does not work, or maybe it does, but for some reason it dizzy left and right, making it difficult to see. But if I add this Tab, the dizzying stops. I don't know why. Well, the result is good.

Introduction of the "Data Label Display Settings" button

This button allows you to set the display of data labels. In the case of this format where the data is lined up horizontally with (X, Y, size) as a set, adding data labels is quite time-consuming because it has to be done manually one by one. Therefore, this function was added so that data labels can be added all at once even in this format.

Next to this button (around cell B28) is the following cell.

After setting these parameters, select the bubble chart and click the "Display Data Label Setting" button to reflect the label on the chart. As for the series name, the Y-coordinate item name (cell H1, K1, ...) will be reflected, so please set the contents of these cells to the contents you want to use as the label.

You can also choose the position of the label by selecting the triangle next to the data label from the + button that appears in the upper right corner when the graph is selected.

Introduction of the "Continuous processing of bubble chart (in case of varying coordinate)" button

This is the same as the previous one. Pressing this button while the bubble chart is selected (activated) will output a series of each animation frames.

I made a video of the whole process just in case.

Closing Words

This time, we have powered up the file introduced in the previous post to support motion bubble charts.

Again, I haven't said much about the contents of the scripts, but the basics are the same as in my last two posts. If you need the scripts, you can get them from the link to Booth, a file sales site, and check out the contents.

Sheets only (for those who already have the basic file)

• https://diy360.booth.pm/items/3315817

A high-function version (basic file + this new feature)

• https://diy360.booth.pm/items/3316008