Tag Archives: Twitter

Tweet Cruncher

I have a Social Media project on the go (waves at https://twitter.com/librarytrustees) that is going to involve tweeting sections of existing documents.

I really hate counting characters. So I decided it was time to make myself a tool for the job.

Original Document and Result after Tweet Cruncher is run on the selected text
Original Document and Result after Tweet Cruncher is run on the selected text

You can see above what I have; the selected area of the original document is highlighted in varying colours, corresponding to the resulting text broken up into tweets. Additionally, I have inserted my chosen hashtag and a count of the sequence of tweets.

Dialog box for Tweet Cruncher
Tweet Cruncher Dialog

The length of the tweets and the Hashtag are entered in a dialog box when the Tweet Cruncher runs. This information is saved with the document, for consistency with subsequent tweets. The Tweets are not exactly the tweet length; I’ve added a bit of code to “round off” each tweet to whole words. The hashtag and sequence count are additional to the length.

And realistically, there will still be editing for content and meaning. Nevertheless, this tool should save me a ton of counting and get the project going faster.

Sub BreakIntoTweets()
Dim IntSelection As Integer
Dim IntPostNumb As Integer
Dim IntPostCount As Integer
Dim IntCharCount As Integer
Dim IntTweetLength As Integer
Dim rngSelectedRange As Word.Range
Dim strPostText As String
Dim intColourPick As Integer
Dim docNewDoc As Word.Document
Dim docWorkingDoc As Word.Document
Dim strPropertyName As String
Dim strHashTag As String
Dim blnWord As Boolean
Dim intActualLength As Integer

Dim arrColourOptions As Variant
arrColourOptions = Array(wdBrightGreen, wdPink, wdTurquoise, wdYellow)
   
Set docWorkingDoc = ActiveDocument
strPropertyName = "HashTag"
strHashTag = frmStartCrunchingTweets.txtHashTag
docWorkingDoc.CustomDocumentProperties(strPropertyName) = strHashTag
IntTweetLength = frmStartCrunchingTweets.txtTweetLength
Set rngSelectedRange = Selection.Range
MsgBox rngSelectedRange.Characters.Count & " characters are selected. Including Paragraph Marks"
IntSelection = rngSelectedRange.Characters.Count
IntPostNumb = IntSelection / IntTweetLength
MsgBox IntPostNumb

rngSelectedRange.Characters(1).Select
IntCharCount = 1
Documents.Add DocumentType:=wdNewBlankDocument
Set docNewDoc = ActiveDocument
docWorkingDoc.Activate
For IntPostCount = 1 To IntPostNumb
    Selection.MoveRight unit:=wdCharacter, Count:=IntTweetLength - 1, Extend:=wdExtend
    If (Right(Selection.Text, 1) <> " ") Then blnWord = True ' extend to word
    If (Right(Selection.Text, 1) <> ".") Then blnWord = True
    If (Right(Selection.Text, 1) <> "?") Then blnWord = True
    If (Right(Selection.Text, 1) <> vbCr) Then blnWord = True
    If (Right(Selection.Text, 1) <> "!") Then blnWord = True
    If blnWord = True Then
        Selection.MoveRight unit:=wdWord, Count:=1, Extend:=wdExtend
        intActualLength = Selection.Characters.Count
    Else
        intActualLength = IntTweetLength
    End If
    blnWord = False
    strPostText = Selection.Text & frmStartCrunchingTweets.txtHashTag & " " & IntPostCount & "/" & IntPostNumb
    'get rid of any hard returns
    strPostText = Replace(strPostText, vbCr, " ")
    docNewDoc.Activate
    Selection.TypeText (strPostText) & vbCr
    docWorkingDoc.Activate
    intColourPick = IntPostCount - (4 * Int(IntPostCount \ 4)) 'note this is why no base 1 option for array here, also \ means different than / (truncation function)
    Selection.Range.HighlightColorIndex = arrColourOptions(intColourPick)
    IntCharCount = IntCharCount + intActualLength
      On Error Resume Next
    rngSelectedRange.Characters(IntCharCount).Select '(errors on final character of selection)
Next IntPostCount

End Sub

Social Media Spreadsheet – Conditional Formatting

I’m a big fan of Excel’s conditional formatting feature. I use it a lot in my spreadsheets to check on the quality of data, find errors and many other tasks. Here is the first of a couple of examples of how I’m using conditional formatting in my social media spreadsheet.

Just a bit of background on the spreadsheet. I use this spreadsheet to compose Facebook Posts and Tweets for the Redcliff Library. I also use it to schedule when the posts/tweets will be published. This allows me to sit down and plan a coherent sequence of posts/tweets.

I often take the Facebook posts and cut them down to shorter lengths and reuse them on Twitter. Twitter has a character limit of 140 characters. However, I don’t want to use all 140 characters if I can avoid it. Its’ generally recognized that the ideal tweet length is around 120 characters. This length allows others to retweet and add hashtags without having to edit the tweet.

So I have created 4 conditional formatting rules to help me meet this length limit.

  1. The background of the cell turns bright red [STOP] if the tweet is over 140 characters.
  2.  The background of the cell turns dull red if the tweet is over 135 characters.
  3. The background of the cell turns bright orange [WARNING] if the tweet is over 125 characters.
  4. The background of the cell turns dull orange if the tweet is over 120 characters.

Why four rules? I could use 2 warnings only; at 120 and 140 characters respectively. In fact, that is where I started. But, writing tweets can be a tricky thing and I found I needed a little wiggle room to help me when I compose. The other thing to keep in mind is that the conditional format isn’t applied until I finish editing the cell (by pressing the Enter key or the checkmark). It is possible to have an interactive format applied using VBA, but those functions are memory intensive and slow down the whole spreadsheet. Since my writing process seems to involve a lot of pauses to think, stopping to  apply the conditional format isn’t really a big problem for me.

So what does it look like in action?

Conditional Formatting Results
Conditional Formatting Results

As a result, I can quickly identify which tweets need to be edited. Here are the 4 rules as displayed in the Conditional Formatting Dialog box.Conditional Formatting Dialog

These are formula based conditional formats.

closer look at formula
The formula the conditional format is based on.

 

 

A conditional formatting formula must return a value of TRUE in order to fire. The following formula uses the AND, SEARCH and LEN functions

=AND(((SEARCH(“TW”,B1))>0),LEN(H1)>140)

If you were reading this formula in something like english it would read: “If the letters TW appear in column B AND the length of text in this cell is more that 140 characters the result equals TRUE”.

Why am I testing for the presence of TW in the subject column? Remember I said that I had both Facebook and Twitter posts in the same spreadsheet. I don’t want the conditional formatting to flag Facebook posts, which by their nature are longer.

Pro Tip:

When you are writing a formula for conditional formatting, do it in a cell in the spreadsheet first. The dialog for conditional formatting is really cramped and you don’t get any help features. After you are sure the formula works, you can then copy/paste it into the dialog using the Ctrl + V keyboard shortcut. Also, because I planned on apply this conditional format to the entire Description column ($H:$H). I had cell H1 selected when I built the conditional formula. That way the formula will adjust relatively to the entire column. Using absolute and relative references properly is another tricky part of building conditional formatting formulas.

applying the coloured background fill
applying the coloured background fill

Once I have my formula built. I can click the format button and select the background colour fill.

 

 

When I’ve built my first format successfully  I can then use it for the basis of the subsequent formulas. Just changing the length of the text in the cell.

  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>140)
  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>135)
  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>125)
  • =AND(((SEARCH(“TW”,B1))>0),LEN(H1)>120)
