Excel VBA practice assignments?

[ToxicBug]

hi guys,

i'm trying to learn Excel VBA for work and i did this guy's tutorial: http://www.vbtutor.net/VBA/vba_tutorial.html

i remember that there are a few of you who are good in Excel and many of you are programmers, so maybe you guys could recommend some practice problems i could do?

what i've done so far on my own is this kind of thing:

a greyscale pattern:

Code: Select all

Sub MyPattern1()
Sheet1.Cells.Clear
Dim iCounter As Integer
Dim rngRange As RANGE
Set rngRange = Sheet1.Cells
With rngRange
.Columns.ColumnWidth = .Columns("A").ColumnWidth / .Columns("A").Width * .Rows(1).Height
End With
Dim i As Long
Dim j As Long
For i = 1 To 36
For j = 1 To 36
If i > 12 And i <= 24 And j > 12 And j <= 24 Then
Cells(i, j).Interior.Color = RGB(i * (255 / 22) + j * (255 / 22) - (3315 / 11), i * (255 / 22) + j * (255 / 22) - (3315 / 11), i * (255 / 22) + j * (255 / 22) - (3315 / 11))
ElseIf i > 6 And i <= 30 And j > 6 And j <= 30 Then
Cells(i, j).Interior.Color = RGB(i * (-255 / 46) + j * (-255 / 46) + (7650 / 23), i * (-255 / 46) + j * (-255 / 46) + (7650 / 23), i * (-255 / 46) + j * (-255 / 46) + (7650 / 23))
'ElseIf i >= 1 And i <= 36 And j >= 1 And j <= 36 Then
Else: Cells(i, j).Interior.Color = RGB(i * (51 / 14) + j * (51 / 14) + (-51 / 7), i * (51 / 14) + j * (51 / 14) + (-51 / 7), i * (51 / 14) + j * (51 / 14) + (-51 / 7))
End If
Next j
Next i
End Sub

Fibonacci sequence to calculate the golden ratio (phi, 1.618...) iteratively (couldn't get the variant data type to work as a decimal tho):

Code: Select all

Sub MyFib()
Sheet1.cells.Clear
cells(1, 3).NumberFormat = "0.0000000000000000000000000000000"
cells(2, 3).NumberFormat = "0.0000000000000000000000000000000"
Dim i As Integer
Dim vPhi As Variant
Dim vRemainder As Variant
cells(1, 1) = 1
cells(2, 1) = 1
Do
i = i + 1
cells(i + 2, 1) = cells(i + 1, 1) + cells(i, 1)
vRemainder = CDec(Abs(cells(i + 2, 1) / cells(i + 1, 1) - _
 cells(i + 1, 1) / cells(i, 1)))
vPhi = CDec(cells(i + 2, 1) / cells(i + 1, 1))
Loop Until vRemainder < CDec(1E-16)
cells(1, 3) = vPhi
End Sub

if anyone have any more ideas of simple programs i could write to practice, please post them!

[GONNAFISTYA]

Goddamned nerd.

[seremtan]

well, his website looks like it was designed by a university IT dept about 10 years ago, but nevertheless i might actually have a use for this

ta

[duffman91]

With any programming language, pick up a reference book and go wild.

What is your job function?

[ToxicBug]

With any programming language, pick up a reference book and go wild.

What is your job function?

the company i work at bought me this book per my request: http://www.amazon.ca/Professional-Excel ... =8-1-spell

it's a bit too advanced for me right now, i need to get used to the language and syntax a bit more first.

my job title is "risk analyst", my main task is to analyzing transaction data and figure out ways to reduce credit card chargebacks. i have volunteered to develop and implement a streamlined spreadsheet solution for helping other analysts spend less time collecting data, allowing them to have more time performing in-depth analysis.

[ToxicBug]

well, his website looks like it was designed by a university IT dept about 10 years ago, but nevertheless i might actually have a use for this

ta

i found this tutorial yesterday, going over it now. i think it's much better than the one i posted.

http://www.excelfunctions.net/Excel-VBA-Tutorial.html

[ToxicBug]

hi,

i re-did the golden ratio (Phi) calculator by using variables instead of cells and now this allows to calculate it up to 28 decimal places and display the result in a message box (since cells can only store double precision values).

the problem is that the result that i get is rounded up, so it's actually only correct to 27 decimal points.

my phi value:
1.6180339887498948482045868344

real phi value:
1.6180339887498948482045868343656...

anyone know how to fix my phi to display 343 on the end instead of 344?

Code: Select all

Sub MyFib()

    Dim i As Long
    Dim x_0, x_1, x_2 As Double
    Dim vx_0, vx_1, vx_2 As Variant
    
    Dim vPhi As Variant 'decimal
    Dim vRemainder As Variant 'decimal
    
    x_0 = 1
    x_1 = 1
    i = 0
        
    Do
    
        i = i + 1
        x_2 = x_1 + x_0
                
        vx_0 = CDec(x_0)
        vx_1 = CDec(x_1)
        vx_2 = CDec(x_2)
                
        vPhi = (vx_2 / vx_1)
        vRemainder = Abs((vx_2 / vx_1) - (vx_1 / vx_0))
        
        x_0 = x_1
        x_1 = x_2
        
    Loop Until vRemainder < 1E-28
   
    MsgBox ("Phi = " & vPhi & vbCrLf & _
        "Remainder = " & vRemainder & vbCrLf & _
        "Iterations = " & i)
        
End Sub

[duffman91]

Can't you calculate to 28+ digits and truncate to 27?

http://www.techonthenet.com/excel/formulas/trunc.php

The least significant digit will generally act a fool otherwise.

Wait:
http://www.ozgrid.com/VBA/variables.htm

64-bit numbers in the range -1.79769313486232E308 to -4.94065645841247E-324

A double has wayyyy over 28 digits... you should be able to truncate and then store to a value => cell

[Plan B]

Maybe time to add a "please help me do my homework"-board.

[^misantropia^]

ToxicBug, look into Python if you're going to do serious number crunching. It has arbitrary precision math, tons and tons of numerical libraries and a pretty syntax.

[ToxicBug]

Can't you calculate to 28+ digits and truncate to 27?

http://www.techonthenet.com/excel/formulas/trunc.php

The least significant digit will generally act a fool otherwise.

Wait:
http://www.ozgrid.com/VBA/variables.htm

64-bit numbers in the range -1.79769313486232E308 to -4.94065645841247E-324

A double has wayyyy over 28 digits... you should be able to truncate and then store to a value => cell

i think that you misunderstood how the program stores numbers. i'll explain it in simpler terms. a Double data type can only store 15 significant digits (plus an exponent), while the decimal data type can store 29 significant digits (plus an exponent).

my procedure calculates phi by taking a ratio of two numbers. this gives me 1.618..., so if i define phi as a Double it can only store 14 decimals after the 1. as a Variant converted to Decimal (using the CDec() function) it can store 28 decimals.

[ToxicBug]

ToxicBug, look into Python if you're going to do serious number crunching. It has arbitrary precision math, tons and tons of numerical libraries and a pretty syntax.

thanks, i'll take a look

[Deji]

I programmed a game of pool when I had my Excel VBA course, might want to try that for something a bit different

(Though it's less then a perfect environment for constant position and collision calculations, I think I only did it with 4 balls or something because it started crashing with too many)