Excel VBA Macros: Borders and Formatting

Borders seem very complicated when you record a macro for borders, do any kind of text of text formatting, etc., and look at it in Visual Basic. There are a few ways to simplify this.

Border Formatting Example

You record a macro, make a selection, and set borders. You immediately get something like this:

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    ...

Need a TL;DR? Essentially it is

1. Removing any diagonal borders through the cell, and
2. replacing each border with a medium size black line.

The details are:

• .Borders(Direction) indicate which border you want changed.
  • xlDiagonalDown affects the line from the top-left to bottom-right of each cell selected.
  • xlDiagonalUp affects the line from the bottom-left to top-right of each cell selected.
  • xlEdgeLeft, xlEdgeRight, xlEdgeUp, and xlEdgeDown affects the left, right, top, and bottom borders of your selection. That means it treats your selection as a box, and the sides are what the code affects, see the above image for details.
  • xlInsideVertical and xlInsideHorizontal affects all the other vertical and horizontal lines (respectively) in your selection that the above didn't cover.
  • If you don't specify a direction, then all
• The parameters you can set for borders are:
  • .LineStyle controls the line style. See the top for details.
  • .Weight controls how thick the line will be.
  • .Color controls the color of the line. You can use RGB format.
  • .TintAndShade controls tint and shade, but they can be ignored if you're using RGB values.

You don't have to stick to just the current selection, it can be modified so that you can change the borders for any cell without having to select it (and there are some good uses for this), so you could substitute Selection with stuff like:

    With Worksheets("Sheet1").Cells(6, 1)              ' This way you can use variables when accessing. 
       .Borders(xlEdgeBottom).LineStyle = xlContinous
    End With

- OR -

    With [D2].Borders(xlEdgeTop)
       .LineStyle = xlContinous
       .Color = RGB (255,0,0)
    End With

Text Formatting

The same can be done with Selection.Font (the parameters above work on this as well). I am listing only important values.

• .Color controls the color of the test (use RGB)
• .PatternColor controls the color of the background (use RGB)
• .HorizontalAlignment controls text horizontal alignment (equal to xlLeft, xlRight, or xlCenter)
• .HorizontalAlignment controls text horizontal alignment (equal to xlTop, xlCenter, or xlBottom)
• .WrapText = (true to wrap)
• .ShrinkToFit = (true to apply)
• .Orientation = (what angle to rotate it by)
• .AddIndent = (true to indent)
• .IndentLevel to determine how far to indent (whole numbers only)
• .MergeCells to merge or remove the merge on the affected cell(s). To not do anything with the cells structure, don't include it.
• .Name = (font name, enclosed in quotes)
• .Size = (font size)
• .Strikethrough = (True to make it strikethrough)
• .Superscript = (True to make it superscript)
• .Subscript = (True to make it subscript)
• .OutlineFont = (True to put an outline on text, but it apparently does nothing)
• .Shadow = (True to put a shadow on text, but it apparently does nothing)

The UOP Banquet Sounds of Service

Each item, save music instruments, are raffled off.

This banquet - not like any other. Unlike last year, it has more interclub students than new members (at least 2:1). It occurs in Grace Covell, but in a different room. (At first sight - it was a dark room, yule log, was it really a nice party going on? Nope, it was actually in another room.)

Good to see there was another historian - it seems like they must have dSLRs, but I was dissuaded in the past to carry one due to its large weight - a hybrid like the Sony DSC RX-100 would suit my needs perfectly - provided I can afford it, of course.

Events:

• Two icebreakers (or minigames) - Song Poncho (exactly the same as the event last year as a family competition, including the word) and Ipod Wars (guess the song)
• Song played associated with each new member (that was their favorite song) - note it was planned (but wasn't done) last year's banquet.
• Chicken Platters - despite the wording, they're exactly the same as every other chicken dish in past UOP Banquets.
• The raffles - the violins were just for show, but tickets were buyable for everything else at a quarter per ticket.
• The piano was played... by a Sac Student.

Note: I will be unable to make it to the next day's event (Santa's Gift of Service), due to lack of transportation options* and as such the related blog post will be unavailable.

A Clockwork Board

Beetle runs like Clockwork here.

It's like a remade version of Major Mouthful. Only the curves are slightly shorter, and you can't use the same technique for double gapping like in the previous island. With gapple sauce, however, you can get more per double gap. And score much higher than you usually would (My PB here is nearing 15 million.)

Ginger Up is now available, where the only thing it does is... well, prevent the hot frog meter from going down, but that's about it. (Note: Just because the hot frog meter doesn't go down doesn't mean the speed of your balls won't either.)

Strategy: A medium sized gap near the end of the curve (or two, if you're fast) should do the trick. Do double tapping after that point, for up to 250k per double gap. Make sure that the curve is two layers deep before gapping, or your gap shot bonus will decrease significantly. Fruit shouldn't be a main goal here - there's at least two spots where you have to go through two layers of balls to get to it.

Recommended Powers: x3, Time, Warp Ball, Gapple Sauce, Spirit Beetle. You may sub Warp Ball for Wild Shot if it works for you.

Black Friday Shopping - including Costco

When it comes to hard drives, Costco apparently sells out 30 minutes before the store officially opened.

Black Friday sales start today. No lines like last year at least in Stockton (I heard about the 'no camping' rule until 10pm), but to compensate stores opened really early. By that, meaning some stores started their sales as early at 8PM - in which waiting lines formed inside the store to get that 100 dollar TV (see note at bottom). Black Friday sales just went from starting overnight... to last night.

I've also gotten Diablo 3 in an attempt to cover the costs. (Note: it's tough but possible to sell your in-game items for real cash) I'll have to try that during Winter Break though. Just a a few weeks away...

