top of page

Empresa: NYK

Gestor: Bruno

Projeto: LCC

Tech Guide

top

LCC Documentation:

The Tech Guide as well as the documentation written down has been put together to serve as support material for technical professionals who have in-depth knowledge of:

> The tool

> Calculus 

> Programming & Logic

> The business

!

  1. Stevedorage and Shifting Tariff Calculus

  2. Subroutine Documentation: 'GettingBasePaths'

  3. Subroutine Documentation: 'Upload'

  4. Subroutine Documentation: 'Keep'

  5. Subroutine Documentation: 'Fetch'

  6. Subroutine Documentation: 'Recording'

  7. Subroutine Documentation: 'BringingComputing'

  8. Subroutine Documentation: 'UpdatingComputing'

  9. ADM Panel

Stevedorage and Shifting Tariff Calculus
image.png

The formula provided is used to calculate the tariff based on tonnage numbers for Cars, High and Heavy (HH), and Statics. This formula ensures accurate cost calculations by leveraging specific conditions and lookup functions in Excel. Below is a detailed breakdown of the formula:

=IFERROR(
   IF(L44=""; "";
       IF(L44 < SUMIFS(CONTRATOS_VIX!$M$43:$M$51; CONTRATOS_VIX!$G$43:$G$51; Y44; CONTRATOS_VIX!$F$43:$F$51; W44);
         SUMIFS(CONTRATOS_VIX!$L$43:$L$51; CONTRATOS_VIX!$G$43:$G$51; Y44; CONTRATOS_VIX!$F$43:$F$51; W44) / L44;
         SUMIFS(CONTRATOS_VIX!$H$43:$H$51; CONTRATOS_VIX!$F$43:$F$51; W44; CONTRATOS_VIX!$G$43:$G$51; Y44)
                   )
          );""
)

Breaking Down:


1. Error Handling with IFERROR:
IFERROR(...; "") ensures that if any part of the formula results in an error, an empty string "" is returned instead of an error message. This improves the robustness of the formula by preventing error displays.
 

2. Initial Check for Empty Cell:
IF(L44=""; ""; ...) checks if the cell L44 is empty. If it is empty, the formula returns an empty string "", effectively skipping the calculation. This ensures that the formula only proceeds if there is a value in L44.


3. Conditional Tariff Calculation:
IF(L44 < SUMIFS(...); ...; ...) compares the value in L44 with a calculated threshold using the SUMIFS function.
SUMIFS(CONTRATOS_VIX!$M$43:$M$51; CONTRATOS_VIX!$G$43:$G$51; Y44; CONTRATOS_VIX!$F$43:$F$51; W44) sums the values in the range M43:M51 in the CONTRATOS_VIX sheet, based on matching criteria in the ranges G43:G51 and F43:F51 with values in Y44 and W44, respectively.
 

Calculation Based on Condition:
If L44 is less than the calculated threshold:
The formula calculates SUMIFS(CONTRATOS_VIX!$L$43:$L$51; CONTRATOS_VIX!$G$43:$G$51; Y44; CONTRATOS_VIX!$F$43:$F$51; W44) / L44, which divides the sum of the values in the range L43:L51 (meeting the same criteria) by the value in L44. This division likely represents a rate or average calculation.


If L44 is not less than the threshold:
The formula returns SUMIFS(CONTRATOS_VIX!$H$43:$H$51; CONTRATOS_VIX!$F$43:$F$51; W44; CONTRATOS_VIX!$G$43:$G$51; Y44), which sums the values in the range H43:H51 based on the criteria.



This formula dynamically calculates a tariff based on the tonnage numbers for Cars, HH, and Statics, utilizing several SUMIFS functions to ensure accurate aggregation of relevant data. The formula checks for errors, handles empty cells gracefully, and adjusts calculations based on specified conditions to provide reliable and precise tariff values.

Anchor 1
Subroutine Documentation: 'GettingBasePaths'
Anchor 2

The GettingBasePaths subroutine in LCC (Lite Cost Control) is designed to handle the selection and assignment of file paths for the Working Schedule (WS) and Checklist (CL) datasets. This subroutine dynamically determines which port and method (WS or CL upload) is calling it, opens a file dialog for the user to select the appropriate file, and then saves the selected file path to the specified cell in the corresponding support sheet (Apoio). Finally, it triggers the Upload subroutine to handle the file upload process.

Sub GettingBasePaths()

Dim Wb As Workbook
Dim Port As String
Dim ButtonCallingFunction As String
Dim ButtonMethod As String
Dim ApoioSheet As Worksheet
Dim WSPathCell As Range
Dim CLPathCell As Range

Dim FileBox As FileDialog
Dim SelectedFile As String

' Passing the respective values to each variable
Set Wb = ThisWorkbook
ButtonCallingFunction = Application.Caller

Port = Left(ButtonCallingFunction, 3)
ButtonMethod = Right(ButtonCallingFunction, 8)

Set ApoioSheet = Wb.Sheets("Apoio_" & Port)
Set WSPathCell = ApoioSheet.Range("J10")
Set CLPathCell = ApoioSheet.Range("N10")

' Opening up the dialog window to select files
Set FileBox = Application.FileDialog(msoFileDialogFilePicker)

' Logic to check if a file was selected and saving its path to a cell
If FileBox.Show = -1 Then
SelectedFile = FileBox.SelectedItems(1)

' Distinguishing which button is calling the function. WS or CL?
If ButtonMethod = "WSUpload" Then
WSPathCell.Value = SelectedFile
Call Upload(ButtonMethod, Port)

ElseIf ButtonMethod = "CLUpload" Then
CLPathCell.Value = SelectedFile
Call Upload(ButtonMethod, Port)

End If
End If

Set FileBox = Nothing

End Sub

Variables and Objects

  • Wb (Workbook): The current workbook (ThisWorkbook).

  • Port (String): Extracted from the calling button's name, representing the port.

  • ButtonCallingFunction (String): The name of the button that triggered the subroutine.

  • ButtonMethod (String): Determines if the subroutine was called for WS or CL upload.

  • ApoioSheet (Worksheet): The support sheet corresponding to the specified port.

  • WSPathCell (Range): The cell in the Apoio sheet where the WS file path is saved.

  • CLPathCell (Range): The cell in the Apoio sheet where the CL file path is saved.

  • FileBox (FileDialog): File dialog object for selecting files.

  • SelectedFile (String): The path of the selected file.

​

Workflow

1.Assign Variables:

  • Set Wb to the current workbook.

  • Extract Port and ButtonMethod from the calling button's name.

  • Set the ApoioSheet based on the extracted port.

  • Define WSPathCell and CLPathCell as the specific cells in the Apoio sheet.

​

2. Open File Dialog:

  • Initialize FileBox as a file picker dialog.

  • Display the file dialog to the user.


3. Save Selected File Path:

  • If a file is selected (FileBox.Show = -1), save the selected file path to either WSPathCell or CLPathCell based on the ButtonMethod.

  • Call the Upload subroutine with the appropriate method and port.

​

4. Clean Up:

  • Release the FileBox object.

​

