Browsed by
Category: Computing

list of extremely useful software

Fix for Smarterm macro error: “Error in line: Script is too large to be compiled”

Fix for Smarterm macro error: “Error in line: Script is too large to be compiled”

I had long been besieged by this error, and I know I can’t be the only one.  I could find nothing regarding this error in the manual, the help file, or even endless Googling.  Splitting your code out to multiple .stm macro files is the easy answer.  But that is problematic because passing variables to functions between macro files is iffy at best.  I don’t think it’s even possible without using precompiled files.

I finally stumbled across this forum post.
http://www.pstnet.com/forum/Topic1767-5-1.aspx
It was about some psychology software tool but apparently the same principle applies.
That led me to this: http://www.pstnet.com/support/kb.asp?TopicID=1300
Remove string literals.  That’s it.  You may not remember what a string literal is, in your subs or functions, if you put some text within quotes: sName = “Beverly” that is a string literal.  I found that I use these all over the place.  When you record a macro and then look at the code it uses string literals.  The code samples in the macro guide are littered with literals.  So I’d always assumed that this was the proper way to do things.
Anyway so what to do?  You can’t just remove these string literals they are essential.  One easy thing to do is replace anything you can with one of the built in constants.
So instead of using “” use ebNullString.

Sub Test1()
    If x = "" Then
         Exit Sub
    End If

    'Change it to this:   
    If x = ebNullString Then     
         Exit Sub
    End If
End Sub

I was able to do a search and replace and found that I’d used “” all over the place.

If you need even more space take it a step further, replace string literals with constants.

‘For those string literals you create, make a constant instead:

Sub ChooseLoop()
     If sName = "West" Then
        Call WestLoop()
     Else
        Call EastLoop()
     End If
End Sub

'Outside the sub declare a constant instead:   
Const WEST = "West"

Sub ChooseLoop()
     If sName = WEST Then
        Call WestLoop()
     Else
        Call EastLoop()
     End If
End Sub

By replacing just a couple of my commonly used string literals, I gained an extra 37 lines of compile space!

Excel Macro Tutorial 6 – Add macro buttons to your toolbar

Excel Macro Tutorial 6 – Add macro buttons to your toolbar

The finishing touch will be to put your macros into the toolbar, so they will be available to you every time you open up excel.
I have been working with Excel 2007 in these tutorials but up to this point everything I have shown you will work with 2003 and most other versions.  With 2007 Microsoft removed the ability to (without tricky tweaking) make a custom drop-down menu in Excel.  But they did make it easy to add a custom button to the quick access bar for a single macro.  For a great tutorial on adding custom menus to 2003 see this page: Assign a Macro to a Toolbar or Menu.  For 2007 see my tutorial below.

Adding a custom macro button to the Excel 2007 quick access toolbar:

The first thing to do is make sure you’ve been putting your macro code in the right place.  If you put the code in the workbook it will be available to anyone who opens that file.  But in this example we open and process a new workbook every day.  So we should put the code in our personal macro file.  This makes it available to you every time you open Excel.  Open the visual basic editor to look at your files:

Read More Read More

Excel Macro Tutorial 5 – Delete cancelled tests, flag credited tests

Excel Macro Tutorial 5 – Delete cancelled tests, flag credited tests

When a test is cancelled in the system it appears as a charge and then a credit with the same date and time.

The billing team does not want to see these, as they have to charge for a test and them immediately credit a test, and it’s a hassle.  So we clean them up before submitting.
Often a test gets ordered, cancelled, and then re-ordered, so it is important to make sure the code doesn’t delete the re-ordered test:

Read More Read More

Excel Macro Tutorial 4 – Delete non-chargeable tests and sort.

Excel Macro Tutorial 4 – Delete non-chargeable tests and sort.

The billing report has a bunch of tests with at cpt code of ‘0’ or a blank.  These are non-chargeable tests.  We used to have to delete them manually.
bill report 0cpt
For this we can loop through the rows and delete non-chargeables just like we did with the junk removal.  For efficiency we could just add this code to the junk-removal macro, but I have kept them separate here for the sake of simplicity. I called the macro ‘DeleteZero’ and copied and modified from the last macro:

Read More Read More

Excel Macro Tutorial 3 – Remove header rows and delete junk loop

Excel Macro Tutorial 3 – Remove header rows and delete junk loop

Ok now we’re getting into a bit more advanced material.  We’re going to have the macro do some real grunt-work for us.  Here’s what we want it to do:
1. Find the last row of the report.
2. Go through the entire report, row by row, and delete anything that we don’t want.

The report has a bunch of junk we need to delete:

Simple enough, the only problem is you can’t just record yourself going through the report deleting stuff and then go back and change the code. 

Read More Read More

Excel Macro Tutorial 1 – Open a text file

Excel Macro Tutorial 1 – Open a text file

In this 6-part series of tutorials, I show you how I was able to automate a process that took 5-10 minutes per report.  Now the process takes about 30 seconds.  The techniques and code examples from these tutorials can be adapted and used by anyone who has the misfortune of being delegated to do tedious gruntwork in Excel.  Many concepts like ‘how to record a macro in Excel’ are NOT covered in this tutorial.  There are plenty of tutorials on the web covering the basics of Excel macros.  Instead this tutorial fills in the gaps by explaining a real-world example from start to finish.

How to open a text file with an Excel Macro:

Read More Read More

List of extremely useful software

List of extremely useful software

First a few sites where you can search for shareware.  I like to read reviews about what I’m trying so I like the following shareware sites:

  • CNET or Downloads.com : Decent editorial reviews but I’ve seen some reviews that were entirely off the mark here.
  • Snapfiles : Lots of ads but also aggregates user reviews.
  • Tucows : Older well known site with reviews.
  • LifeHacker : Along with everything else he reviews software. Good straightforward reviews of unique apps.

[ad name=”Synap blog wide”]
There are probably many I missed so add a comment if you think I should add to that list.

What follows is my list of programs that I personally have found to be extremely useful, and that you may have not heard of:

Read More Read More