Code Review Request

[Legacy 373233]

Good Morning/Afternoon All

This is my first post, My name is Jorge, but call me George, I work as a Controls Engineer at an Automotive Parts Supplier in Mexico.

Straight to the point.

I have a heavily scripted Excel File that serves as a Database for a client, i would like to ask somebody to have a look at the code. it is quite complex since various steps are taken to process a single row of information.

I don't know if it's easier to upload the file, (guess not, because safety related issues) or just post the code module by module.

It has:
-Two stages for data entry (for two different sets of information regarding one database record)
-3 levels of permissions (Guest, User and Admin) with a options dashboard for every user
-Database Automatic Reports
-Userforms can fill with a database entry, user can complete or modify them and the save the changes.
-User/Password Adding or Removing

Thanks in advance for your help. i'll take further steps to publish code if anybody is interested.

 

[Peter_SSs]

Jorge

Welcome to the MrExcel board!

The forum is not a private one-to-one service. If you want somebody to look at some code, you need to post the code and explain what you want any reviewer(s) to do.
Then you just have to hope that somebody (or more than one maybe) takes up the challenge.

Note also
- my signature block below regarding posting code (using Code Tags)
- the forum is basically intended for help with small parts of projects so if your code is very long, it is less likely that you will get any people responding.

 

[Legacy 373233]

Thanks for your answer.

It was not my intention to get private assistance. I thought i could share useful stuff that i have learned.

I have been using VBA for almost 4 years, i learned on my own, i just wanted to know if i am not doing something wrong when writing code.

Thanks for your help


Example:

Sub DBProcess()Dim dia1 As Long
Dim dia2 As Long
Dim min1 As Double
Dim min2 As Double
Dim res1 As Double
Dim res2 As Double


'Database Format and Data Prep
Worksheets("Set Ups").Select
Range("A2").Select
ActiveSheet.ListObjects("Main").Range.Select
If ActiveSheet.FilterMode Then
    Selection.AutoFilter
End If


Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.EntireRow.Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
If Not ActiveCell.Offset(0, 1).Value = 0 Then
    ActiveCell.Offset(1, 0).Select
End If
Selection.PasteSpecial Paste:=xlPasteFormats


'/////////////////////////////////////
'Duplicated ID Protection
'///////////////////////////


If ActiveCell.Value = "" Then
    ActiveCell.Offset(-1, 0).Value = ActiveCell.Offset(-2, 0).Value + 1
End If
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
'///////////////////////////////////
'Data Entry 
'///////////////////////////////////


'Paso 1: General Info
ActiveCell.Offset(0, 1).Select
ActiveCell.Offset(0, 0).Value = CDate(Prod.fecha1.Text)
ActiveCell.Offset(0, 1).Value = Prod.NumParte.Value
ActiveCell.Offset(0, 4).Value = Prod.AjPres.Text
ActiveCell.Offset(0, 5).Value = Prod.Linea.Text


'Routine to parse time formatted data
'Dia
dia1 = VBA.Format(Prod.fecha2.Value, "#")
hora1 = ((1 / 24) * Prod.Hrs1.Value)
min1 = ((1 / 24) * 60)) * Prod.Mins1.Value)
res1 = dia1 + hora1 + min1


ActiveCell.Offset(0, 6).Value = VBA.Format(res1, "DD/MM/YY HH:MM:SS")


ActiveCell.Offset(0, 7).Value = Prod.Scrap.Value
'Subrutina para el control de tiempo exacto
'Dia de firma
dia2 = VBA.Format(Prod.fecha3.Value, "#")
hora2 = ((1 / 24) * Prod.Hrs2.Value)
min2 = ((1 / 24) * 60))* Prod.Mins2.Value)
res2 = dia2 + hora2 + min2
ActiveCell.Offset(0, 8).Value = VBA.Format(res2, "DD/MM/YY HH:MM:SS")
'End 
'
ActiveCell.Offset(0, 9).Value = Prod.AjCS.Value
ActiveCell.Offset(0, 10).Value = Prod.Soporte.Value
ActiveCell.Offset(0, 11).Value = Prod.InspCal.Value
ActiveCell.Offset(0, 12).Value = VBA.Format(res2 - res1, "hh:mm:ss")
ActiveCell.Offset(0, 18).Value = Prod.Comments.Text
ActiveCell.Offset(0, 19).Value = Prod.Status.Value






Application.CutCopyMode = False








End Sub

 

[Legacy 373233]

Sorry. Don't know how to edit posts.

The code posted fills a table with data entered from a userform.

Thanks

 

[ask2tsp]

I would like to see a sample of the "Set Ups" sheet.

Since there is no form at this point, I return only the first part of your sub with comments added.

    Dim dia1 As Long
    Dim dia2 As Long
    Dim min1 As Double
    Dim min2 As Double
    Dim res1 As Double
    Dim res2 As Double


    'Database Format and Data Prep
    Worksheets("Set Ups").Select
    'Range("A2").Select <-- useless because immediately followed by another select
    ActiveSheet.ListObjects("Main").Range.Select
    If ActiveSheet.FilterMode Then
        Selection.AutoFilter
    End If

    Range("A2").Select
    Selection.End(xlDown).Select
    ActiveCell.EntireRow.Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    If Not ActiveCell.Offset(0, 1).Value = 0 Then
        ActiveCell.Offset(1, 0).Select
    End If
    Selection.PasteSpecial Paste:=xlPasteFormats

    '/////////////////////////////////////
    'Duplicated ID Protection
    '///////////////////////////

    '-------- these 4 lines -------
    'If ActiveCell.Value = "" Then
    '    ActiveCell.Offset(-1, 0).Value = ActiveCell.Offset(-2, 0).Value + 1
    'End If
    'ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
    '----- can also be written as -----
    With ActiveCell
        If .Value = "" Then
            .Offset(-1, 0).Value = .Offset(-2, 0).Value + 1
        End If
        .Value = .Offset(-1, 0).Value + 1
    End With
    
End Sub

 

[Legacy 373233]

Thank you for your answer.

The Set Ups sheet is just a table with that a userform. It keeps records of machine adjustments times.

i have found that Excel Crashes if i use this code to fill a combobox, run the userform and the click to open the combobox

DateList.RowSource = "=Dates"

Instead i use

Date1.List = Sheets("Masterlist").Range("Dates").Value

I use this code to determine if a date is older than another date. (Data is entered separately, days, hours and minutes)

'Determine Beginning Date.  (Prod is the name of the userform)
day1 = VBA.Format(Prod.date1.Value, "#")
hour1 = ((1 / 24) * Prod.Hrs1.Value)
min1 = ((1 / (24 * 60)) * Prod.Mins1.Value)
result1 = day1 + hour1 + min1

'Determine Ending Date.  (Prod is the name of the userform)
day2 = VBA.Format(Prod.date2.Value, "#")
hour2 = ((1 / 24) * Prod.Hrs2.Value)
min2 = ((1 / (24 * 60)) * Prod.Mins2.Value)
result2 = dia2 + hora2 + min2


If result2 < result1 Then
    MsgBox "The End Date cannot be more recent than starting date"
    Exit Sub
End If