The VBA code of ManejandoMondo Excel

Second chapter about ManejandoMondo and the VBA code inside: not only you can have for free the Excel file, but also, I explain the code.

The app ManejandoMondo is available for you by sending me an email to manejandodatos@gmail.com, and of course, you can donate something if you like!

A long time ago, by 2013, I wrote an article here at ManejandoDatos related to VBA and its survival, but still in 2021, VBA is alive. VBA is not a language I use a lot because I develop with Javascript and Vue, or Python.

For work reasons, I also develop in ASP Classic on the back-end side, although less and less.

VBA still alive

A long time ago, VBA was destined to die, but it is still very much alive. I don’t think it is a burden of language, but it is true that while Python or Javascript, or even Microsoft’s C# have evolved, VBA has remained static, and it looks like it will not evolve more (in fact, I think it has been years without evolution).

It’s a pity because this language could be much more powerful than it is now, if it was reinforced with several “little things”. In my opinion, I would have improved the dictionary type (from Python or something similar to JSON in Javascript). But even more, I would have also provided shorthand syntax (like the a = 1 if condition else 2 from Python).

VBA of ManejandoMondo to open a form

Let’s start what’s the VBA code inside ManejandoMondo: the best way to open a form inside VBA is from a module, in a public funcion, with a very simple code, such as:

Public Const APP = "ManejandoMondo"

Public Const TRACK = "track"
Public Const sSTART = "start"


Public Sub START()
Dim f As New frmTrack
f.Caption = APP & VERSION
f.txtPF.Text = 122
f.Show
End Sub

The frmTrack form

When the form is initialized, several operations are performed. The function PonFecha, described below, is the one that locates the last row of the track sheet to find the last date. In addition, it locates the unique values of the various Activities, to insert them in the ListBox.

Private Sub UserForm_Initialize()
Dim uFecha As String, f As Date, unicos As Variant, i As Integer
Me.lblTit.Caption = APP & VERSION
Sheets(TRACK).Select
PonFecha
        unicos = WorksheetFunction.Unique(Range("B2:B" & ufila))
        'Obtenemos los datos de la matriz y los pasamos a la hoja
        For i = LBound(unicos) To UBound(unicos)
            If Len(unicos(i, 1)) Then Me.lstTipo.AddItem unicos(i, 1)
        Next i
End Sub

The function PonFecha:

This function is responsible for working with the date. If the chackbox is checked, the one in the corresponding box is selected, but if it is unchecked, the last activity is worked on. It also verifies that there is always a date available.:

Private Sub PonFecha()
Dim uFecha As String, f As Date
Dim i As Integer
If Me.chkPF.Value Then
    i = Me.txtPF.Text
Vuelve:
    uFecha = Range("A" & i).Value
    If Len(uFecha) = 0 Then
mierror:
        i = i - 1
        If i < 3 Then GoTo Ending Else GoTo Vuelve
    End If
    On Error GoTo mierror
    f = CDate(uFecha)
    On Error Resume Next
Else
    ufila = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
LeeFecha:
    uFecha = Range("A" & ufila).Value
    If Len(uFecha) = 0 Then
        ufila = ufila - 1
        GoTo LeeFecha
    End If
    f = CDate(uFecha)
    f = DateAdd("d", 1, f)
End If
Me.txtFecha.Text = Str(f)
Ending:
End Sub

In order to avoid having to change the date to the next or previous day, two buttons (and three functions) have been created for this purpose:

Private Sub ModFecha(v As Integer)
Dim uFecha As String, f As Date
f = CDate(Me.txtFecha.Text)
f = DateAdd("d", v, f)
Me.txtFecha.Text = f
End Sub

Private Sub cmdMas_Click()
ModFecha 1
End Sub

Private Sub cmdMenos_Click()
ModFecha -1
End Sub

One is the one that executes the modification of the date, while the others send if you want a day before or a day after. It is simple, but effective, reducing what the user has to type.

Moreover, to reduce what the user write, and to have appropriate formats of times and rhythms, two functions are created to modify the . by what is appropriate.

