VBA – Sorting VBA array – Functions in VBA

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.

'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 🙂