TV Recommendations: I typically use Vizio and Sony - more expensive, but also better in picture quality. You might find extremely cheap HDTVs during the sale, but they lack slightly in a few areas.

Additional Hard Drives: Costco had them at 3TB for $100, selling out very quickly due to 'overwhelming demand'. They actually opened before the advertised 9am date - and others got to them first. It's good to see that space isn't an issue anymore (note) - with this many drives the only major issue would be running out of drive letters to mount a drive at. I don't need all the space - redundancy is more of my interest. Placing them in a RAID 1 config would be a much better use for them.

Thanksgiving Board

Not called the 'Stump' board.

The Thanksgiving Board takes a new twist - you've got one curve that around the outside (with a difficult gap possible at the end). On the other one it wraps around itself once making it a double gap. The question of 'how' to reach 10M can leave many people stumped.

There's three additional food items available, but you don't really need to make use of any of them. And there's the new message center, but the daily gifts are typically the least bought items.

Strategy: You need to setup two small gaps near the end of the board. Wait for the balls to come up on the first curve and double tap for max points. It is recommended you get Gapple Sauce (if you don't have any, refill using the Gapper's Delight bundle.)

Recommended Powers: x3, Time, Warp Ball, Gapple Sauce, Spirit Beetle.

The Last Hostess Package

No Twinkies here - guess they sold out quickly.

News spread fast that Hostess was shutting down and won't be selling any more of their goods on the 16th. It wasn't too important for me... until KY mentioned she never had a Twinkie before, and never will, so I put a slight priority to secure as many Hostess foods as possible, and if I found one, to send it over to her... somehow. (I did have a Twinkie once).

I finally made it over to a store that might be selling it on the 20th, four days later. They were right. Shelves of their products were emptied quickly.

I was unable to find Twinkies (the labels suggested they didn't have them in the first place) but I did find a few doughnuts. I considered it close enough, bought the last package, and took an image of the last Hostess product I could ever buy (and eat).

In the Past: There was a Hostess store years back that I visited once, before it was torn down to make way for a new shopping center in the late 2000s.

Davis Turkey Trot: Road Running Through the Rain

Circling round Rainbow City Park (5K path)

Months ago, after volunteering for a 5K race in Elk Grove, I thought - why can't I be one of the runners? It would be much cooler.

However recently I couldn't find any 5K that Capital Division was actively volunteering in. (Note I started practicing running the 5K in July, and all 5Ks they were doing was before this.). I did some research and noticed that Davis CKI was doing this as volunteer work last year (and also considered an interclub), so I chose the Turkey Trot as a run.

As it's an hour away, the question is why? Well, what wouldn't be a better way to impress friends by making record time? Walking up to the booth, signing in, and saying 'as a runner'. lol. The run was months away, so there was no indication that Davis was doing that this year... yet. (at least on their calendar)

October DCM indicated that their next DCM was on the 17th... which was the day of the event. Was there any other volunteer event going on that involved this event for those who weren't going to the DCM? Um... no.* That really cut off my main motivation for going to this race. Though there were other options, like 5K locally, I didn't sense the enjoyment of running back and forth along a downtown road, so I stuck to Davis for the run. And then a few day forecast predicted rain. I was advised to back down for an hours drive, but I didn't train 3 months just for nothing.

Results: 5K Cleared in 32:27.

Based on my performance over the past few weeks coming in at around 38-40 minutes in training runs, I realized that even friends might not be impressed if they saw me finish in that time frame (as I would fall less than the 80th percentile of runners in my age range). In reality, I managed to do better than expected, placing in the top 37% of runners, crossing the finish at 32 and a half minutes - it was also my personal best. I had improved a lot since I started my first (unmeasured) 5k at UOP back in April last year, where I finished in around the 48 minute mark.

Overall, I consider this a good practice run, and keep will on training until Capital Division actually happens to for another 5K, at a race where it really matters. I was not the only one in this race, as another member from Capital - Bobby Miyashiro - was also in this and finished it in 23, but whether I can beat that - only future runs will tell.

Playlist: My playlist was adapted to sunny conditions. But it's raining hard, so I can't just plug in that playlist containing Cotton Eye Joe by Rednex and some other songs. I had to change my playlist. So I set my iPod on Shuffle and the following songs came up, which seemed appropriate:

Eric Redd - Breathe (Albert Castillo Clean Radio Mix)
AN-2 - Road Through The Rain
Skrillex - Rock N' Roll (Will Take You to the Mountain)
Natasha Bedingfield - These Words (Ford Radio Mix)
The Chemical Brothers - Under the Influence
Rob Dougan - Clubbed To Death
The Prodigy - One Love (Edit)
Kesha - We R Who We R
Celldweller - The Last Firstborn