Page 1 of 1

Excel VBA practice assignments?

Posted: Sat Jan 29, 2011 3:40 pm
by 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!

Re: Excel VBA practice assignments?

Posted: Sat Jan 29, 2011 5:28 pm
by GONNAFISTYA
Goddamned nerd.

Re: Excel VBA practice assignments?

Posted: Sat Jan 29, 2011 5:28 pm
by 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

Re: Excel VBA practice assignments?

Posted: Sun Jan 30, 2011 6:13 pm
by duffman91
With any programming language, pick up a reference book and go wild.

What is your job function?

Re: Excel VBA practice assignments?

Posted: Mon Jan 31, 2011 3:38 am
by ToxicBug
duffman91 wrote: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.

Re: Excel VBA practice assignments?

Posted: Mon Jan 31, 2011 3:39 pm
by ToxicBug
seremtan wrote: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

Re: Excel VBA practice assignments?

Posted: Mon Jan 31, 2011 6:28 pm
by 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

Re: Excel VBA practice assignments?

Posted: Tue Feb 01, 2011 6:20 pm
by 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

Re: Excel VBA practice assignments?

Posted: Tue Feb 01, 2011 8:11 pm
by Plan B
Maybe time to add a "please help me do my homework"-board.

Re: Excel VBA practice assignments?

Posted: Tue Feb 01, 2011 10:56 pm
by ^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.

Re: Excel VBA practice assignments?

Posted: Wed Feb 02, 2011 12:29 am
by ToxicBug
duffman91 wrote: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.

Re: Excel VBA practice assignments?

Posted: Wed Feb 02, 2011 12:35 am
by ToxicBug
^misantropia^ wrote: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 :)

Re: Excel VBA practice assignments?

Posted: Thu Feb 03, 2011 8:20 pm
by Deji
I programmed a game of pool when I had my Excel VBA course, might want to try that for something a bit different :p

(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)