Code Review Request

L

Legacy 373233

Guest
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,250
Office Version
365
Platform
Windows
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.
 
L

Legacy 373233

Guest
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:
Code:
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
 
L

Legacy 373233

Guest
Sorry. Don't know how to edit posts.

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

Thanks
 

ask2tsp

Active Member
Joined
Feb 18, 2015
Messages
484
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.
Code:
    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
 
L

Legacy 373233

Guest
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
Code:
DateList.RowSource = "=Dates"
Instead i use
Code:
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)

Code:
'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
 

Forum statistics

Threads
1,082,243
Messages
5,363,972
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top