+ Reply to Thread
Results 1 to 5 of 5

Optimize long code

Hybrid View

  1. #1
    Registered User
    Join Date
    03-07-2016
    Location
    Spain
    MS-Off Ver
    2013
    Posts
    16

    Optimize long code

    Hello,

    I attach a file with a userform which is working good.

    The problem is that I think there is too much code for doing that action, so I would like you to ask if you might know a shortest code with loop or similar.

    Main problems:

    - Labels are not consecutive
    - Cells doesn't have a logic order
    - The code only is done until february, if I go on til December it gets soo long.

    Hope I explained good,

    Thanks!! And merry christmas
    Attached Files Attached Files

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Optimize long code

    The first thing I would do is rationalize the names of the controls on the userform.
    You could use code like this. Note that this code is to be in a normal module and run at design time. Once all the controls have decent names it can be discarded. The names of the controls will have been permanently changed.

    You'll have to change the StartControlName and the Prefix for each row of your grid.
    Similar code could take care of the headers and the total row.


    Sub test()
        Dim thisLabel As MSForms.Control
        Dim nextLabel As MSForms.Control
        Dim colIndex As Long
        Dim prefix As String
        Dim StartControlName As String
        
        StartControlName = "Label1701"
        prefix = "lblGrid1_"
        colIndex = 1
        
        With ThisWorkbook.VBProject.VBComponents("Userform1").Designer
    
            Set thisLabel = .Controls(StartControlName)
            
            Do
                thisLabel.Name = prefix & colIndex
                colIndex = colIndex + 1
                Set nextLabel = NextToRight(thisLabel)
            Loop Until nextLabel Is Nothing
        End With
    End Sub
    
    Function NextToRight(aControl As MSForms.Control) As MSForms.Control
        Dim oneControl As MSForms.Control
        Dim RightVal As Single
        RightVal = 10000
        For Each oneControl In aControl.Parent.Controls
            If (Abs(oneControl.Top - aControl.Top) < 4) Then
                If aControl.Left < oneControl.Left Then
                    If oneControl.Left < RightVal Then
                        Set NextToRight = oneControl
                        RightVal = oneControl.Left
                    End If
                End If
            End If
        Next oneControl
    End Function
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Optimize long code

    
    Public ChangeFlag As Boolean
    Public Filled As Variant
    
    Private Sub cboMonth_Change()
    
    'This skips this Macro when the Userform is first activated
    If ChangeFlag = True Then GoTo Quit
    
    'This is required to store the row numbers of the data on the sheet
    Pos = Split("6,11,14,23,25,27", ",")
    
    'Is this row empty?
    If Filled(cboMonth.ListIndex) = 0 Then
    
    'Fill Row
    Filled(cboMonth.ListIndex) = 1
    
    For Count = 0 To 5
    
    'Load the Textbox
    Me.Controls("Textbox" & Count + cboMonth.ListIndex * 6 - 5).Value = Sheets(1).Cells(Pos(Count), cboMonth.ListIndex + 1).Value
    
    'Add the New Data to the Summing Text Box at the bottom of the Userform
    T1 = Me.Controls("Textbox" & 73 + Count).Value + Sheets(1).Cells(Pos(Count), cboMonth.ListIndex + 1).Value
    Me.Controls("Textbox" & 73 + Count).Value = T1
    
    Next
    
    Else
    
    'Clear Row
    Filled(cboMonth.ListIndex) = 0
    
    For Count = 0 To 5
    
    'Load the Textbox
    Me.Controls("Textbox" & Count + cboMonth.ListIndex * 6 - 5).Value = 0
    
    'Subtract the New Data from the Summing Text Box at the bottom of the Userform
    T1 = Me.Controls("Textbox" & 73 + Count).Value - Sheets(1).Cells(Pos(Count), cboMonth.ListIndex + 1).Value
    Me.Controls("Textbox" & 73 + Count).Value = T1
    
    Next
    
    End If
    
    Quit:
    
    End Sub
    
    Private Sub UserForm_Initialize()
    
    'This is my database to log which months have been added to the Userform
    Filled = Split("0,0,0,0,0,0,0,0,0,0,0,0", ",")
    
    ChangeFlag = True
    cboMonth.List = Array("Select", "JANUARY", "FEBRUARY", "MARCH", "APRIL", "MAY", "JUNE", "JULY", "AUGUST", "SEPTEMBER", "OCTOBER", "NOVEMBER", "DECEMBER")
    cboMonth.ListIndex = 0
    ChangeFlag = False
    End Sub
    Attached Files Attached Files
    Last edited by mehmetcik; 12-22-2016 at 01:39 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Optimize long code

    I've renamed the controls, added Labels to the Bus and Taxi columns on the user form.

    I think that this will do what you want.

    Option Explicit
    
    Private Sub cboMonth_Change()
        Dim i As Long, j As Long
        For i = 1 To 12
            For j = 1 To 6
                GridLabel(i, j).Caption = vbNullString
            Next j
        Next i
        If 0 < cboMonth.ListIndex Then
            Call FillGridRow(cboMonth.ListIndex)
        End If
        Call fillTotals
    End Sub
    
    Sub fillTotals()
        Dim j As Long
        For j = 1 To 6
            Me.Controls("lblTotal" & j).Caption = TotalValue(cboMonth.Text, j)
        Next j
    End Sub
    Sub FillGridRow(rowNum As Long)
        Dim i As Long
        For i = 1 To 6
            GridLabel(rowNum, i).Caption = GridValue(rowNum, i)
        Next i
    End Sub
    
    Function GridValue(rIndex As Long, cIndex As Long) As Variant
        Dim MonthVal As String
        Dim ColHeader As String
        Dim MonthCol As Range
        Dim HeaderRow As Range
        
        GridValue = vbNullString
        
        MonthVal = Trim(Me.Controls("lblMonth" & Format(rIndex, "00")).Caption)
        ColHeader = Trim(Me.Controls("lblHeader" & cIndex).Caption)
        
        With Hoja1.Cells
            Set MonthCol = .Find(MonthVal, lookat:=xlWhole, MatchCase:=False)
            Set HeaderRow = .Find(ColHeader, lookat:=xlWhole, MatchCase:=False)
        End With
        
        If Not (MonthCol Is Nothing Or HeaderRow Is Nothing) Then
            GridValue = Application.Intersect(MonthCol.EntireColumn, HeaderRow.EntireRow).Value
        End If
    End Function
    
    Function TotalValue(MonthVal As String, cIndex As Long) As Variant
        Dim ColHeader As String
        Dim MonthCol As Range
        Dim HeaderRow As Range
        
        TotalValue = vbNullString
        
        ColHeader = Trim(Me.Controls("lblHeader" & cIndex).Caption) & " ACC"
        
        With Hoja1.Cells
            Set MonthCol = .Find(MonthVal, lookat:=xlWhole, MatchCase:=False)
            Set HeaderRow = .Find(ColHeader, lookat:=xlWhole, MatchCase:=False)
        End With
        
        If Not (MonthCol Is Nothing Or HeaderRow Is Nothing) Then
            TotalValue = Application.Intersect(MonthCol.EntireColumn, HeaderRow.EntireRow).Value
        End If
    End Function
    
    Function GridLabel(rIndex As Long, cIndex As Long) As MSForms.Label
        Set GridLabel = Me.Controls("lblGrid" & rIndex & "_" & cIndex)
    End Function
    
    
    Private Sub UserForm_Initialize()
    
    cboMonth.List = Array("Select", "JANUARY", "FEBRUARY", "MARCH", "APRIL", "MAY", "JUNE", "JULY", "AUGUST", "SEPTEMBER", "OCTOBER", "NOVEMBER", "DECEMBER")
    cboMonth.ListIndex = 0
    
    End Sub
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-07-2016
    Location
    Spain
    MS-Off Ver
    2013
    Posts
    16

    Re: Optimize long code

    sorry for my late reply,

    Thanks mikerickson and mehmetcik! appreciate so much your time and effort.

    Both of you solved my problems, I will probably use both of your codes as you opened my mind in other issues.

    Again, thanks for your time and have a good christmas

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. New to VBA programming and need help to Optimize VBA code
    By snuffnchess in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2016, 07:44 AM
  2. Optimize a code
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2015, 05:31 PM
  3. Optimize code
    By DarkKnightLupo in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-19-2014, 08:58 AM
  4. Optimize a slow code...
    By benoitly in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2013, 01:44 PM
  5. optimize the Code needs to run faster
    By farrukh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2012, 10:40 AM
  6. Optimize code
    By miso.dca in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2011, 03:35 PM
  7. Optimize VBA code
    By doodlebug in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2007, 07:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1