Sup guys, now my task is to create a macro that will be altered by user inputs.
Basically I will have a form where the user can enter two values, a date and the value of the tax, the current values for these are 12/31/2022 and 2.91, but soon the values will change, because this tax is always ajusted, so that my database dont become dated, I need a way to deal with the new values.
The macro will work inside another form, where I have the entering date and the leaving date of the car, the code will have to compare the dates with the current tax value, but if the start date was before the new tax value, it will be calculated till the new date with the old value and then it will calculate with the new value till the end date, and then sum the values into the tax final value.
I have a working version that works with 2 values, I need to change it to accept more values and get theses values from the table called “DBVariation” that stores the Data and UFM (tax) fields.
Here is the version I’m currently using:
Sub CalculateUFM()
'variable declaration
mainform = ThisComponent.DrawPage.Forms.getByName("MainForm")
subform = mainform.getByName("SubFormUFM")
GuardaBox = subform.getByName("Guarda")
DiariaBox = subform.getByName("Diaria")
DataEntrada = mainform.getByName("datDataEntrada")
DataSaida = mainform.getByName("datDataSaida")
TipoVeiculo = mainform.getByName("TipoVeiculo")
DataEntradaText = DataEntrada.text
DataSaidaText = DataSaida.text
DataLimit = "31/12/2019"
Today = Date
DataLimitDiff = DateDiff("d", 1900, DataLimit)
DataEntradaDiff = DateDiff("d", 1900, DataEntradaText)
DataSaidaDiff = DateDiff("d", 1900, DataSaidaText)
TipoVeiculoText = TipoVeiculo.SelectedValue
TextUFM = mainform.getbyname("textoufm")
'===========================================================================================
'if the entry date is empty, it clears the three fields: Guard, Daily, and the explanation text
If DataEntradaText = "" Then
GuardaBox.text = ""
DiariaBox.text = ""
TextUFM.label = ""
Exit Sub
End If
'===========================================================================================
'date difference = days between 1900 and the date
'if the entry date is greater than the exit date and the exit date is not empty, then it resets the text of the exit date and the daily box
'after that, it calculates in which UFM the date fits and calculates the guard
If DataEntradaDiff > DataSaidaDiff And DataSaidaText <> "" Then
DataSaida.text = ""
DiariaBox.text = ""
If DataEntradaDiff < DataLimitDiff Then
UFM = 2.50
Else
UFM = 2.91
End If
If TipoVeiculoText = 0 Then
Guarda = 21 * UFM
ElseIf TipoVeiculoText = 1 Then
Guarda = 25 * UFM
ElseIf TipoVeiculoText = 2 Then
Guarda = 29 * UFM
ElseIf TipoVeiculoText = 3 Then
Guarda = 34 * UFM
Else
Guarda = 48 * UFM
End If
Guarda = "R$" + Guarda
GuardaBox.text = Guarda
TextUFM.label = "Guard calculated with UFM at: " + UFM
MsgBox("Exit date cannot be earlier than entry date.", MB_OK, "Error")
Exit Sub
End If
'===========================================================================================
'if the exit date is empty, it checks if the entry date is empty, if it is, it resets everything and exits, if not, it resets the daily box and calculates the guard based on the entry date
If DataSaidaText = "" Then
If DataEntradaText = "" Then
GuardaBox.text = ""
DiariaBox.text = ""
TextUFM.label = ""
Exit Sub
End If
DiariaBox.text = ""
If DataEntradaDiff < DataLimitDiff Then
UFM = 2.50
Else
UFM = 2.91
End If
If TipoVeiculoText = 0 Then
Guarda = 21 * UFM
ElseIf TipoVeiculoText = 1 Then
Guarda = 25 * UFM
ElseIf TipoVeiculoText = 2 Then
Guarda = 29 * UFM
ElseIf TipoVeiculoText = 3 Then
Guarda = 34 * UFM
Else
Guarda = 48 * UFM
End If
Guarda = "R$" + Guarda
GuardaBox.text = Guarda
TextUFM.label = "Guard calculated with UFM at: " + UFM
Exit Sub
End If
'===========================================================================================
'if the entry date is before 2019 and the exit date is also before 2019, it does the calculation considering the old UFM
If DataLimitDiff > DataEntradaDiff And DataLimitDiff > DataSaidaDiff Then
UFM = 2.50
DataDiff = DateDiff("d", DataEntradaText, DataSaidaText) + 1
If TipoVeiculoText = 0 Then
Guarda = 21 * UFM
Diaria = 5 * UFM * DataDiff
ElseIf TipoVeiculoText = 1 Then
Guarda = 25 * UFM
Diaria = 7 * UFM * DataDiff
ElseIf TipoVeiculoText = 2 Then
Guarda = 29 * UFM
Diaria = 13 * UFM * DataDiff
ElseIf TipoVeiculoText = 3 Then
Guarda = 34 * UFM
Diaria = 13 * UFM * DataDiff
Else
Guarda = 48 * UFM
Diaria = 25 * UFM * DataDiff
End If
TextUFM.label = "Days the car is stored: " + DataDiff + ". UFM: " + UFM
Guarda = "R$" + Guarda
Diaria = "R$" + Diaria
GuardaBox.text = Guarda
DiariaBox.text = Diaria
'===========================================================================================
'if the entry date is before or equal to 2019 and the exit date is greater or equal to 2019, it calculates half with the old UFM and half with the new one, in the end, it adds the two dailies
'the guard value is calculated based on the entry date
ElseIf DataLimitDiff >= DataEntradaDiff And DataLimitDiff <= DataSaidaDiff Then
UFM = 2.50
UFMBKP = UFM
DataDiff = DateDiff("d", DataEntradaText, DataLimit) + 1
DataDiffBKP = DataDiff
If TipoVeiculoText = 0 Then
Guarda = 21 * UFM
Diaria1 = 5 * UFM * DataDiff
ElseIf TipoVeiculoText = 1 Then
Guarda = 25 * UFM
Diaria1 = 7 * UFM * DataDiff
ElseIf TipoVeiculoText = 2 Then
Guarda = 29 * UFM
Diaria1 = 13 * UFM * DataDiff
ElseIf TipoVeiculoText = 3 Then
Guarda = 34 * UFM
Diaria1 = 13 * UFM * DataDiff
Else
Guarda = 48 * UFM
Diaria1 =
Thank you very much for all the help!