ThunderFrame
  • Member for 6 years
  • Last seen more than 3 years ago
  • Australia
"Of three points supplied, is one a midpoint of the other two?"
15 votes

All of the previous answers seem to work on averages of each of the 2 numbers (expressed as either division or multiplication). But I'm working on averages of all 3 numbers, on the assertion that the ...

View answer
Exporting a UserForm from the VBE... and working around API bugs
7 votes

Well, here's a form saved from Excel 2013. Notice the VB_Exposed, VB_HelpID and VB_Description attributes. I set the Description and Help attributes using the Object Browser Context Menu ("Properties"...

View answer
Class Module to wrap up classic ADO call to SQL-server
7 votes

Suggestions: Don't assume the cursor type of the recordset, the user might want a different type. Consider making a ConnectionString class that lets the user use properties to get and let the ...

View answer
Retrieving database values through properties
5 votes

Properties in Standard Modules Standard modules can have Properties, but as @Rubberduck pointed out, they're not necessarily intuitive (although they do force you to qualify a call with the module ...

View answer
Immutable Object class in VBA – Creatable only through constructor and not via “New” keyword
5 votes

EDIT Mat's Mug has the best approach in going for an Interface and an add-in, but if you constrain the requirements to a self-contained solution, and you assume the user of the class is unfamiliar ...

View answer
Format RGB Long as hex string in VB6
5 votes

@Comintern got you some performance, and @MatsMug reviewed your code, but nobody had properly addressed your concern over the strings. Every Right$ call requires a concatenation of "0" and the Hex$ ...

View answer
Creating dialog with Access form instances
Accepted answer
4 votes

Using `Val` The Val function can run into problems when converting string values, depending upon the locale of the user. It is generally safer to use CDbl, but it will depend on the input. See ...

View answer
Else if .value = 1 to 92 code check
Accepted answer
4 votes

You can only use the To syntax with Case statements inside a Select block, and you can nest Select Blocks.... Note that indentation helps enormously, so I've changed the indentation to 2 spaces, ...

View answer
Load txt to Scripting.Dictionary
Accepted answer
4 votes

I assume you're working with a 40MB file instead of a 40GB file. The performance will vary greatly depending upon: the length of each line in the file the number of lines in the file the number of ...

View answer
Functional Framework
Accepted answer
4 votes

Implicit Variant Types You've been diligent with Type declarations, but in IFunction you have omitted the Variant type of the ParamArray: Public Function func(ParamArray args()) As Variant Use ...

View answer
CopyOf2DArray (Values Only)
4 votes

Am I missing something? Why were you using Set newArray = oldArray? Arrays are not objects, so you can copy them using a normal assignment: Sub TestLongArray() Dim a(1, 1) As Long a(0, 0) = 900 ...

View answer
Count rows of a table
4 votes

You can avoid the extra goto, and use inline error resetting, like this: Function CountCurrencyTrades() As Integer Dim sheetCurrencyTable As Worksheet Set sheetCurrencyTable = Worksheets("...

View answer
Handling errors with minimal repetition when using Find function
4 votes

If you absolutely need to use your Find approach, or if you'd just like to see a centralized error handler, as per you question, then you can use a variable to store the custom error message, and then ...

View answer
Script for removing empty rows in a sheet
4 votes

If sorting the data doesn't help move the blanks to the end of the data range, and deleting the rows is a better solution, given your data, then you can try this approach. If you know a column will ...

View answer
Create a folder and perform a SaveAs
Accepted answer
4 votes

You haven't mentioned exactly what triggers this code, but it might run on any open workbook, even if that workbook is confidential to the active user. You might want to consider checking if the ...

View answer
Creating ADODB Parameters on the fly
3 votes

Waking this one up... ExecuteNonQuery Return value never assigned ExecuteNonQuery never has its return value assigned. Return value type You have an opportunity here to return a richer value than ...

View answer
Renaming form controls and underlying code
3 votes

Referring to the current project I know you probably meant for this code to be generic so that it works across any project, but it would seem that the code is supposed to sit inside your addin. ...

View answer
Excel display driver class for Tetris
3 votes

Have you considered using Conditional Formats as your rendering cathode ray? I've just thrown this together, so it's by no means complete or optimized, but by assigning sprite arrays of values to ...

View answer
Parsing excel cells containing line feed characters
3 votes

ColumnNumberToLetter What happens if the user selects an entire row? Your function will currently return a row number. In cases where an entire row is selected, you can't even work around it by using ...

View answer
VBA function that returns color RBG code of a referenced cell
Accepted answer
3 votes

I don't think you need to activate the cell, or use the selection, just inspect the range directly. I've also made the function volatile so it will update when you calculate. But, for Code Review ...

View answer
Handling errors with minimal repetition when using Find function
3 votes

Find is a very slow method for doing what you need. You will achieve much greater performance, and avoid the need for complex error handling, by reading the search range into an array, and looping ...

View answer
Deleting rows from a spreadsheet where Column C is blank
3 votes

This code defines a few constants, and uses a With block, but otherwise achieves the result in a single line. The code would be easier to read and maintain if I broke the line down into 2-3 lines, ...

View answer
A reusable ProgressIndicator
3 votes

Create an IProgress interface, and have your class implement the interface. Then the interface can be reimplemented by Access forms that do the same thing, or by classes that encapsulate the ...

View answer
Hex encoding and decoding of ASCII strings in VBA
2 votes

Hex to String - Preserving the leading 0 Hmm, I think I can use a bit trick to make the Hex conversion more efficient. Right$("00" & Hex$(61), 2) becomes Right$(Hex$(256 Or char),2) Options ...

View answer
Check existence of item in collection
2 votes

IMO, you're better off with a function that either returns a boolean that confirms the sheet is present (before you try to do something with it), or returns a reference to the sheet or otherwise ...

View answer
A function that implements a query
2 votes

As rbp says, while the shared name between function name and query name might be intuitive in your mind, and might work under DAO/Access, it might not be intuitive to users that inherit your code, and ...

View answer
Clearing duplicates from an Excel 2007 sheet
2 votes

Using Replace will convert non-blank, zero-length-string cells to truly blank cells. With ActiveSheet.Range("A1").UsedArea 'First convert zero-length cells to a unique value .Replace("", "...

View answer