loops Archives - HowtoExcel.net

Do you want to create a spinning wheel like in the video below?

In this post, I’ll show you how you can do this, with the help of visual basic. There are a couple of ways you can create a spinning wheel effect. I’ll go over both approaches, and share the code with you so that you can set it up in your own spreadsheet.

Create a spinning wheel effect by rotating an object

The easiest way to spin a wheel, or any object for that matter, is to rotate it. This can be done in visual basic. Before you get started, however, you need to know the name of the shape that you want to spin.

In the above screenshot, I have inserted a wheel into my spreadsheet, but you can use any image. In the top-left-hand corner, you’ll notice it says wheel1 — this is the name of the object. You change this to whatever you want. However, this is what you’ll need to reference in the macro when applying the spin effect.

Now, go into visual basic. This can be done using ALT+F11 shortcut. Then, you’ll need to go to the Insert option from the menu and click on Module.

Then in Module 1, copy the following code in:

Sub SpinEffect()
    Dim i As Long
    Dim wheel As Shape
    Set wheel = ActiveSheet.Shapes("wheel1")
      
     For i = 1 To 100 Step 1
        wheel.IncrementRotation 5
        DoEvents
    Next i
    

     For i = 1 To 100 Step 1
        wheel.IncrementRotation 3
        DoEvents
    Next i
    

     For i = 1 To 100 Step 1
        wheel.IncrementRotation 2
        DoEvents
    Next i
    
    
     For i = 1 To 500 Step 1
        wheel.IncrementRotation 1
        DoEvents
    Next i
End Sub

At the beginning of the code, I specify the name of my object — wheel1. This is where you need to update the code to reflect the name of your object.

The rest of the code is going through a series of loops. The first one is rotating the image by 5 degrees, then 3 degrees, then 2, and finally 1. The last loop goes through 500 steps and is the longest. You can adjust these to change the speed of the wheel’s rotation.

You can also insert a shape that links to this macro, so that it effectively becomes a button. In my example, I created a rectangle and added the text ‘SPIN’ onto it. If you go to the Insert menu on the Excel ribbon and select Shapes, you can create your own.

Once you’ve created a shape, you can assign a macro to it by right-clicking on the shape and selecting Assign Macro.

Then, select your macro and click on OK.

Now, anytime you click on the button, the macro will run, and your object will spin.

The one limitation about this method is that there is no way to know which value your wheel landed on. It spins, but there is no easy way to determine what it landed on. This is where the second method comes into play.

Creating a spin effect by changing visibility

This method is a bit more complex, but it addresses the main issues from the first approach, which is that you’ll known which value was selected.

I’m going to use the same wheel, but this time I’m going to make nine copies of it — one for each possible outcome. I will do the rotations myself and then just toggle the visibility using code. The setup can be a bit more tedious here because you’ll need to make sure the objects are on top of one another and that the rotations are precisely in the right position.

You’ll have to do this for each rotation and each object. You’ll also want to name each individual object so that you know which value it corresponds to.

Once the objects are all aligned, then you can insert the following code:

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub spin()

Dim wheel1 As Shape
Dim wheel2 As Shape
Dim wheel3 As Shape
Dim wheel4 As Shape
Dim wheel5 As Shape
Dim wheel6 As Shape
Dim wheel7 As Shape
Dim wheel8 As Shape
Dim wheel9 As Shape
Dim wheel10 As Shape

Set wheel1 = ActiveSheet.Shapes("wheel1")
Set wheel2 = ActiveSheet.Shapes("wheel2")
Set wheel3 = ActiveSheet.Shapes("wheel3")
Set wheel4 = ActiveSheet.Shapes("wheel4")
Set wheel5 = ActiveSheet.Shapes("wheel5")
Set wheel6 = ActiveSheet.Shapes("wheel6")
Set wheel7 = ActiveSheet.Shapes("wheel7")
Set wheel8 = ActiveSheet.Shapes("wheel8")
Set wheel9 = ActiveSheet.Shapes("wheel9")
Set wheel10 = ActiveSheet.Shapes("wheel10")

