Some days ago, someone posted in my favourite german office forum a question how to create an image gallery with rotating images in Excel VBA. The images may rotate in a circular orbit when the user clicks on an image. “Image 1” should take the position of “Image 2”, “Image 2” the position of “Image 3” and so on. Following image shows a screenshot of the sample Excel file which you can download at the end of this article and includes also the VBA Code.
Some users have presented a solution in the forum which alsoare working. I had the idea to implement a part of my solution by using Excel functions/formulas. The idea consists in creating a chart and then let the images follow the path drawn by the chart. I would also like to compare the different Excel versions 2003, 2007 and 2010.
In the first step, I had to create the formulas needed. For this I consulted the excellent formula collection on the german website, where you can find the formulas needed for creating a chart with an ellipse. An ellipse is a smooth closed curve which is symmetric about its center. The distance between antipodal points on the ellipse, or pairs of points whose midpoint is at the center of the ellipse, is maximum and minimum along two perpendicular directions, the major axis or transverse diameter, and the minor axis or conjugate diameter. An ellipse can be defined by using the equation:
x² / a² + y² / b² = 1
or, if you resolve this equation by y:
y = ±b/a*SQRT(a² - x²) where SQRT is the square root function.
The following figure shows the main parameters of an ellipse.
You can also the equations of the parametric form in canonical position to calculate the x and y values, then you may use x = a*cos(α) and y = b*sin(α). For creating the chart in Excel we need a list of angle values and the corresponding calculations. In column A, starting from line 16, I first create a simple list of angle values. These values increases per row by 1 degree. Then I used the cells in column B, C and D for the calculations. Here an example for the formulas used on row 17:
A17 = 1 Angle in degrees
B17 = RADIANS(A17) Calculated value in radians
C17 = SIN(B17)*$C$7 where $C$7 contains the value for “a”
D17 = COS(B17)*$C$8 where $C$8 contains the value for “b”
If I create a scatter chart with only markers, I get the result shown in the figure below:
Very nice. However in this case the ellipse can not be rotated. Fortunately, I also got help here on the website with the formula collection mentionned above. I just need to insert a new parameter for the rotation and modify my formulas. If I use cell C9 for this factor, you can rotate the ellipse by using following formulas:
E16 = C16*COS(RADIANS($C$9))-D16*SIN(RADIANS($C$9))
F16 = C16*SIN(RADIANS($C$9))+D16*COS(RADIANS($C$9))
Please note, that we have also to convert the rotation factor from degrees to radians. Then I get the following result shown in the figure below:
In my sample file, you will see, that I combined these two steps to one step. The image gallery should later contain 4 images and rotate them. For this, we have to compute the starting positions for each image. Ideally, the axes of the ellipse should also be calculated automatically and the points from the ellipse intersecting thes axes should be retrieved. The following figure shows the two axes D1 and D2. However, I have first inserted them manually.
The fomulas for calculating these points are really simple. I had just to calculate the sinus and cosinus from “a” and “b” and also consider the rotation.
D1_1_X = $C$7*COS(RADIANS($C$9))
D1_1_Y = $C$7*SIN(RADIANS($C$9))
The second point from the axis intersecting the ellipse is just opposite to the first one, so I can use D1_2_X = – D1_1_X and D1_2_Y = – D1_1_Y. For the scond axis D2, I have to rotate the angle by 90 degrees:
D2_1_X = $C$8*COS(RADIANS(90+$C$9))
D2_1_Y = $C$8*SIN(RADIANS(90+$C$9))
Then, I created new series for the points of the axes. The figure below shows how the 4 images will be placed on the sheet in their initial state.
The images mentionned here are just shapes which I had numbered from 1 to 4 and filled the backgrounds with different colors. Of course, you can replace them with real images. I named the shapes as “Image_1”, “Image_2”, “Image_3” and “Image_4” for referenciating them by name in VBA.
It would have been ideal if the coordinates of the points in the diagram could be retrieved directly. Unfortunately, there is no method for achieving this “points” object. However, it is possible to retrieve the positions of the associated data labels; they should have been activated before. For hiding the values in the data labels, I changed the number format to the custom format “•” as shown in the figure below. I also changed the background and foreground colors for the labels.
In a next step, I placed a rectangle behind the 4 images which will serve as a coordinate system for the pictures of carousel. To make the code a little smaller as well for testing purposes, I first write a function in VBA, which stores all coordinates of the data labels in the columns “R” and “S” starting from row 16 until row 376 (= 360 elements). The function “Public Function mlfpAnimateCalculate () As Long” performing this task can be found in the MLP_Animate module.
Then, it was easy to implement a function which reads these stores values and moves an image to these coordinates. Please note, that the retrieved values from the data labels are relative to the chart upper left corner. By adding these values to the upper left position of the rectangle behind the images, you will get an exact path of the ellipse. Here an excerpt of the code:
You can setup a zoom factor in the sample sheet, which multiplies with a calculated factor based on the vertical position of the image.
At this time, I got a problem when retrieving the coordinates of the data labels representing the points of the ellipse intersecting with the axes D1 and D2. In fact, when using the VLOOKUP() function, sometime an error occurred du to the precision of the calculated values. So, I truncated the values by modifiying my formulas as shown below:
E8 = TRUNC($X$8*$C$7*COS(RADIANS($C$9));0)/$X$8 with $X$8 = 10000
After doing that, it was possible to correctly retrieve the starting points (rows) for the images.
Finally, it was relatively easy to implement some functions for storing the current order for the images or add some controls to the sheet for setting up the parameters. And creating and implementing a UserForm using the values from the sheet was also very easy.
The last thing I have done was testing the solution in Excel 2003, 2007 and 2010. Unfortunately, the animation is very slow in Excel 2007 and Excel 2010; even after I introduced a factor for increasing the speed. This factor just bypasses some values in the animation loop. I discovered that it is possible to increase the speed a little bit by removing some “DoEvents” from the code. But I have not really a explanation why this happens in Excel 2007 and 2010; perhaps someone of you has an idea and likes to write me message. Finally the link to sample file:
Please note, that I can not give any warranty or something else when testing the code or playing with the sample file. I recommend to first close all other applications before and save your documents.
- Forum thread at Office-Lösung.de, German
- Excel Formeln, German
- Ellipses, Wikipedia, English
- Sinus and Cosinus, Wikipedia, English
This article has also been published in German and in English on the following sites: