Twixcel = Power of Twitter * Power of Excel

Have a Good Last Tweet to get more Followers

If someone follows me and there last tweet is

Buy xxxxxx at http://bit.ly/h2hks

I may think it’s spam so I won’t follow.

But if last tweet is something useful, funny I may follow that user:


It's not fat it's stored energy! #quote


I’m more likely to follow the user who tweeted the second tweet.

So before you follow people make sure your last tweet attracts people instead of having them report you as a spammer.

Keep it Moving

Today I had a problem due to the Application.DisplayAlerts = True setting.

I was running code that set this to true and the Excel-VBA code stopped running. This will occur when Saving a workbook, deleting a sheet or any other place where Excel wants your confirmation.

Most of the time this setting should be true. But if you’re automating a process it can be bad.

The Twixcel software needs to keep moving. So there are a number of things we’ve done to avoid the code from stopping.

1. Don’t use MsgBox
2. Don’t use DisplayAlerts = True
3. Handle all errors without pausing the program

Replacing the Msgbox

To replace the Msgbox code we’ve used a Form and a routine called PopUp. This routine displays the form and waits for a specified amount of time. It then unloads the form.

Getting Suspended on Twitter

I hate that. Especially when there’s so much real spam on twitter. 

Many of my accounts were suspended on Twitter.  It’s sad for me because of the effort I’ve put in to developing the software and then getting suspended.   Matter of fact, I’ve been trying to develop tools to reduce spam and I get suspended. Here are 2 videos of what I was working on http://www.youtube.com/watch?v=wOAMdeI6r9I and http://www.youtube.com/watch?v=iZ0p5JZr_Yo

Twitter doesn’t say why they suspended an account and they have the worst website to contest it.  You start on the help page http://help.twitter.com/portal and then eventually you get to http://help.twitter.com/forums/26257/entries/15790 which is their page to contest the suspension. When you try to contest it you get back to the original page.  What could be more frustrating than that? 

So I send an email to the Suspended@Twitter.com and they did get back to me with an automated reply.

There are 4 possible reasons why my accounts where suspended

1. I accidentally followed users which had only 50 followers
2. I increased the number of users I unfollowed from 62% to 70%
3. I followed same user from multiple accounts accidently
4. I was tweeting the time with some of the accounts and twitter didn’t like it even though I’ve gotten a lot of positive feedback from users

For some reason my main account wasn’t suspended. I’m not sure why.

In any case I need to decide whether to spend time on twitter as all the work I do can go down the drain in minute from whatever policies they choose to enforce.  It’s sad to me that they can’t deal with real spam and wind up suspending my accounts.

Here is the line of code that I believe lead to the suspensions:

If friendFollowerRatio > 0.9 And friendFollowerRatio < 1.096 Or followers < 2000 Then

The followers < 2000 is what did me in.  I was testing code to get targeted followers for a keyword of Tony Robbins and I figured I’d try to follow anyone who mentioned Tony in a tweet.  I then forgot to remove tis line of code and all of my accounts started to follow users with < 2000 followers and no balance in the f/f ratio. 

What probably happened next is that users either blocked my account or reported the accounts to the @Spam user.  It turns out that I was trying to build up a follower list of Tony Robbins for a friend. I guess the saying “No good deed goes unpunished” is true.

On the down side I lost 190,000 followers, but on the up side I learned a valuable lesson.

“Don’t base a business on a company that doesn’t have a clue what it is doing” 

I spent too much time trying to automate twitter when in the end the automation is somewhat useless.  I have many other ideas and software products that I can try to market. 

Also on the positive side, I was spending way too much time automating twitter.  There are many other things to do to enjoy life.  I think I’ll go automate Facebook.

Don’t Hardcode Cell Columns in Excel-VBA

I know I shouldn’t but sometimes I get lazy and write code like:

Cells(i, 7) = 27

It’s a bad habit. I run into more bugs from hardcoding cell columns than probably any other source in Excel VBA.

One thing with the Twitter API is that the xml you get can change based on the users you’re retrieving data for. So if you hardcode columns, it can work a lot of the time, then all of a sudden it stops working.

If you control where the columns go you can use range names to define a column. If cell A1 has the range name “CustomerName”, you could reference the cells in a loop by the following:

for i = 1 to lastRow
    cells(i, range("CustomerName").Column) = GetData()
Next

