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 firstname.lastname@example.org, and of course, you can donate something if you like!
For work reasons, I also develop in ASP Classic on the back-end side, although less and less.
VBA still alive
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.