The order of the rules and the stop if true flags must be set.
The order of the rules and the stop if true flags must be set.

To make this really successful, the rules need to be placed in the proper order, with the Stop If True flags turned on. Now excel will check to see if the text exceeds 140 characters first, then 135, then 125 and finally 120.  The Stop if True flag doesn’t need to be set on the final rule, because no other rules follow it.

Conditional formats can take time to build, but are extremely useful in many ways.

To find another example of using conditional formatting with a social media spreadsheet check out this post.

What I learned on my summer vacation

5 Things I Learned On My Summer Vacation

I volunteer as a Board Member for the Redcliff Public Library.  We are a small library in a small town, but we’ve agreed that one of the important things that we need to do is to promote our library  on Social Media. Of course, there is no extra money to hire additional staff to perform this responsibility. And so, one of the tasks I’ve taken on, is handling the library’s social media campaigns. It has been a good experience, partnering with our Library Manager to promote our library through various social media channels*.

This summer I worked on 3 different campaigns promoting the library. While I’m rediscovering the wheel here; I think the wheel is the same shape regardless of the size of your audience/campaign. So, here are 5 things I’ve learned about using social media effectively.

#1 Organization is Key

If you’re not prepared to be organized about your social media you might as well not start any kind of social media campaign. The problem – many people (including myself at the start of this project) think of social media in terms of hanging out on Facebook and Twitter and playing on Pinterest. They don’t think of it as real work. If you’re going to do it right – it is real work and you need to get organized to do it. After running three social media campaigns for our library, I’m building a workflow and the tools to support that workflow. I also think about the workload differently now. Various campaigns may represent peak effort, but in order to keep the connection with our audience alive (see #3), we need to plan to stay active all the time.

#2 Know Your Audience

It seems so simple, of course you’ve got to know your audience. But really, who are you talking to? To get the best results for your effort on social media, be prepared to re-evaluate who your audience is. For example; initially on Twitter our follows were of other libraries and and book oriented accounts. But the longer this social media project goes on, the more our definition of our audience and our purpose for talking to them evolves. Currently, we see all social media channels as methods for connecting with our patrons. And by demonstrating our strong connections with our patrons, we are also using social media to influence our funding agencies.

#3 Silence is Golden – Except When it Isn’t

Don’t wait for your announcement/programs/promotion before you start to speak to your audience. The social media space is a busy place and people aren’t going to hang around watching to see if you’re about to say something.

Instead

#4 Give First

Bring value to the conversation with information that is useful and/or interesting to your conversation partners. This is good manners and good conversation in real life, and it works online too. Good value can be kitten pictures or local information. Targeting your audience will help you decide what information serves them best. I  won’t say the worst thing you can do is post random stuff, but random does make you appear less focused. It makes it harder for your desired audience to understand what you can do for them.  Take advantage of the analytics provided by the various social media channels. What posts are liked and shared? What tweets are viewed most often?

#5 Make Connections

Make connections with complementary organizations. Linking to partners also helps with the ever present problem (demand) for more content. It wasn’t until I started including information from the Town of Redcliff’s Programs and Events calendar; that I felt like our information stream started to achieve a natural flow. That is; we’re publishing lots of useful posts/tweets, without always harping on our particular program reminders and solicitations. Instead, “our stuff” is placed in an attractive context of useful information.  Connections have helped with content and those connections extend our reach by sharing and reposting  our content.

Have I figured everything out yet? Of course not, social media is a work in progress and so is how we use it.


Footnotes

* What do I mean by Social Media Channels. For now, for us, it is Facebook, Twitter and Pinterest.
†By workflow, I mean creating a series of steps that will take an idea and create a series of posts, tweets etc to promote it. Very little of this is automated. My goal is to create a workflow that any volunteer could follow.
My tools include at this point: an Excel spreadsheet, an IFTTT account, Google Calendar, and a bit of Hootsuite. This article from Hootsuite started me thinking about how I wanted to design my own spreadsheet.