Hot answers tagged

4

Qualify your variables. I read your post and after copying/pasting the code I created a standard module named GlobalVariables so that I had O.Offset(GlobalVariables.OFFSET_ROW, GlobalVariables.OFFSET_COLUMN) that identified where those variables came from. This helps future-you or another coder reading your know where things are located. Your wall of ...


4

First I'd like to say that this is impressive work, overall pretty squeaky clean... despite the adjustments needed to make it build on x64 :) One enhancement I can see in terms of readability, would be to use PascalCase rather than camelCase for member names: inconsistent casing is distracting, because parameters and locals are usually camelCase, so a camel-...


4

Declarations My first comment is Option explicit. Every. Single. Time. Your first line of code is : Set objShell = CreateObject("WScript.Shell") Why? objshell is not declared or used. And while on the matter of declarations: Dim WDR, WDCheck, ShipmentID As Object declares WDR and WDCheck as Variant, not Object. Youi are writing a utility tool - ...


4

If Not DebugMode Then Resume CleanExit Else: Stop: Resume Avoid using the : instruction separator in code. That's good for golfing up quick throw-away code in the immediate pane, not for production code. Statement syntax is fine when the Then part is a single statement - if it gets any more complex than that, block syntax should be used for clarity and ...


3

On the whole nice work! This is actually a problem I've looked at in the past, and as a result I'd like to review the approach you've taken and the API/ way users can interact with your code. As I understand it you have 3 main classes Tables - responsible for instantiating a collection of Table objects (with some automagic methods to allow client code to ...


3

I don't see anything wrong so just a few shots in the dark: Are there any "hidden" data-driven automations like worksheet_change or whatever? You may disable event handling and a few more time comsuming services for the course of processing. Take a look at this: Speed up App I would not apply copying validations and formats and whatever on all the 16K ...


3

Since, there is an array of values, no need to iterate in the range, I think. Why not iterating between the array elements? Dim arr As Variant, sh As Worksheet, El As Variant Dim refVal As Variant, boolWrong As Boolean, strDif As String Set sh = ActiveSheet arr = Application.Index(sh.Range("A1:AA1").Value, 1, Array(1, 2, 3, 6, 11, 24, 27)) refVal = arr(1)...


3

Using a Dictionary to count the occurences of a single value is overkill. If the question was count the number of values that match the first then If FirstValue = cl.Value Then Count = Count + 1 would be far more efficient than using a Dictionary. I would like to know if all cells are equal to the first Cell in the Range It seems to me that you are over ...


3

I use the following sub to speed up processing: Public Sub AppSpeed(Optional iWhat As Long = xlDown) Dim bDir As Boolean bDir = True If iWhat = xlUp Then bDir = False On Error Resume Next With Application .Calculation = IIf(bDir, xlCalculationAutomatic, xlCalculationManual) .ScreenUpdating = bDir ....


3

Declare your variables right before you use them. For your cap variables, assuming these stand for caption (more on that with descriptive variables), you have a Declare, Populate, Use in a 1-2-3 pattern. Dim foo As String '<-- 1 Declare 'Code that does stuff foo = "bar" '<-- 2 Populate 'More code buzz.Caption = foo `<-- 3 Use You can, and IMO ...


2

Use this code to directly reference and open: Set NS = myolApp.GetNamespace("MAPI") NS.Logon Set myAppt = NS.GetItemFromID(EntryID) myAppt.Display Works like a dream.


2

It is possible to combine Application.Find with Application.Sum (arr2 in the code below) on the extracted array (arr in the code) to get the same result as Countif. The reason is that Application.Find returns an array filled with 1 if the value is found in the corresponding element (See picture below.) But Application.Find will return false positives if the ...


2

public class ReportData { public ReportData(IEnumerable<ReportColumn> columns) { Columns = columns ?? throw new ArgumentNullException("No valid columns available"); } public IEnumerable<ReportColumn> Columns { get; set; } } The Columns property should be get-only. public string ColumnName { get; set; } public object[] ...


2

It's tradeoffs. On one hand you get useful static methods in a toolbox, functions that don't accidentally show up in Excel's IntelliSense in the formula bar. On the other hand, you could get the very same out of a standard module with Option Private Module specified, minus the possibility of client code mistakenly attempting to create a New instance of the ...


2

I find relying on Sheet.Activate / Sheet.Deactivate to set/unset the sheetTables reference is rather frail, error-prone (miss an Activate event for whatever reason (Application.EnableEvents being toggled off, for example), and just like that the Change handler starts throwing error 91), and doesn't really make much sense: the table exists on Sheet1 as long ...


1

I am going to address some style issues. Do not compress lines Yes, the syntax allows you to write code like: Dim subtaskws As Worksheet: Set subtaskws = ThisWorkbook.Sheets("Sub Tasks") But it is harder to read and the assignment versus declaration is harder to see. This makes code harder to follow and maintain. Instead, be clear and explicit: Dim ...


1

A function able to set the column number according to the column header would be the next one. Please replace ActiveSheet from the Track worksheet definition Private Function HdN(strHead As String) As Long Dim hdArr As Variant, Track As Worksheet, i As Long, lastCol As Long Set Track = ActiveSheet 'Attention! define here your real sheet. I used it for ...


1

I think I found a solution that seemed the most elegant: Sub Test() 'Get a 1D-array from columns of interest Dim arr As Variant: arr = Application.Index(Range("A1:AA1").Value, 1, Array(1, 2, 3, 6, 11, 24, 27)) 'Check if all elements in array match the first element With Application If .Count(.Match(arr, Array(arr(1)), 0)) = 7 Then Debug.Print "...


1

try this code. It assumes that the worksheet with data is active. Sub ReplaceCodeReview235023() With ActiveSheet.Columns("D") .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-3]" End With End Sub


Only top voted, non community-wiki answers of a minimum length are eligible