Key Points

  • Dynamic Button Handling: The subroutine dynamically determines which button (WS or CL upload) triggered it and processes the selected file accordingly.

  • File Dialog: Provides a user-friendly interface for selecting files.

  • Path Assignment: Saves the selected file path to the appropriate cell in the support sheet.

  • Subroutine Call: After saving the path, it triggers the Upload subroutine to handle the upload process.

Subroutine Documentation: 'Upload'
Anchor 3

The Upload subroutine in LCC (Lite Cost Control) is designed to upload and process the Working Schedule (WS) and Checklist (CL) datasets for a specified port. This subroutine dynamically handles file selection, sheet identification, and data copying based on the user's selection, ensuring that the necessary data is correctly integrated into the LCC system.

Public Sub Upload(ButtonMethod As String, Port As String)

'SETTING VARIABLES
Dim Wb As Workbook
Dim UploadSheet As Worksheet
Dim ApoioSheet As Worksheet
Dim WSSheet As Worksheet
Dim CLSheet As Worksheet

Dim TargetWb As Workbook
Dim TargetSheet As Worksheet
Dim WSSheetName As String
Dim CLSheetName As String
Dim WSTargetRange As String
Dim CLTargetRange As String

Dim filePath As String
Dim FilePathRange As Range

Dim Decider As String
Dim LastModified As Date

Dim LCCPassword As String

'ASSIGNING VARIABLE VALUES
Set Wb = ThisWorkbook
Set UploadSheet = Wb.Sheets("P_" & Port & "_BASE")
Set ApoioSheet = Wb.Sheets("Apoio_" & Port)
Set WSSheet = Wb.Sheets("BASE_WS_" & Port)
Set CLSheet = Wb.Sheets("BASE_CL_" & Port)
LCCPassword = A2_LCCPassword.LCCPassword()

WSSheetName = "WORKING SCHEDULE"
CLSheetName = "CHECK LIST"

WSTargetRange = "A1:AD1000"
CLTargetRange = "A1:T1000"

If ButtonMethod = "WSUpload" Then
Application.ScreenUpdating = False
Set FilePathRange = ApoioSheet.Range("J10")
filePath = FilePathRange.Value
WSSheet.Unprotect Password:=LCCPassword
WSSheet.Range(WSTargetRange).ClearContents

Set TargetWb = Workbooks.Open(filePath, , ReadOnly:=True)
On Error GoTo WSSheetNotFoundHandler
Set TargetSheet = TargetWb.Sheets(WSSheetName)
On Error GoTo 0

TargetSheet.Range(WSTargetRange).Copy
WSSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
TargetWb.Close SaveChanges:=False

WSSheet.Select
WSSheet.Range("A1").Select

LastModified = FileDateTime(filePath)

ApoioSheet.Range("J10").Value = GetFileName(filePath)
ApoioSheet.Range("J11").Value = LastModified
ApoioSheet.Range("J13").Copy
ApoioSheet.Range("J12").PasteSpecial Paste:=xlPasteValues

UploadSheet.Select
WSSheet.Protect Password:=LCCPassword

Application.ScreenUpdating = True
MsgBox ("BASE_WS_" & Port & " has been successfully added")

ElseIf ButtonMethod = "CLUpload" Then
Application.ScreenUpdating = False
Set FilePathRange = ApoioSheet.Range("N10")
filePath = FilePathRange.Value
CLSheet.Unprotect Password:=LCCPassword
CLSheet.Range(CLTargetRange).ClearContents

Set TargetWb = Workbooks.Open(filePath, , ReadOnly:=True)
On Error GoTo CLSheetNotFoundHandler
Set TargetSheet = TargetWb.Sheets(CLSheetName)
On Error GoTo 0

TargetSheet.Range(CLTargetRange).Copy
CLSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
TargetWb.Close SaveChanges:=False

CLSheet.Select
CLSheet.Range("A1").Select

LastModified = FileDateTime(filePath)

ApoioSheet.Range("N10").Value = GetFileName(filePath)
ApoioSheet.Range("N11").Value = LastModified
ApoioSheet.Range("N13").Copy
ApoioSheet.Range("N12").PasteSpecial Paste:=xlPasteValues

UploadSheet.Select
CLSheet.Protect Password:=LCCPassword

Application.ScreenUpdating = True
MsgBox ("BASE_CL_" & Port & " has been successfully added")

End If

Exit Sub

WSSheetNotFoundHandler:
ApoioSheet.Range("J10").ClearContents
MsgBox ("We couldn't find a sheet named " & WSSheetName & " in the workbook you've selected.")
TargetWb.Close SaveChanges:=False
Exit Sub

CLSheetNotFoundHandler:
ApoioSheet.Range("N10").ClearContents
MsgBox ("We couldn't find a sheet named " & CLSheetName & " in the workbook you've selected")
TargetWb.Close SaveChanges:=False
Exit Sub

End Sub