Dim i As Integer, j As Integer, cycle As Integer
    Dim winningNumber As Integer
    Dim delay As Long
    
    winningNumber = Int((9 * Rnd) + 1)
    
    ' 1. Initial Reset
    For i = 1 To 10
        ActiveSheet.Shapes("wheel" & i).Visible = msoFalse
    Next i

    ' --- STAGE 1: FAST (10 Cycles) ---
    delay = 10
    For cycle = 1 To 10
        For j = 1 To 10
            ActiveSheet.Shapes("wheel" & j).Visible = msoTrue
            DoEvents
            Sleep delay
            ActiveSheet.Shapes("wheel" & j).Visible = msoFalse
        Next j
    Next cycle

    ' --- STAGE 2: MEDIUM (10 Cycles) ---
    delay = 30
    For cycle = 1 To 10
        For j = 1 To 10
            ActiveSheet.Shapes("wheel" & j).Visible = msoTrue
            DoEvents
            Sleep delay
            ActiveSheet.Shapes("wheel" & j).Visible = msoFalse
        Next j
    Next cycle

    ' --- STAGE 3: SLOW (10 Cycles) ---
    delay = 40
    For cycle = 1 To 10
        For j = 1 To 10
            ActiveSheet.Shapes("wheel" & j).Visible = msoTrue
            DoEvents
            Sleep delay
            ActiveSheet.Shapes("wheel" & j).Visible = msoFalse
        Next j
    Next cycle


    ' --- STAGE 4: SLOW (10 Cycles) ---
    delay = 50
    For cycle = 1 To 10
        For j = 1 To 10
            ActiveSheet.Shapes("wheel" & j).Visible = msoTrue
            DoEvents
            Sleep delay
            ActiveSheet.Shapes("wheel" & j).Visible = msoFalse
        Next j
    Next cycle
    
    
    ' --- STAGE 5: SLOW (10 Cycles) ---
    delay = 60
    For cycle = 1 To 10
        For j = 1 To 10
            ActiveSheet.Shapes("wheel" & j).Visible = msoTrue
            DoEvents
            Sleep delay
            ActiveSheet.Shapes("wheel" & j).Visible = msoFalse
        Next j
    Next cycle
    
    
    ' --- FINAL LAP: STOP ON WINNER ---
    ' Even slower for the "crawl" to the finish line
    delay = 70
    For j = 1 To winningNumber
        ActiveSheet.Shapes("wheel" & j).Visible = msoTrue
        DoEvents
        Sleep delay
        
        ' Only hide if it's not the final winning shape
        If j < winningNumber Then
            ActiveSheet.Shapes("wheel" & j).Visible = msoFalse
        End If
    Next j

    MsgBox "The wheel stopped on Wheel " & winningNumber & "!"

End Sub

This code is longer and how it works is it determines the winning value at the beginning, based on a random number generator. Then the macro goes through loops to change the visibility of all the wheels, eventually revealing the winning one at the end.

If you liked this post on How to Create a Spinning Wheel in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on X and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Doing loops in VBA is very useful because it allows you to go through a range of cells, evaluate each, and then take an action if a specified criteria is met. I am going to go through a couple of examples to show you how you could do this to highlight cells and also delete rows.

For a brief intro to macros check out my earlier post about how to access VBA and record macros.

I have a data set below that shows population by country in the year 2015:

First, what I will do is do a loop to evaluate each country’s population for 2015, and for any that had more than 1,000,000 people, highlight the country name in column A.

There are a number of ways to do loops in VBA but I prefer using the For…Each loop.

The key thing is first to define the range of cells I want the loop to be applied to. I can use Excel’s COUNTA function to count the range. To do so, I first setup a variable called totalcount:

totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))

WorksheetFunction allows me to use Excel functions that are accessible in a normal spreadsheet and are accessible without VBA, in this instance, COUNTA. The totalcount variable now tells me how big my range needs to be; to what row I need my loop to go to.

I created a couple of additional variables as well: cl – this is the specific cell that will be evaluated when the loop is in progress; ws – this is just the worksheet which allows me to shortcut having to spell out the name of the worksheet each time (in this case it is just the default Sheet1). Below are my variable declarations:

Dim cl As Range

Dim totalcount As Integer

Dim ws As Worksheet

Set ws = Worksheets(“Sheet1”)

totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))

The first line in my loop specifies the range that I am going to cycle through:

For Each cl In ws.Range(“A2:A” & totalcount)

The above can be interpreted as saying that I am going to cycle through each cell from the range A2 until A and whatever the totalcount is equal to. I have to close the loop as well, so the last line of the loop is as follows:

Next cl

The point of the above line is that once I finish evaluating the cell and making whatever changes I need I now move onto the next cell to evaluate. This variable does not have to be called cl, it just makes it easier to think of instead of next a or b let’s say.

So that is the loop. But without anything inbetween the start and end of the loop all the sub procedure is going to do is go through every cell in that range and do nothing. So as mentioned I want to determine if the population is more than 1,000,000:

If cl.Offset(0, 1) > 1000000 Then