Private Function AjustaHora(s As String) As String
Dim i As Integer
s = Replace(s, ".", ":")
i = InStr(1, s, ":")
If i Then i = InStr(i + 1, s, ":")
If i = 0 Then s = "0:" & s
AjustaHora = s
End Function

Private Function AjustaRitmo(s As String) As String
AjustaRitmo = Replace(s, ".", ":")
End Function

Another function is also included to clean the form, when more than one activity is entered.:

Private Sub ResetForm()
Me.txtDist.Text = ""
Me.txtTiempo.Text = ""
Me.txtCal.Text = ""
Me.txtRitmo.Text = ""
Me.txtAsc.Text = ""
Me.txtDes.Text = ""
Me.txtFC.Text = ""
Me.txtFCMx.Text = ""
Me.txtObs.Text = ""
End Sub

And finally, the function that passes the data from the form to the sheet Track:

Private Sub cmdInsert_Click()
If Len(Me.lstTipo.Text) = 0 Then
    MsgBox "Actividad SIN seleccionar!", vbCritical, APP & VERSION
    Exit Sub
End If
    Dim sDate() As String, v As Variant
    Dim Y As Long, M As Long, D As Long, f As String
    
    If Me.chkPF.Value Then
        ufila = Me.txtPF.Text
        Rows(ufila & ":" & ufila).Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Me.txtPF.Text = ufila + 1
        ufila = ufila - 1
    End If

f = Me.txtFecha.Text: sDate = Split(f, "/")
    Y = sDate(2): M = sDate(1): D = sDate(0)
    f = Format(DateSerial(Y, M, D), "yyyy-mm-dd")
Range("A" & ufila + 1).Value = f ' Fecha
Range("B" & ufila + 1).Value = Me.lstTipo.Text  ' Tipo
Range("C" & ufila + 1).Value = Me.txtDist.Text  ' Distancia
Range("D" & ufila + 1).Value = AjustaHora(Me.txtTiempo.Text)  ' Tiempo
Range("E" & ufila + 1).Value = Me.txtCal.Text  ' Calorias

Range("F" & ufila + 1).Value = AjustaRitmo(Me.txtRitmo.Text)  ' Ritmo Mx
Range("G" & ufila + 1).Value = Me.txtAsc.Text  ' Asc
Range("H" & ufila + 1).Value = Me.txtDes.Text  ' Desc
Range("I" & ufila + 1).Value = Me.txtFC.Text  ' FC Media
Range("J" & ufila + 1).Value = Me.txtFCMx.Text  ' FC Mx

Range("K" & ufila + 1).Value = Me.txtObs.Text  ' Obs
Range("L" & ufila & ":O" & ufila).Select
    Selection.AutoFill Destination:=Range("L" & ufila & ":O" & ufila + 1), Type:=xlFillDefault
If Me.chkPF.Value Then
    ResetForm
    cmdMas_Click
Else
    Dim vv As VbMsgBoxResult
    vv = MsgBox("Incluir datos a Proyección de objetivos?", vbYesNo, APP)
    If vv = vbYes Then
        ToProyeccion
    End If
    Me.Hide
End If
ActiveWorkbook.Save
End Sub

Later, it is verified that an Activity Type has been selected, and after inserting the row in the tab, the data is inserted, and it is applied to autocomplete several calculated columns. (from column L to O).

Finally, if you are inserting previous activities, clean and restart everything, but if it is the last activity, save the activity and also the hello, to avoid losing data..

A extra: in version 0.4.0 a new Projection sheet has been included, which allows you to save new information about projections of your statistics for the end of the year.

Next versions of ManejandoMondo

So far, updates to the Excel sheet have focused on improving statistics, and new charts. There are few new features in the bersion 0.4.0 of ManejandoMondo. This update is focused on the projection of objectives, to date with respect to what is expected on December 31 of each year. For this purpose, objectives must be defined and it is possible to check if during the end of the year, you are reaching them, and how this progression is going.

And … that’s all.