But in the case with Twitter you don’t know where columns may be. I use a routine called “FindDataCol” passing it the string to find and the row to search on and it returns the column or 0 if not found. So the code looks something like:

status_col = FindDataCol("/status",3)
for i = 1 to lastRow
cells(i, status_col ) = GetData()
Next

Excel Memory Leak

One of the may things you may have to deal with when writing Excel VBA code are memory leaks.

I guess if you fix them you’re a Microsoft Excel plumber. 

While running  Twixcel I noticed that the spreadsheet was using 1.6 Gigabytes of memory. Wow, that’s a lot.

I ran taskmgr (you can go to “Start button” select “Run” and type in “taskmgr” or use Ctrl+Alt+Delete) and saw this memory usage. I shut down the spreadsheet and the memory went back down to 150 Mb.

These memory leaks can be difficult to find but the key to finding them is get more information. obviously the first step is to search Google or your favorite search engine for a solution.

My first instinct is that I’m using Static variables in a few places and this may be causing my problem.

The other idea I have is to measure the memory used coming in to a suspect routine and then leaving the routine and log it to a sequential file.

A third thought is that I’m creating lots of sheets in Twixcel. I’ve found that clearing the cells on a sheet is slower than just recreating the sheet. But this may be causing this side effect.

One other possibility is that I have an object that’s not getting set to Nothing.  

           Set objSomeObject = Nothing

Though, Excel is supposed to take care of this for you.

So my steps are:

1. Look for solution on google
2. Test deleting and recreating sheets
3. Add code to monitor the memory
4. Test static variables
5. Look for places where I’m not clearing objects

One thing I’ve learned is that if you can recreate a problem you can solve it. It just takes time and effort. 

I’ll keep you updated on my progress.

I started to watch the code in action with the task manager window on top.  I noticed that a megabyte of memory going away each time a sheet was created. So I paused the Excel VBA code (Ctrl+Break) and modified my CreateSheet function.  If the sheet exists I’m doing the following:

Cells.Clear
Cells.Clear
ActiveSheet.Range("A1").Select


The two clears are needed for the case where there are filters on the sheet. The first Cells.Clear will clear the filter but not the sheet. The second one clears the sheet.

After watching the memory and the change, it’s still increasing.

The Buttons Don’t Work

Today, I check on Twixcel and find that the buttons on the status form don’t work. Matter of fact, the form doesn’t seem to recognize anything I do to it.

This is just an example of the types of things you will run into when you automate Excel using VBA. This happens especially when you have large complex spreadsheets that push Excel to its limit.

The most likely cause is that Microsoft developers never wrote complex macros to test the VBA software as their resource goes into writing the software.

The most important thing you can do is look for work-arounds and see if others on the internet have had the same problem.

Twitter is Over Capacity Bug

There appears to be a bug in the Twitter API that if you pass a double quote or % when doing a status request Twitter returns an Over Capacity error. Very strange.

http://twitter.com/statuses/update.xml?status=Sales+growth+can+be+increased+if+you+cut+your+product+line+by+50%+-+Doug+Hall+#quote

and you get the Overcapacity message

16 Ways to Get More Twitter Followers # 4

Unfollow Users Who don’t Follow You.

The 4th method to get more followers is to unfollow users who have stopped following you or don’t follow you back. This rule is once again due to the following to follower ratio (f/f).

Suppose you are following 11,000 people and 10,000 follow you back. This puts you at the 110% following to follower (f/f) ratio. Twitter may limit your ability to follow more people for 24 hours. Now what if 1,000 people unfollow you. Your ratio is 11,000 / 9,000 which is 122%. You’ll have to wait until you get 1,000 new followers before you can follow a new person. The best strategy is always unfollow users who’ve stopped following you. There is another class of users which are users that you’ve followed but didn’t follow you back.

You have to be more careful with this bunch as twitter can consider this spam and suspend your account according to their documentation. They are very vague as to what is offensive but they seem to not like you doing it too quickly. I think they also don’t want you to make it look like you’re a great tweeter by having many more followers than you follow.

Dropping these followers will bring you f/f ratio into a better balance and you’ll be able to follow more users.

Then there are the famous people you follow who won’t follow you back. Twixcel supports a function to not unfollow them if you add their screen name to a list.

See the entire list at 16 Ways to Get more Twitter Followers

Check out Twixcel software that supports these functions and many others.

Follow me on twitter @alecberg