New answers tagged

0

Your code is still laced with very ugly, impenetrable prefixes and other 'hungation nototation' stuff.. Try comparing the readability of your code with my TryGetSize function '@Description("Returns the count of items in an iterable") Public Function TryGetSize(ByVal ipIterable As Variant, Optional ByRef iopResult As ResultLong, Optional ByVal ...


0

NOTE: Per the CodeReview rules, I am not allowed to modify my original question with suggestions and improvements. And instead, I'm required to post an answer showing any substantial adjustments and/or enhancements. Hence, the post below. Thanks to both comments and feedback by @HackSlash and @FreeFlow, it appears the original overall implementation is ...


1

@Hackslash The assertion of lngLBound = 0 lngUBound = -1 for an uninitialised array is incorrect. The code below demonstrates why Public Sub ArrayTypeInfo() Dim myUbound As Long Dim myUboundMsg As String On Error Resume Next Debug.Print , , , "TypeName", "VarType", "IsArray", "IsNull", "IsEmpty&...


1

Once you know you have an array it looks to me that this comparison is unecessary: If (lngLBound <= lngUBound) Then lngSize = lngUBound - lngLBound + 1 'Non-Empty, so return size End If The reason being that if you have an uninitialized array then lngLBound = 0 lngUBound = -1 So your equation evaluates to: lngSize = -1 - 0 + 1 Which means that ...


3

Without knowing what SolverReset, SolverAdd, SolverOK, SolverSolve, and Calculate are doing specifically, there are limits to how much optimization can be suggested. That said, there are some general comments that may be useful that can have some effect on speed, but probably not of the magnitude you are hoping for. (Best Practice) Always declare Option ...


0

A VBA Lookup The two 'Source Lookup Ranges' ("F2:Flr", "B2:Blr"), the ranges where you try to find a match, stay as ranges (because Application.Match is faster on ranges) while the rest is written to arrays. The results are written to the 'Destination Array' (dData) which is then, in one go, written to the 'Destination Range'. Adjust (...


0

Suggestion: use named ranges if possible to make your ranges more descriptive (and also diminish the risk of confusion). And perhaps try the UsedRange argument rather than selecting whole columns down to the last row. The overly broad selection is wasteful and could be a cause of slowdown. I am on Linux now, so I can't test. But make sure you are not making ...


0

This should run faster. If any of the applications I turn off at the start is messing up with the functionality of the macro delete it. Sub Formula() 'turn off applications not required Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.EnableEvents = False Activesheet....


2

I'm in agreement with the comments/answer provided by @HackSlash and have used the @HackSlash version of the Subroutine in this answer. And, the @VBasic2008 version certainly is an improvement as well and demonstrates a more efficient implementation while reducing the levels of nesting from 4 to 3. That said, the original title of the post implied a ...


1

VBA SumIf in Columns of Multiple Tables Option Explicit Sub GenerateBOM() Const dName As String = "Bill of Materials" Const dtblName As String = "BOM" Const dlName As String = "Part Number" Const drName As String = "Project Totals" Const slName As String = "Part Number" ...


4

Use descriptive variable names: When choosing variables names always avoid reserved words. Err on the side of verbosity. For example: Don't use the name row for a variable as that is the name of a property you are using. This is what forces the row in tblBOM.HeaderRowRange.row to be lower case. It also leads to the confusing statement row.row Move ...


Top 50 recent answers are included