In the current article, I will show how to resolve with VBA an entry exam for programming in HackBulgaria. The problems could be resolved in any language and I will use VBA for the first two in this article.
The idea is to calculate the volume of a tetrahedron (the thing rolling below). The amount of water should be in liters. After this, you should calculate how many tetrahyrdons can you fill up with the the given liters. The detailed description of the problem is here.
Problem 1 – 1-Fill-tetrahedron-with-water
Problem 2 – 2-Tetrahedron-filled-with-water
So, what I have done? I have generated 2 custom functions and 1 sub routine. The two custom functions calculate how much liters of water fill a tetrahyrdon and how many tetrahydron can we fill. The sub routine is needed, in order to sort the array of tetrahydrons – thus, when we start filling them one by one we can see how many we have filled once the water is over. This is a small drawback of VBA – if I haev used another language solve this, then the sorting of an array (or list or anthing that contains systemized info) would have come automatic. Anyway, VBA is still a nice language.
Here is how the formula looks like in Excel:
And this is the code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
'NOTE - THE CODE IS VBA! 'This is a way to run it -> https://www.vitoshacademy.com/vba-how-to-start/ 'Enjoy it! 'The code works. Option Explicit Public Function fill_tetrahedron(ByVal num As Integer) As Double fill_tetrahedron = Round((num ^ 3) / (6 * Sqr(2)) / 1000, 2) End Function Public Function tetrahedron_filled(iWater As Integer, ParamArray arrNumbers() As Variant) As Integer 'Pay attention for the way the parameters are put. 'First we put the iWater and then the tetrahedrons. Dim arrResult() As Variant Dim iResult As Long Dim z As Long Dim i As Long ReDim arrResult(UBound(arrNumbers)) For i = LBound(arrNumbers) To UBound(arrNumbers) Step 1 arrResult(i) = CInt(arrNumbers(i)) Next i InsertionSort arrResult(), LBound(arrResult), UBound(arrResult) i = 0 For i = LBound(arrResult) To UBound(arrResult) Step 1 z = CLng(arrResult(i)) iWater = iWater - fill_tetrahedron(z) If iWater < 0 Then tetrahedron_filled = i Exit Function End If Next i tetrahedron_filled = UBound(arrResult) + 1 End Function Public Sub InsertionSort(ByRef a(), ByVal lo0 As Long, ByVal hi0 As Long) Dim i As Long, j As Long, v As Long For i = lo0 + 1 To hi0 v = a(i) j = i Do While j > lo0 If Not a(j - 1) > v Then Exit Do a(j) = a(j - 1) j = j - 1 Loop a(j) = v Next i End Sub |
Enjoy it 🙂