Search Results

You are correct that this code can be simplified. The posted code was almost certainly created using Copy-Paste (19 times) followed by changing a single variable within the block. When code is creat …

The code cycles thru the remainder of dArray even after meeting the logic condition for setting an mArray element to 'Anonymous'. This is inefficient since replacing the vbNullString once is suffici …

A simple search on 'Implement an Interface in Excel VBA' will provide the rest of the details to get to the following version of CFrameSorter: Option Explicit Implements IFrameSorter Private …

Option Explicit (Best Practice) Always declare Option Explicit at the top of your modules. This allows the compiler to flag the use of variables that have not been explicitly declared. This helps to …

The comments I have fall into three categories that relate to general programming practices that would be applicable to any language, not just VBA. Raw Strings Don't Repeat Yourself (DRY) Single …

I think the way you are doing this is reasonable. Regarding pre-existing blank sheets..The rewritten code below removes blank worksheet(s) in the importing workbook, after the selected workbooks' wor …

Some comments that you hopefully find useful: (Best Practice)Declare "Option Explicit" at the top of every module. This option requires that every variable used in the module is explicitly declared. …

be confirm that the DB to import is stored in the REGISTER tab of the file") 'Opening the File and copy to my workbook Dim fileName As Variant fileName = Application.GetOpenFilename(FileFilter:="Excel …

String) Dim timestamp As String, logEntry As String timestamp = Format(Now, "mm/dd/yyyy HH:mm:ss") logEntry = message & ": " & timestamp 'Append logEntry to a text file or write them out to an excel … Worksheets If TempSheetName = UCase(Sheet.Name) Then MsgBox "Reset before importing" Exit Sub End If Next Sheet 'File opening FilePath = Application.GetOpenFilename(FileFilter:="Excel …

When your users open this workbook, the Excel application/workbook becomes the initial User-Interface. …

It is always a speed improvement to set variables external to a loop so long as the value is remains constant within the loop. So, the first change to improve performance here would be: Prefer instant …

More information If you are interested in more information about best/better coding practices demonstrated using VBA, I would recommend you visit RubberDuck and consider using the free RubberDuck Excel …

You do not have look at more than about 2 Code Review answers to find posts that advocate two very important principles when writing code (in VBA or any other language). The "Do not repeat yourself" …

Here, copying a Range from a worksheet to update a PowerPoint Slide is repeated 7 times: 'code to copy range from excel sheet Sheets("Commercial-H1").Select Sheets("Commercial-H1").Range("B3:L220").Copy … PPPres.Slides.Add(1, ppLayoutTitle) PPslide.Select PPslide.Shapes(1).TextFrame.TextRange.Text = "Monthly P&L Report" PPslide.Shapes(2).TextFrame.TextRange.Text = "Commercial" 'back to excel …

The one class is trying to handle multiple types of textboxes. In this specific case it would seem a better approach to create a class for each textbox type. This keeps code specific to each textbox …