Function GetFileName(filePath As String) As String
Dim LastBackSlash As Integer
LastBackSlash = InStrRev(filePath, "\")
GetFileName = Right(filePath, Len(filePath) - LastBackSlash)
End Function

Variables and Objects

  • Wb (Workbook): The current workbook (ThisWorkbook).

  • UploadSheet (Worksheet): The base upload sheet for the specified port.

  • ApoioSheet (Worksheet): The support sheet for the specified port.

  • WSSheet (Worksheet): The worksheet for the Working Schedule of the specified port.

  • CLSheet (Worksheet): The worksheet for the Checklist of the specified port.

  • TargetWb (Workbook): The workbook containing the source data.

  • TargetSheet (Worksheet): The specific sheet within the target workbook.

  • WSSheetName (String): The name of the Working Schedule sheet.

  • CLSheetName (String): The name of the Checklist sheet.

  • WSTargetRange (String): The range for the Working Schedule data.

  • CLTargetRange (String): The range for the Checklist data.

  • filePath (String): The path of the selected file.

  • FilePathRange (Range): The range in the Apoio sheet where the file path is stored.

  • LastModified (Date): The last modified date of the selected file.

  • LCCPassword (String): The password for protecting/unprotecting sheets.

 


Workflow

1. Assign Variables:

  • Set references to the current workbook, relevant sheets, and define key strings for sheet names and target ranges.

  • Retrieve the password for protecting/unprotecting sheets.


2. Working Schedule (WS) Upload:

  • File Path and Unprotect:

  • Retrieve the file path from the Apoio sheet.

  • Unprotect the WS sheet.

  • Clear the existing contents in the WS target range.

  • Open File and Copy Data:

  • Open the target workbook in read-only mode.

  • Set a reference to the target WS sheet.

  • Copy the data from the target WS range and paste it into the WS sheet.

  • Close the target workbook.

  • Update Metadata and Protect:

  • Update the Apoio sheet with the file name and last modified date.

  • Protect the WS sheet.

  • Display a success message.


3. Checklist (CL) Upload:

  • File Path and Unprotect:

  • Retrieve the file path from the Apoio sheet.

  • Unprotect the CL sheet.

  • Clear the existing contents in the CL target range.

  • Open File and Copy Data:

  • Open the target workbook in read-only mode.

  • Set a reference to the target CL sheet.

  • Copy the data from the target CL range and paste it into the CL sheet.

  • Close the target workbook.

  • Update Metadata and Protect:

  • Update the Apoio sheet with the file name and last modified date.

  • Protect the CL sheet.

  • Display a success message.


4.Error Handling:

  • WS Sheet Not Found:

  • Clear the file path in the Apoio sheet and display an error message if the WS sheet is not found.

  • CL Sheet Not Found:

  • Clear the file path in the Apoio sheet and display an error message if the CL sheet is not found.


Key Points

  • Dynamic File Handling: The subroutine dynamically processes the WS and CL datasets based on the button method.

  • File Dialog Integration: Uses a file dialog to allow users to select the appropriate dataset files.

  • Data Validation: Ensures that the selected files contain the required sheets and data

  • Sheet Protection: Manages the protection status of sheets to maintain data integrity and security.

  • User Feedback: Provides success or error messages to inform the user of the outcome of the upload process.

Subroutine Documentation: 'Keep'
Anchor 4

The Keep subroutine in LCC (Lite Cost Control) is designed to save the current state of an analysis into one of the available memory slots. This allows users to preserve ongoing work and clear the LCC system for a new analysis. The subroutine handles copying relevant data to a designated slot sheet and then clears the original sheets to prepare for the next operation.

Public Sub Keep(Port As String, SlotSheetName As String)

Dim Wb As Workbook
Dim ContratoSheet As Worksheet
Dim ApoioSheet As Worksheet
Dim SlotSheet As Worksheet
Dim PainelSheet As Worksheet
Dim BaseUploadSheet As Worksheet
Dim ComputingSheet As Worksheet
Dim ParamSheet As Worksheet
Dim WSSheet As Worksheet
Dim CLSheet As Worksheet
Dim ReportSheet As Worksheet
Dim KeepOrClearDecider As String

Dim ComputingRange As String
Dim SlotNo As Long

Dim ContratoSheetName As String
Dim PainelSheetName As String
Dim BaseUploadName As String
Dim ParamSheetName As String
Dim ComputingSheetName As String
Dim WSSheetName As String
Dim CLSheetName As String

Dim LCCPassword As String

Application.ScreenUpdating = False

LCCPassword = A2_LCCPassword.LCCPassword()

Set Wb = ThisWorkbook
Set ApoioSheet = Wb.Sheets("Apoio_" & Port)
SlotNo = ApoioSheet.Range("B11").Value
Set SlotSheet = Wb.Sheets(SlotSheetName)

ContratoSheetName = ApoioSheet.Range("B22").Value
PainelSheetName = ApoioSheet.Range("B23").Value
BaseUploadName = ApoioSheet.Range("B24").Value
ParamSheetName = ApoioSheet.Range("B25").Value
ComputingSheetName = ApoioSheet.Range("B26").Value
WSSheetName = ApoioSheet.Range("B27").Value
CLSheetName = ApoioSheet.Range("B28").Value

Set ContratoSheet = Wb.Sheets(ContratoSheetName)
Set PainelSheet = Wb.Sheets(PainelSheetName)
Set BaseUploadSheet = Wb.Sheets(BaseUploadName)
Set ParamSheet = Wb.Sheets(ParamSheetName)
Set ComputingSheet = Wb.Sheets(ComputingSheetName)
Set WSSheet = Wb.Sheets(WSSheetName)
Set CLSheet = Wb.Sheets(CLSheetName)
Set ReportSheet = Wb.Sheets("REPORT")

ComputingRange = ApoioSheet.Range("B17").Value

'Removing protection from all sheets
On Error Resume Next
Wb.Unprotect Password:=LCCPassword
ContratoSheet.Unprotect Password:=LCCPassword
PainelSheet.Unprotect Password:=LCCPassword
BaseUploadSheet.Unprotect Password:=LCCPassword
ParamSheet.Unprotect Password:=LCCPassword
ComputingSheet.Unprotect Password:=LCCPassword
WSSheet.Unprotect Password:=LCCPassword
CLSheet.Unprotect Password:=LCCPassword
On Error GoTo 0

KeepOrClearDecider = PainelSheet.Range("G7").Value

PainelSheet.Range("G7").MergeArea.Copy
SlotSheet.Range("J10").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

PainelSheet.Range("M7").MergeArea.Copy
SlotSheet.Range("O10").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

PainelSheet.Range("P7").Copy
SlotSheet.Range("R10").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'Saving Computing with data
ComputingSheet.Range(ComputingRange).Copy
SlotSheet.Range("E34").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

'Saving parameters
ParamSheet.Range("F4:P34").Copy
SlotSheet.Range("V4").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

'Saving information about WS
ApoioSheet.Range("J10:J12").Copy
SlotSheet.Range("AK4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'Saving WS data
WSSheet.Columns("A:AD").Copy
SlotSheet.Columns("AR").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'Saving information about CL
ApoioSheet.Range("N10:N12").Copy
SlotSheet.Range("AK14").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'Saving CL data
CLSheet.Columns("A:T").Copy
SlotSheet.Columns("CA").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'Clearing data after operation
PainelSheet.Range("G7").MergeArea.ClearContents
PainelSheet.Range("M7").MergeArea.ClearContents
PainelSheet.Range("P7").ClearContents

ParamSheet.Range("K5").ClearContents
ParamSheet.Range("O12").ClearContents
ParamSheet.Range("H30:H32").ClearContents
ParamSheet.Range("G28:J28").ClearContents
ParamSheet.Range("F22:J22").ClearContents

ApoioSheet.Range("J10:J12").ClearContents
ApoioSheet.Range("N10:N12").ClearContents

WSSheet.Columns("A:AD").ClearContents
CLSheet.Columns("A:T").ClearContents

ApoioSheet.Range(ComputingRange).Copy
ComputingSheet.Select
ComputingSheet.Range("E34").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
ActiveSheet.Range("D2").Select

'Adding protection to all sheets
On Error Resume Next
ContratoSheet.Protect Password:=LCCPassword
PainelSheet.Protect Password:=LCCPassword
BaseUploadSheet.Protect Password:=LCCPassword
ParamSheet.Protect Password:=LCCPassword
ComputingSheet.Protect Password:=LCCPassword
WSSheet.Protect Password:=LCCPassword
CLSheet.Protect Password:=LCCPassword
ReportSheet.Protect Password:=LCCPassword
Wb.Protect Password:=LCCPassword
On Error GoTo 0

PainelSheet.Select

Application.ScreenUpdating = True

'Condition to display 'Slot Cleared' if we're keeping no data.
If KeepOrClearDecider <> "" Then
MsgBox "Your data has been successfully kept in Slot " & SlotNo & " and LCC may begin a new calculation"
ElseIf KeepOrClearDecider = "" Then
MsgBox ("Slot Cleared!")
End If

End Sub

Variables and Objects

  • Wb (Workbook): The current workbook (ThisWorkbook).

  • ContratoSheet (Worksheet): The sheet containing contract data.

  • ApoioSheet (Worksheet): The support sheet for the specified port.

  • SlotSheet (Worksheet): The memory slot sheet where data will be saved.

  • PainelSheet (Worksheet): The initial page sheet with primary data.

  • BaseUploadSheet (Worksheet): The sheet for dataset uploads.

  • ComputingSheet (Worksheet): The core sheet where calculations are performed.

  • ParamSheet (Worksheet): The parameters sheet.

  • WSSheet (Worksheet): The Working Schedule sheet.

  • CLSheet (Worksheet): The Checklist sheet.

  • ReportSheet (Worksheet): The report sheet.

  • KeepOrClearDecider (String): A value to determine if data should be kept or cleared.

  • ComputingRange (String): The range of the computing area.

  • SlotNo (Long): The slot number where data will be saved.

  • LCCPassword (String): The password for protecting/unprotecting sheets.


Workflow
1. Assign Variables:

  • Set references to the current workbook, relevant sheets, and define key strings for sheet names and target ranges.

  • Retrieve the password for protecting/unprotecting sheets.

  • Determine the slot number from the Apoio sheet.

​​

2. Unprotect Sheets:

  • Remove protection from all relevant sheets to allow data manipulation

​

​​

3. Copy and Paste Data:

  • Primary Data: Copy primary data from the PainelSheet and paste it into the SlotSheet.

  • Computing Data: Copy the computing range from the ComputingSheet and paste it into the SlotSheet.

  • Parameters: Copy the parameter range from the ParamSheet and paste it into the SlotSheet.

  • WS Information and Data: Copy WS information and data from the ApoioSheet and WSSheet, respectively, and paste them into the SlotSheet.

  • CL Information and Data: Copy CL information and data from the ApoioSheet and CLSheet, respectively, and paste them into the SlotSheet.


4.Clear Original Data:

  • Clear contents from the PainelSheet, ParamSheet, ApoioSheet, WSSheet, and CLSheet to prepare for a new analysis.

  • Reset the ComputingSheet with the original computing structure from the ApoioSheet.


5.Protect Sheets:

  • Reapply protection to all relevant sheets to maintain data integrity and security.

 


6.Display Confirmation:

  • Display a message confirming the data has been successfully kept or the slot has been cleared, based on the KeepOrClearDecider value.

 


Key Points

  • Dynamic Data Handling: The subroutine dynamically processes and saves the current analysis state into a specified memory slot.

  • Sheet Protection Management: Manages the protection status of sheets to ensure secure data manipulation and integrity.

  • User Feedback: Provides confirmation messages to inform the user of the outcome of the keep operation.

Subroutine Documentation: 'Fetch'
Anchor 5

The Fetch subroutine in LCC (Lite Cost Control) is designed to retrieve a previously saved analysis from a specified memory slot. This allows users to restore the state of a previously paused analysis, reloading all relevant data and settings into the appropriate sheets for continuation.

Public Sub Fetch(Port As String, SlotSheetName As String)

Dim Wb As Workbook
Dim ApoioSheet As Worksheet
Dim ContratoSheet As Worksheet
Dim SlotSheet As Worksheet
Dim PainelSheet As Worksheet
Dim BaseUploadSheet As Worksheet
Dim ComputingSheet As Worksheet
Dim ParamSheet As Worksheet
Dim WSSheet As Worksheet
Dim CLSheet As Worksheet
Dim ReportSheet As Worksheet

Dim SlotNo As Long

Dim ComputingRange As String

Dim ContratoSheetName As String
Dim PainelSheetName As String
Dim BaseUploadName As String
Dim ParamSheetName As String
Dim ComputingSheetName As String
Dim WSSheetName As String
Dim CLSheetName As String

Dim LCCPassword As String

Application.ScreenUpdating = False

LCCPassword = A2_LCCPassword.LCCPassword()

Set Wb = ThisWorkbook
Set ApoioSheet = Wb.Sheets("Apoio_" & Port)
SlotNo = ApoioSheet.Range("B11").Value
Set SlotSheet = Wb.Sheets(SlotSheetName)

ContratoSheetName = ApoioSheet.Range("B22").Value
PainelSheetName = ApoioSheet.Range("B23").Value
BaseUploadName = ApoioSheet.Range("B24").Value
ParamSheetName = ApoioSheet.Range("B25").Value
ComputingSheetName = ApoioSheet.Range("B26").Value
WSSheetName = ApoioSheet.Range("B27").Value
CLSheetName = ApoioSheet.Range("B28").Value

Set ContratoSheet = Wb.Sheets(ContratoSheetName)
Set PainelSheet = Wb.Sheets(PainelSheetName)
Set BaseUploadSheet = Wb.Sheets(BaseUploadName)
Set ParamSheet = Wb.Sheets(ParamSheetName)
Set ComputingSheet = Wb.Sheets(ComputingSheetName)
Set WSSheet = Wb.Sheets(WSSheetName)
Set CLSheet = Wb.Sheets(CLSheetName)
Set ReportSheet = Wb.Sheets("REPORT")

ComputingRange = ApoioSheet.Range("B17").Value

'Removing protection from all sheets
On Error Resume Next
Wb.Unprotect Password:=LCCPassword
ContratoSheet.Unprotect Password:=LCCPassword
PainelSheet.Unprotect Password:=LCCPassword
BaseUploadSheet.Unprotect Password:=LCCPassword
ParamSheet.Unprotect Password:=LCCPassword
ComputingSheet.Unprotect Password:=LCCPassword
WSSheet.Unprotect Password:=LCCPassword
CLSheet.Unprotect Password:=LCCPassword
On Error GoTo 0

Application.ScreenUpdating = False

'Bringing primary data from the slot
SlotSheet.Range("J10").MergeArea.Copy
PainelSheet.Select
PainelSheet.Range("G7").MergeArea.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Range("D7").Select

SlotSheet.Range("O10").MergeArea.Copy
PainelSheet.Range("M7").MergeArea.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

SlotSheet.Range("R10").Copy
PainelSheet.Range("P7").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Range("D7").Select

'Bringing computing data
SlotSheet.Range(ComputingRange).Copy
ComputingSheet.Select
ComputingSheet.Range("E34").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
ActiveSheet.Range("D2").Select

'Bringing parameters
SlotSheet.Range("V4:AF34").Copy
ParamSheet.Select
ParamSheet.Range("F4").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
ActiveSheet.Range("D2").Select

'Bringing WS info
SlotSheet.Range("AK4:AK6").Copy
ApoioSheet.Select
ApoioSheet.Range("J10:J12").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Range("A1").Select

'Bringing WS data
SlotSheet.Columns("AR:BV").Copy
WSSheet.Select
WSSheet.Columns("A").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Range("A1").Select

'Bringing CL info
SlotSheet.Range("AK14:AK16").Copy
ApoioSheet.Select
ApoioSheet.Range("N10:N12").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Range("A1").Select

'Bringing CL data
SlotSheet.Columns("CA:CT").Copy
CLSheet.Select
CLSheet.Columns("A:T").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveSheet.Range("A1").Select

'Adding protection to all sheets
On Error Resume Next
PainelSheet.Protect Password:=LCCPassword
ContratoSheet.Protect Password:=LCCPassword
BaseUploadSheet.Protect Password:=LCCPassword
ParamSheet.Protect Password:=LCCPassword
ComputingSheet.Protect Password:=LCCPassword
WSSheet.Protect Password:=LCCPassword
CLSheet.Protect Password:=LCCPassword
ReportSheet.Protect Password:=LCCPassword
Wb.Protect Password:=LCCPassword
On Error GoTo 0

Sheets("PAINEL_" & Port).Select
Application.ScreenUpdating = True

MsgBox ("The data in slot " & SlotNo & " has been retrieved successfully!")

End Sub

Variables and Objects

  • Wb (Workbook): The current workbook (ThisWorkbook).

  • ApoioSheet (Worksheet): The support sheet for the specified port.

  • ContratoSheet (Worksheet): The sheet containing contract data.

  • SlotSheet (Worksheet): The memory slot sheet from which data will be retrieved.

  • PainelSheet (Worksheet): The initial page sheet with primary data.

  • BaseUploadSheet (Worksheet): The sheet for dataset uploads.

  • ComputingSheet (Worksheet): The core sheet where calculations are performed.

  • ParamSheet (Worksheet): The parameters sheet.

  • WSSheet (Worksheet): The Working Schedule sheet.

  • CLSheet (Worksheet): The Checklist sheet.

  • ReportSheet (Worksheet): The report sheet.

  • SlotNo (Long): The slot number from which data will be retrieved.

  • ComputingRange (String): The range of the computing area.

  • LCCPassword (String): The password for protecting/unprotecting sheets.


Workflow
1.Assign Variables:

  • Set references to the current workbook, relevant sheets, and define key strings for sheet names and target ranges.

  • Retrieve the password for protecting/unprotecting sheets.

  • Determine the slot number from the Apoio sheet.


2.Unprotect Sheets:

  • Remove protection from all relevant sheets to allow data manipulation.


3.Copy and Paste Data:
Primary Data: Copy primary data from the SlotSheet and paste it into the PainelSheet.
Computing Data: Copy the computing range from the SlotSheet and paste it into the ComputingSheet.
Parameters: Copy the parameter range from the SlotSheet and paste it into the ParamSheet.
WS Information and Data: Copy WS information and data from the SlotSheet and paste them into the ApoioSheet and WSSheet, respectively.
CL Information and Data: Copy CL information and data from the SlotSheet and paste them into the ApoioSheet and CLSheet, respectively.


4.Protect Sheets:

  • Reapply protection to all relevant sheets to maintain data integrity and security.


5.Display Confirmation:

  • Display a message confirming that the data has been successfully retrieved from the specified slot.


Key Points

  • Dynamic Data Handling: The subroutine dynamically processes and retrieves the saved analysis state from a specified memory slot.

  • Sheet Protection Management: Manages the protection status of sheets to ensure secure data manipulation and integrity.

  • User Feedback: Provides confirmation messages to inform the user of the successful retrieval of data from the specified slot.

Subroutine Documentation: 'Recording'
Anchor 6

The Recording subroutine in LCC (Lite Cost Control) finalizes the analysis process by recording the results, clearing the input data, and resetting the system for a new analysis. It ensures that all necessary conditions are met before proceeding with the recording, and it manages sheet protection and data transfer efficiently.

Sub Recording()

'DEFINING VARIABLES
Dim Wb As Workbook
Dim Port As String
Dim ApoioSheet As Worksheet
Dim ContratoSheet As Worksheet
Dim PainelSheet As Worksheet
Dim BaseUploadSheet As Worksheet
Dim ParamSheet As Worksheet
Dim ComputingSheet As Worksheet
Dim HeaderTransferSheet As Worksheet
Dim StopOverTransferSheet As Worksheet
Dim HeaderSheet As Worksheet
Dim StopOverSheet As Worksheet
Dim WSSheet As Worksheet
Dim CLSheet As Worksheet
Dim ReportSheet As Worksheet
Dim LCCPassword As String
Dim PassPrompt As String

Dim BaseNextFreeRow As Long
Dim LastRowWithContent As Long

Dim ComputingRange As String

'SETTING VARIABLES
Set Wb = ThisWorkbook
Port = Application.Caller

Set ApoioSheet = Wb.Sheets("Apoio_" & Port)
Set ContratoSheet = Wb.Sheets("CONTRATOS_" & Port)
Set PainelSheet = Wb.Sheets("PAINEL_" & Port)
Set BaseUploadSheet = Wb.Sheets("P_" & Port & "_BASE")
Set ParamSheet = Wb.Sheets("P_" & Port & "_PARA")
Set ComputingSheet = Wb.Sheets("P_" & Port & "_COMP")
Set HeaderTransferSheet = Wb.Sheets("BASE_CAB_TRANSF_" & Port)
Set StopOverTransferSheet = Wb.Sheets("BASE_ESC_TRANS_" & Port)
Set HeaderSheet = Wb.Sheets("BASE_CABEÇALHO")
Set StopOverSheet = Wb.Sheets("BASE_ESCALAS")
Set WSSheet = Wb.Sheets("BASE_WS_" & Port)
Set CLSheet = Wb.Sheets("BASE_CL_" & Port)
Set ReportSheet = Wb.Sheets("REPORT")

ComputingRange = ApoioSheet.Range("B17").Value

LCCPassword = A2_LCCPassword.LCCPassword()

'KEEPING SCREEN FROM MOVING AROUND AS MACRO RUNS
Application.ScreenUpdating = False

'CHECKING FOR CONSISTENCY - ALL OK STATUSES
If ApoioSheet.Range("B9").Value <> 0 Then
MsgBox ("In order to record data, make sure there is no 'Nok' status in the consistency panel")
Application.ScreenUpdating = True
Exit Sub
End If

'CHECKING WHETHER THE YEAR HAS BEEN ADDED
If PainelSheet.Range("P7").Value = "" Then
MsgBox "Add year of operation.", , "Oops"
Application.ScreenUpdating = True
Exit Sub
End If

'ASKING FOR PASSWORD
PassPrompt = InputBox("Enter Password")
If PassPrompt <> LCCPassword Then
MsgBox "Incorrect Password", , "Oops"
Application.ScreenUpdating = True
Exit Sub
End If

'REMOVING PROTECTION FROM ALL SHEETS
On Error Resume Next
Wb.Unprotect Password:=LCCPassword
ContratoSheet.Unprotect Password:=LCCPassword
PainelSheet.Unprotect Password:=LCCPassword
BaseUploadSheet.Unprotect Password:=LCCPassword
ParamSheet.Unprotect Password:=LCCPassword
ComputingSheet.Unprotect Password:=LCCPassword
WSSheet.Unprotect Password:=LCCPassword
CLSheet.Unprotect Password:=LCCPassword
HeaderSheet.Unprotect Password:=LCCPassword
StopOverSheet.Unprotect Password:=LCCPassword
On Error GoTo 0

'GETTING NEXT FREE ROW INDEX ON THE HEADER BASE
BaseNextFreeRow = HeaderSheet.Cells(HeaderSheet.Rows.Count, 1).End(xlUp).Row + 1
'COPYING HEADER TRANSFER CONTENT
HeaderTransferSheet.Rows("4:5").Copy
'PASTING HEADER TRANSFER CONTENT AS VALUES ON THE HEADER BASE
HeaderSheet.Rows(BaseNextFreeRow).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'GETTING LAST NON-EMPTY ROW INDEX ON THE STOPOVER TRANSFER CONTENT
LastRowWithContent = StopOverTransferSheet.Cells(StopOverTransferSheet.Rows.Count, "E").End(xlUp).Row
'GETTING NEXT FREE ROW INDEX ON THE STOPOVER BASE
BaseNextFreeRow = StopOverSheet.Cells(StopOverSheet.Rows.Count, 1).End(xlUp).Row + 1
'COPYING STOPOVER TRANSFER CONTENT
StopOverTransferSheet.Rows("4:" & LastRowWithContent).Copy
'PASTING STOPOVER TRANSFER CONTENT AS VALUES ON THE STOPOVER BASE
StopOverSheet.Rows(BaseNextFreeRow).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'CLEARING PARAM AND PANEL VALUES
ParamSheet.Range("K5").ClearContents
ParamSheet.Range("O12").ClearContents
ParamSheet.Range("H30:H32").ClearContents
ParamSheet.Range("G28:J28").ClearContents
ParamSheet.Range("F22:J22").ClearContents
PainelSheet.Range("G7").MergeArea.ClearContents
PainelSheet.Range("M7").MergeArea.ClearContents
PainelSheet.Range("P7").ClearContents

'CLEARING WORKING SCHEDULE VALUES
WSSheet.Range("A1:AD1000").ClearContents

'CLEARING WS VALUES FROM APOIO
ApoioSheet.Range("J10").ClearContents
ApoioSheet.Range("J11").ClearContents
ApoioSheet.Range("J12").ClearContents

'CLEARING CHECKLIST VALUES
CLSheet.Range("A1:T1000").ClearContents

'CLEARING CL VALUES FROM APOIO
ApoioSheet.Range("N10").ClearContents
ApoioSheet.Range("N11").ClearContents
ApoioSheet.Range("N12").ClearContents

'RESETTING COMPUTING SHEET
ApoioSheet.Range(ComputingRange).Copy
ComputingSheet.Select
ComputingSheet.Range("E34").PasteSpecial Paste:=xlPasteAll
ActiveSheet.Range("D2").Select
Application.CutCopyMode = False

'ADDING PROTECTION TO ALL SHEETS
On Error Resume Next
PainelSheet.Protect Password:=LCCPassword
ContratoSheet.Protect Password:=LCCPassword
BaseUploadSheet.Protect Password:=LCCPassword
ParamSheet.Protect Password:=LCCPassword
ComputingSheet.Protect Password:=LCCPassword
WSSheet.Protect Password:=LCCPassword
CLSheet.Protect Password:=LCCPassword
HeaderSheet.Protect Password:=LCCPassword
StopOverSheet.Protect Password:=LCCPassword
ReportSheet.Protect Password:=LCCPassword
Wb.Protect Password:=LCCPassword
On Error GoTo 0

'STOPPING ON PANEL SHEET
PainelSheet.Select

'TURNING OFF THE SCREEN FREEZING FEAT
Application.ScreenUpdating = True

MsgBox ("Data recording concluded. You may begin a new Cost Control analysis")

End Sub

Variables and Objects

  • Wb (Workbook): The current workbook (ThisWorkbook).

  • Port (String): The port identifier derived from the calling button.

  • ApoioSheet (Worksheet): The support sheet for the specified port.

  • ContratoSheet (Worksheet): The sheet containing contract data.

  • PainelSheet (Worksheet): The initial page sheet with primary data.

  • BaseUploadSheet (Worksheet): The sheet for dataset uploads.

  • ParamSheet (Worksheet): The parameters sheet.

  • ComputingSheet (Worksheet): The core sheet where calculations are performed.

  • HeaderTransferSheet (Worksheet): The sheet for header transfer data.

  • StopOverTransferSheet (Worksheet): The sheet for stopover transfer data.

  • HeaderSheet (Worksheet): The cumulative header database sheet.

  • StopOverSheet (Worksheet): The cumulative stopover database sheet.

  • WSSheet (Worksheet): The Working Schedule sheet.

  • CLSheet (Worksheet): The Checklist sheet.

  • ReportSheet (Worksheet): The report sheet.

  • LCCPassword (String): The password for protecting/unprotecting sheets.

  • PassPrompt (String): The user-entered password prompt.

  • BaseNextFreeRow (Long): The next free row index in the cumulative database.

  • LastRowWithContent (Long): The last non-empty row in the transfer data.

  • ComputingRange (String): The range of the computing area.


Workflow
1.Assign Variables:

  • Set references to the current workbook, relevant sheets, and define key strings for sheet names and target ranges.

  • Retrieve the password for protecting/unprotecting sheets.

  • Determine the port identifier from the calling button.


2.Screen Updating and Consistency Checks:

  • Disable screen updating to prevent screen flicker.

  • Check for any 'Nok' statuses in the consistency panel.

  • Ensure the year of operation is added in the PainelSheet.

  • Prompt the user for the password and validate it.


3.Unprotect Sheets:

  • Remove protection from all relevant sheets to allow data manipulation.


4.Copy and Paste Data:

  • Header Transfer Data: Copy header transfer data from HeaderTransferSheet and paste it into HeaderSheet.

  • Stopover Transfer Data: Copy stopover transfer data from StopOverTransferSheet and paste it into StopOverSheet.
     

5.Clear Input Data:

  • Clear contents from ParamSheet, PainelSheet, WSSheet, CLSheet, and ApoioSheet to prepare for a new analysis.

 

6.Reset Computing Sheet:

  • Copy the original computing structure from ApoioSheet and paste it into ComputingSheet.


7.Protect Sheets:

  • Reapply protection to all relevant sheets to maintain data integrity and security.


8.Stop on Panel Sheet:

  • Select the PainelSheet to provide a clear end state for the user.


9.Screen Updating and Confirmation:

  • Re-enable screen updating.

  • Display a message confirming that the data recording is concluded and the system is ready for a new analysis.
     

Key Points

  • Dynamic Data Handling: The subroutine dynamically records the results of the analysis and prepares the system for a new analysis.

  • Sheet Protection Management: Manages the protection status of sheets to ensure secure data manipulation and integrity.

  • User Feedback: Provides confirmation messages to inform the user of the successful recording of data and readiness for a new analysis.

Subroutine Documentation: 'BringingComputing'
Anchor 7

The BringingComputing subroutine in LCC (Lite Cost Control) allows users with managerial privileges to bring the Computing Engine to the ADM Panel for modifications. This process ensures that any ongoing data is cleared, and the current state of the Computing Engine is loaded into the ADM Panel for editing. The subroutine also manages sheet visibility and protection to maintain data integrity.

Sub BringingComputing()

Dim Wb As Workbook
Dim ADMPanelSheet As Worksheet
Dim CallerMotherSlot As Worksheet
Dim AllApoios As Worksheet

Dim CallerSlots As Worksheet
Dim CallerComputing As Worksheet

Dim ApoioSheet As Worksheet
Dim PainelSheet As Worksheet
Dim ParamSheet As Worksheet
Dim WSBase As Worksheet
Dim CLBase As Worksheet

Dim CallerPort As String

Dim PortList As Variant

Dim counter As Integer

Dim Confirmation As String
Dim ConfirmationTwo As String

Dim ComputingRange As String

Dim VisibleTab1 As String
Dim VisibleTab2 As String
Dim VisibleTab3 As String

Dim LCCPassword As String

Set Wb = ThisWorkbook
Set ADMPanelSheet = Wb.Sheets("ADM_Panel")

PortList = Z4_PORTS.Ports()
CallerPort = Right(Application.Caller, 3)
LCCPassword = A2_LCCPassword.LCCPassword()

Set CallerComputing = Wb.Sheets("P_" & CallerPort & "_COMP")
Set ApoioSheet = Wb.Sheets("Apoio_" & CallerPort)
Set PainelSheet = Wb.Sheets("PAINEL_" & CallerPort)
Set ParamSheet = Wb.Sheets("P_" & CallerPort & "_PARA")
Set WSBase = Wb.Sheets("BASE_WS_" & CallerPort)
Set CLBase = Wb.Sheets("BASE_CL_" & CallerPort)

counter = 0
VisibleTab1 = "ADM_Panel"
VisibleTab2 = "CONTRATOS_" & CallerPort
VisibleTab3 = "P_" & CallerPort & "_PARA"

For i = 0 To 3
Set AllApoios = Wb.Sheets("Apoio_" & PortList(LBound(PortList) + i))
counter = counter + AllApoios.Range("B38").Value
Next

Set AllApoios = Nothing

If counter > 0 Then
MsgBox CallerPort & " COMPUTING engine cannot be reached while another Computing editing process is taking place. Conclude it first and try again."
Exit Sub
Else
Confirmation = MsgBox("Are you sure you want to edit the " & CallerPort & " COMPUTING engine?", vbYesNo)

If Confirmation = vbYes Then
ConfirmationTwo = MsgBox("Any data you might have in the " & CallerPort & " Computing engine and memory slots will be permanently lost. Are you sure you wish to continue?", vbYesNo)

If ConfirmationTwo = vbNo Then
Exit Sub
ElseIf ConfirmationTwo = vbYes Then
Application.ScreenUpdating = False

On Error Resume Next
Wb.Unprotect Password:=LCCPassword
WSBase.Unprotect Password:=LCCPassword
CLBase.Unprotect Password:=LCCPassword
ParamSheet.Unprotect Password:=LCCPassword
CallerComputing.Unprotect Password:=LCCPassword
On Error GoTo 0

' Clearing parameter and panel values
ParamSheet.Range("K5").ClearContents
ParamSheet.Range("O12").ClearContents
ParamSheet.Range("H30:H32").ClearContents
ParamSheet.Range("G28:J28").ClearContents
ParamSheet.Range("F22:J22").ClearContents

PainelSheet.Range("G7").MergeArea.ClearContents
PainelSheet.Range("M7").MergeArea.ClearContents
PainelSheet.Range("P7").ClearContents

' Clearing Working Schedule values
WSBase.Range("A1:AD1000").ClearContents

' Clearing WS values from Apoio
ApoioSheet.Range("J10").ClearContents
ApoioSheet.Range("J11").ClearContents
ApoioSheet.Range("J12").ClearContents

' Clearing Checklist values
CLBase.Range("A1:T1000").ClearContents

' Clearing CL values from Apoio
ApoioSheet.Range("N10").ClearContents
ApoioSheet.Range("N11").ClearContents
ApoioSheet.Range("N12").ClearContents

ApoioSheet.Range("B38") = 1
ApoioSheet.Range(ApoioSheet.Range("B17").Value).Copy
ADMPanelSheet.Range("E34").PasteSpecial xlPasteAll
Application.CutCopyMode = False

ADMPanelSheet.Range("E32") = CallerPort
ADMPanelSheet.Select
ADMPanelSheet.Range("A3").Select
Application.ActiveWindow.DisplayWorkbookTabs = True

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> VisibleTab1 And ws.Name <> VisibleTab2 And ws.Name <> VisibleTab3 Then
ws.Visible = xlSheetHidden
Else
ws.Visible = xlSheetVisible
End If
Next ws

MsgBox "The " & CallerPort & " COMPUTING Engine has been brought over successfully. Scroll down to edit it."
Application.ScreenUpdating = True
Exit Sub
End If
ElseIf Confirmation = vbNo Then
Exit Sub
End If
End If
End Sub

Variables and Objects

  • Wb (Workbook): The current workbook (ThisWorkbook).

  • ADMPanelSheet (Worksheet): The sheet where the Computing Engine will be brought for modification.

  • AllApoios (Worksheet): The support sheet for all ports.

  • CallerSlots (Worksheet): The memory slot sheet for the calling port.

  • CallerComputing (Worksheet): The Computing Engine sheet for the calling port.

  • ApoioSheet (Worksheet): The support sheet for the specified port.

  • PainelSheet (Worksheet): The initial page sheet with primary data for the specified port.

  • ParamSheet (Worksheet): The parameters sheet for the specified port.

  • WSBase (Worksheet): The Working Schedule sheet for the specified port.

  • CLBase (Worksheet): The Checklist sheet for the specified port.

  • CallerPort (String): The port identifier derived from the calling button.

  • PortList (Variant): The list of ports.

  • counter (Integer): A counter to track ongoing Computing editing processes.

  • Confirmation (String): The first user confirmation prompt.

  • ConfirmationTwo (String): The second user confirmation prompt.

  • ComputingRange (String): The range of the computing area.

  • VisibleTab1, VisibleTab2, VisibleTab3 (String): The sheets to be kept visible during the process.

  • LCCPassword (String): The password for protecting/unprotecting sheets.


Workflow

1.Assign Variables:

  • Set references to the current workbook, relevant sheets, and define key strings for sheet names and target ranges.

  • Retrieve the password for protecting/unprotecting sheets.

  • Determine the port identifier from the calling button.

  • Initialize counters and visibility settings for relevant sheets.


2.Check for Ongoing Processes:

  • Loop through all Apoio sheets to check if any other Computing editing process is taking place. If found, display a message and exit.

3.User Confirmation:

  • Prompt the user for confirmation to edit the Computing Engine.

  • If the user confirms, proceed to the next confirmation prompt to warn about data loss.

  • If the user cancels at any point, exit the subroutine.

 

4.Screen Updating and Unprotect Sheets:

  • Disable screen updating to prevent screen flicker.

  • Unprotect all relevant sheets to allow data manipulation.
     

5.Clear Data:

  • Clear contents from ParamSheet, PainelSheet, WSBase, CLBase, and ApoioSheet to prepare for loading the Computing Engine.

 

6.Load Computing Engine to ADM Panel:

  • Mark the process as ongoing in ApoioSheet.

  • Copy the Computing Engine from ApoioSheet and paste it into ADMPanelSheet.

  • Update the ADMPanelSheet with the current port identifier.

​

7.Manage Sheet Visibility:

  • Ensure only relevant sheets are visible during the editing process.

  • Hide all other sheets.


8.User Notification and Screen Updating:

  • Display a message confirming the successful transfer of the Computing Engine to the ADM Panel.

  • Re-enable screen updating.

 


Key Points

  • Dynamic Data Handling: The subroutine dynamically loads the Computing Engine into the ADM Panel for editing.

  • Sheet Protection Management: Manages the protection status of sheets to ensure secure data manipulation and integrity.

  • User Feedback: Provides confirmation messages to inform the user of the ongoing process and successful transfer of the Computing Engine.

Subroutine Documentation: 'UpdatingComputing'
Anchor 8

The UpdatingComputing subroutine in LCC (Lite Cost Control) applies modifications made in the ADM Panel to all instances of the Computing Engine across the workbook. It ensures that the changes are uniformly updated and applied to the main Computing Engine, support sheets, and all memory slots.

Sub UpdatingComputing()

Dim Wb As Workbook
Dim ADMPanel As Worksheet
Dim ApoioSheet As Worksheet
Dim SlotSheet As Worksheet
Dim PainelSheet As Worksheet
Dim WSSheet As Worksheet
Dim CLSheet As Worksheet
Dim ParamSheet As Worksheet
Dim ComputingSheet As Worksheet

Dim CallerPort As String
Dim Confirmation As String
Dim ComputingRange As String
Dim LCCPassword As String

Dim VisibleTab1 As String
Dim VisibleTab2 As String
Dim VisibleTab3 As String

Set Wb = ThisWorkbook
Set ADMPanel = Wb.Sheets("ADM_Panel")

CallerPort = ADMPanel.Range("E32").Value
LCCPassword = A2_LCCPassword.LCCPassword()

If CallerPort = "" Then
MsgBox "No Computing Engine is being edited right now."
Exit Sub
End If

VisibleTab1 = "ADM_Panel"
VisibleTab2 = "CONTRATOS_" & CallerPort
VisibleTab3 = "P_" & CallerPort & "_PARA"

Confirmation = MsgBox("Are you sure you want to apply the changes you've made to " & CallerPort & " COMPUTING engine?", vbYesNo)

If Confirmation = vbNo Then
Exit Sub
ElseIf Confirmation = vbYes Then
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws

Set ApoioSheet = Wb.Sheets("Apoio_" & CallerPort)
Set PainelSheet = Wb.Sheets("PAINEL_" & CallerPort)
Set WSSheet = Wb.Sheets("BASE_WS_" & CallerPort)
Set CLSheet = Wb.Sheets("BASE_CL_" & CallerPort)
Set ParamSheet = Wb.Sheets("P_" & CallerPort & "_PARA")
Set ComputingSheet = Wb.Sheets("P_" & CallerPort & "_COMP")

ComputingRange = ApoioSheet.Range("B17").Value

ADMPanel.Range(ComputingRange).Copy
ApoioSheet.Range("E34").PasteSpecial xlPasteAll

For i = 1 To 5
Set SlotSheet = Wb.Sheets("S" & i & "_" & CallerPort)

If SlotSheet.Range("E10").Value <> " - " Then
PainelSheet.Range("G7").MergeArea.Copy
SlotSheet.Range("J10").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

PainelSheet.Range("M7").MergeArea.Copy
SlotSheet.Range("O10").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

PainelSheet.Range("P7").Copy
SlotSheet.Range("R10").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

ParamSheet.Range("F4:P34").Copy
SlotSheet.Range("V4").PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False

ApoioSheet.Range("J10:J12").Copy
SlotSheet.Range("AK4").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

WSSheet.Columns("A:AD").Copy
SlotSheet.Columns("AR").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

ApoioSheet.Range("N10:N12").Copy
SlotSheet.Range("AK14").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

CLSheet.Columns("A:T").Copy
SlotSheet.Columns("CA").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

ADMPanel.Range(ComputingRange).Copy
SlotSheet.Range("E34").PasteSpecial xlPasteAll
Application.CutCopyMode = False

Else
ADMPanel.Range(ComputingRange).Copy
SlotSheet.Range("E34").PasteSpecial xlPasteAll
Application.CutCopyMode = False
End If
Next i

ADMPanel.Range(ComputingRange).Copy
ComputingSheet.Select
ComputingSheet.Range("E34").PasteSpecial xlPasteAll
ComputingSheet.Range("D2").Select
Application.CutCopyMode = False

ComputingSheet.Protect Password:=LCCPassword
ApoioSheet.Range("B38") = 0

ADMPanel.Select
ADMPanel.Range("E34:T1000").Clear
ADMPanel.Range("E32").ClearContents
ADMPanel.Range("A3").Select

On Error Resume Next
WSSheet.Protect Password:=LCCPassword
CLSheet.Protect Password:=LCCPassword
ParamSheet.Protect Password:=LCCPassword
ComputingSheet.Protect Password:=LCCPassword
Wb.Protect Password:=LCCPassword
On Error GoTo 0

Application.ActiveWindow.DisplayWorkbookTabs = False
Application.ScreenUpdating = True

MsgBox CallerPort & " COMPUTING engine has been updated successfully!"
End If
End Sub

Variables and Objects
Wb (Workbook): The current workbook (ThisWorkbook).
ADMPanel (Worksheet): The ADM Panel sheet where changes are made.
ApoioSheet (Worksheet): The support sheet for the specified port.
SlotSheet (Worksheet): The memory slot sheet for the specified port.
PainelSheet (Worksheet): The initial page sheet with primary data for the specified port.
WSSheet (Worksheet): The Working Schedule sheet for the specified port.
CLSheet (Worksheet): The Checklist sheet for the specified port.
ParamSheet (Worksheet): The parameters sheet for the specified port.
ComputingSheet (Worksheet): The core sheet where calculations are performed.
CallerPort (String): The port identifier derived from the ADM Panel.
Confirmation (String): The user confirmation prompt.
ComputingRange (String): The range of the computing area.
LCCPassword (String): The password for protecting/unprotecting sheets.
VisibleTab1, VisibleTab2, VisibleTab3 (String): The sheets to be kept visible during the process.


Workflow

1.Assign Variables:

  • Set references to the current workbook, relevant sheets, and define key strings for sheet names and target ranges.

  • Retrieve the password for protecting/unprotecting sheets.

  • Determine the port identifier from the ADM Panel.


2.Check for Active Editing:

  • Check if any Computing Engine is currently being edited. If not, display a message and exit.


3.User Confirmation:

  • Prompt the user for confirmation to apply changes to the Computing Engine. If the user cancels, exit the subroutine.

 

4.Screen Updating and Unprotect Sheets:

  • Disable screen updating to prevent screen flicker.

  • Unprotect all relevant sheets to allow data manipulation.


5.Apply Changes to Apoio and Slot Sheets:

  • Copy the updated Computing Engine from the ADM Panel to the Apoio sheet and all memory slots.

  • Update parameter and panel values in each slot.


6.Reset Computing Sheet:

  • Copy the updated Computing Engine to the main Computing sheet.

  • Protect the Computing sheet.


7.Clear ADM Panel and Finalize:

  • Clear the contents of the ADM Panel and reset the editing status.

  • Hide workbook tabs and re-enable screen updating.

  • Display a message confirming the successful update of the Computing Engine.


Key Points

  • Dynamic Data Handling: The subroutine dynamically applies changes to all instances of the Computing Engine across the workbook.

  • Sheet Protection Management: Manages the protection status of sheets to ensure secure data manipulation and integrity.

  • User Feedback: Provides confirmation messages to inform the user of the ongoing process and successful update of the Computing Engine.

Anchor 9

ADM Panel

The ADM Panel in LCC (Lite Cost Control) is a specialized tab designed for users with managerial privileges to modify the Computing Engine. This panel allows managers to bring the entire computing structure for updates and adjustments. Once modifications are complete, a button click updates the Computing Engine tab, the Slot Mãe, and all instances of the computing engine in the memory slot tabs. This feature ensures that any structural changes are consistently applied across all relevant sections of LCC, maintaining uniformity and accuracy.

©2025 by BeNext Solutions.

bottom of page