I use the offset function to evaluate the column adjacent to colum A. I could have selected my range as being B rather than A and I wouldn’t need to do an offset. However, using offset(0,1) tells the procedure to look at the column to the right. Just like the regular excel function, the first argument in the offset function is the number of row offsets, and the second is the column offset. So the above line evaluates if the related value in column B is more than 1,000,000. The next line I enter is what will happen if it is true:

cl.Interior.Color = vbYellow

This will highlight the cell in yellow. There are a lot of things you can do and in VBA once you type the period after your range, in this case once I enter cl. it will show me a list of properties and methods that I can use. It makes it easy if you don’t remember the exact property or method name:

If I wanted to make the cell bold I would do the following:

cl.Font.Bold = true

I did not use the offset function here as I want the country field in column A to be highlighted and bolded. My entire sub procedure from start to finish looks as follows:

—————————————————————————————————————–

Sub highlight()

Dim cl As Range

Dim totalcount As Integer

Dim ws As Worksheet

Set ws = Worksheets(“Sheet1”)

totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))

For Each cl In ws.Range(“A2:A” & totalcount)

If cl.Offset(0, 1) > 1000000 Then

cl.Interior.Color = vbYellow

cl.Font.Bold = True

End If

Next cl

End Sub

—————————————————————————————————————–

After running the above subprocedure this is what my table now looks like:

I can also use a counter to accomplish the same thing, in which case I don’t use the cl variable

—————————————————————————————————————–

Sub highlight2()

Dim totalcount As Integer

Dim ws As Worksheet

Set ws = Worksheets(“Sheet1”)

totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))

counter = 0

For counter = 1 To totalcount

If ws.Range(“B” & counter) > 1000000 Then

ws.Range(“A” & counter).Interior.Color = vbYellow

ws.Range(“A” & counter).Font.Bold = True

End If

Next counter

End Sub

—————————————————————————————————————–

The above code will work in the exact same way as the previous, the only difference is the counter. You’ll see in the below examples why a counter is useful.

Suppose I wanted to delete the rows where the population was less than 1,000,000 people in 2015. I could modify my original code to work as follows:

—————————————————————————————————————–

Sub deleterow()

Dim cl As Range

Dim totalcount As Integer

Dim ws As Worksheet

Set ws = Worksheets(“Sheet1”)

totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))

For Each cl In ws.Range(“A2:A” & totalcount)

If cl.Offset(0, 1) < 1000000 Then

cl.EntireRow.Delete

End If

Next cl

End Sub

—————————————————————————————————————–

The key difference is instead of bolding and highlighting I have the following line:

cl.EntireRow.Delete

This line will delete the row that the cell is on. If I run this subprocedure, I get the following result:

You’ll notice there is a problem. I have a country that has less than 1,000,000 people in this list – Antigua and Barbuda. It worked in a few instances – it got rid of Aruba, Andorra, and American Samoa. The problem is because American Samoa was right before Antigua and Barbuda. When I delete a row the loop does not factor in that the row that Antigua and Barbuda was on has now moved up a slot so when the loop goes to the next row it has skipped over the one that Antigua and Barbuda is on. So the loop fails in this instance when you run into consecutive rows meeting the condition you have specified.

However all you need to do to correct this is to start your loop backwards: start from the last row and work your way up. To do this I use the counter, The opening line of my loop is as follows:

For counter = totalcount To 2 Step -1

If you recall totalcount is my last row, and it will go until it reaches row 2. Step -1 indicates that it should go up one row at a time (if I wanted it go up two rows at a time it would be Step -2 instead). That way if I delete a row it doesn’t affect my loop since the data above the row I’ve deleted remains the same. My new sub procedure looks like this:

—————————————————————————————————————–

Sub deleterow()

Dim totalcount As Integer

Dim ws As Worksheet

Set ws = Worksheets(“Sheet1”)

totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))

counter = 0

For counter = totalcount To 2 Step -1

If ws.Range(“B” & counter) < 1000000 Then

ws.Range(“A” & counter).EntireRow.Delete

End If

Next counter

End Sub

—————————————————————————————————————–

Now when I run the above sub procedure I get the following result:

Now you’ll notice Antigua and Barbuda is now gone and my list only contains countries with populations of more than 1,000,000 people.

HowtoExcel.net

Posts in category: loops

How to Create a Spinning Wheel in Excel

Create a spinning wheel effect by rotating an object

Creating a spin effect by changing visibility

Doing Loops in VBA to Highlight Cells and Delete Rows

HowtoExcel.net

HowtoExcel.net