In Excel it is much faster to write large sets of data to a spreadsheet all at once instead of using a loop, by copying an array variable to a range of cells directly. This can speed up an Excel macro significantly, especially in large complex workbooks.
In large Excel workbooks when you are executing VB macros that change cells in sheets, you can speed up the process significantly by turning workbook calculations off at the begining of your subroutine, and then back on again at the end. Here are some subs that can do this. Simply call the “Calc_Off” sub at the beginning of your code, and the “Calc_On” sub at end end:
Sub Calc_Off() Application.Calculation = xlCalculationManual End Sub Sub Calc_On() Application.Calculation = xlCalculationAutomatic End Sub If you prefer, you can manually turn automatic calculations on and off from Excel's user interface. From the File menu > Options > Formulas > (Automatic or Manual calculation options).
I’ve tested these in Excel 2010 in sheets with over 16,000 columns and the functions work even for large numbers of columns.
Function ColumnLetterToNumber(ColumnLetters As String) As Long
ColumnLetterToNumber = Range(ColumnLetters & "1").Column
End Function
Function ColumnNumberToLetters(ColumnNumber As Integer) As String
Dim strLetters As String
strLetters = Cells(1, ColumnNumber).Address(1, 0)
ColumnNumberToLetters = Left(strLetters, InStr(1, strLetters, "$") - 1)
End Function
These functions are not necessary usually, because almost all things in Excel VBA can be done using ranges specified as numbers, for example,
Sheets("Sheet1").Cells(2,3).Value = "Hello"
Where 2 is the row number, and 3 is the column number, where column 1 corresponds to column A.
The trick is to replace all of your “Form Controls” with “ActiveX Controls”.
It’s not usually recommended to use global variables because they make it more difficult for debugging or reading other people’s code. However, they are handy sometimes when you’re in a rush to get your code done.
At the top of a Module for example, simply type something like these below. I’ve defined three Pipe Specification variables: two arrays of unknown length and one variable:
Public PipeSpecColNums() As Integer Public PipeSpecNames() As String Public LastPipeSpecName As String
You can update the size of the array on the fly in sub routines, like this:
Redim Preserve PipeSpecNames(20) 'Now 21 array entries (0-20),
'and preserves previous items stored.
Redim PipeSpecNames(20) 'changes the size of the array without preserving.
Batch rename files in a directory you specify in Windows, for free!
Excavations of the bodies have been going on for many years, you can find out more from the Easter Island Statue Project. It’s generally accepted that the statues were made sometime between 1250 and 1500 AD. There is controversy surrounding why the bodies are buried. Was it time and erosion, or were they buried on purpose? Aliens? The soil surrounding the bodies for so long has preserved interesting carvings (petroglyphs, or rock markings)..
Hot water heaters such as the Kenmore Power Miser 9, come with a plastic drain valve that is most likely going to start leaking soon. Mine started leaking at about the 7 year mark, through the plug valve washer, but recently the valve sprung a second leak and was leaking right at the threads so it had to be removed. It is extremely difficult to remove these little bastards without breaking the plastic flats. If you break the thing off at the threads you may need to resort to special tools for removing broken bolts, or even perhaps painstakingly using a hacksaw blade to slowly split the threaded end so you can pry it out. Most likely though you have started to break this on the plastic flats with an adjustable wrench like I did. Anyway, there is an easy solution, don’t break the valve off completely yet with anger! The trick to remove this bastard is to first remove the white/grey knob assembly (by twisting the white part) and valve plug washer… once that is removed, you can then find around your house a metal rod that you can insert into the open hole where the knob/plug assembly used to be… and now with your adjustable wrench and your rod together in each hand, it should turn! You should be able to remove the valve! Even if you cracked the plastic flats slightly trying to remove the valve with your adjustable wrench, this will likely still work like it did for me, I illustrate below…. once it’s off I highly recommend replacing the valve with a standard 3/4″ NPT brass nipple, full-port ball valve, and garden hose adapter as shown in the last image below. These water heaters all use standard 3/4″ NPT fittings which are readily available in your local hardware store, likely even cheaper than the replacement value of the plastic valve from Kenmore, and the brass fittings are a million times better quality and will last much longer. Even if they cost more I still think it’s completely worth the upgrade. In fact, if you buy a new water heater in the future, look for the stupid plastic drain valve and immediately replace it with brass fittings. It will make life easier years down the road. They drain faster too.
Here is a quick summary of what will be covered in this article, useful for people that own their own small business but do not want to buy specialized financial software: downloading CSV files of your transactions for bank accounts and credit cards, and how to batch concatenate (join) multiples of them together all at once, how to remove duplicate lines, and finally how using a spreadsheet you can quickly use keywords of your own to sort all of your expenses into categories like meals, entertainment, hotel and travel expenses. This is not only useful for small businesses but good if you like to keep track of your personal expenses as well.
Packing too much when traveling is bad, it can limit your options for what you are able to do on a trip because of limited luggage space on a bike. I’ve compiled some packing lists below for the light traveler.
Also check out my blog post on how to ship your motorcycle overseas. If you’re interested in such a thing.
This isn’t necessarily a very difficult problem to fix, but I thought it was a bit confusing with these old MOEN kitchen faucets. This particular faucet is about 22 years old. It has a U-shaped black plastic thing underneath, which at first I thought was missing some screws and thought the missing screws were the reason the thing was loose. However, that is not the problem at all.
In 2008 I wanted to personalize my Ducati’s dry clutch cover. After looking in the stores I was really disappointed with how expensive the off-the-shelf open clutch covers were, they go for over $200 typically. So I thought to myself, why not just buy a damaged or scratched up clutch cover off of eBay for next to nothing, and build my own??
This isn’t really that difficult of a problem but I thought it was pretty slick and easy so I’m posting this up. My requirement was that the pots and pans rod must be up and out of sight as shown in the images above, and that the S-hooks must be twisted 90 degrees so that the pans face sideways, and therefore save space.
I’m tailoring these instructions for people importing motorcycles, but the same steps will almost all apply to importing any other vehicle so if you’re importing a car feel free to read on. The potential for saving money is good, particularly when the Canadian dollar is strong. As an Alberta resident, I have imported a new 2007 Ducati 1098 in 2007 from Oregon into Alberta via an Alberta border crossing, and imported a used 2008 Honda CBR1000RR in spring of 2011 from Washington into Alberta through a BC border crossing. I touch on some of the implications of being a resident of different provinces – you may be charged US sales tax if you are a BC resident for example (unless you buy from a sales-tax free state), or if your province has HST or PST you will have to pay those when crossing which is higher than just GST if you are an Alberta resident. On each occasion $3000 was saved after all was said and done. For a little effort and about 2 days of traveling and having a bit of an adventure, I think it is worth it. Even if I wasn’t buying a motorcycle, the trip itself would have been worth it. I love road trips! You can either fly down to the USA to get the bike and ride it back using temp registration that the dealership can help you obtain, or you can drive down there in a truck or with a trailer to bring the bike back. Personally I attached a trailer hitch to my Acura Integra (~$200), and borrowed a very light weight motorcycle trailer from a friend. Read on for all the steps required to import.
Many people with the Samsung i9100 Galaxy S II