New answers tagged vba
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
