Excel Ramblings
A few tidbits about Microsoft Excel. I've been using Excel since 1992 and teaching it for the last few months. I plan on posting lots of hints, tips, tricks, and general info to assist anyone else who wants to learn more.
Formula Snippits
Count how many "a"s are in cell A1:
=LEN(A1) - LEN(SUBSTITUTE(A1, "a", ""))
Look for duplicates in a list:
=IF(COUNTIF($A$1:A1,A1)>1,"***","")
To create a dynamic-length named range in column A:
=OFFSET($A:$A,0,0,COUNTA($A:$A))
Color rows for easier reading after printing:
=MOD(ROW()-1,6)<=2
Show the path of the current workbook:
=LEFT(CELL("filename"),SEARCH("[",CELL("filename"))-1)
Splash Screen
Step-by-step for building a splash screen for a special project:
1. Start the Visual Basic Editor
2. Add a UserForm.
3. Use the form to build your actual splash screen.
4. Add the following code to the userform:
Private Sub UserForm_Activate()
' Set Splash timer for 5 second display
Application.OnTime Now + TimeValue("00:00:05"),
"UnloadForm"
End Sub
Private Sub UserForm_QueryClose (Cancel as Integer, CloseMode as Integer)
If CloseMode <> vbFormCode then Cancel = True
End Sub
5. Add a new Module and insert this code:
Sub UnloadForm()
Unload UserForm1
End Sub
6. Finally, add this code to the workbook:
Private Sub Workbook_Open()
UserForm1.Show
End
Non-Printable Workbooks
I work with confidential data on occasion and I don't want end users printing
out copies
of these documents. Here's a simple way to stop that:
1. Right-click on the sheet tab and click on View Code.
2. Add the following code:
Private Sub Workbook_BeforePrint(Cancel as Boolean)
Cancel = True
End Sub
The only problem with this is the ability for the user to disable macros. I get
around that
by using macros to build the report. This way, if macros aren't enabled, they
can't see the data.
Demonstration Files
When I first started with Excel YEARS ago, I always found it very helpful to
deconstruct
someone elses work, figure out what made it tick, and then use that new-found
information
to build what I needed. To that end, here are some of the demo-type files I've
built:
Gantt Chart
A sample of a unique Gantt Chart built in Excel.
Batch Invoice
I was asked to build an invoice tracking system for a contract installer. This
gets a bit confusing, but here goes:
The parent company cuts work orders for each job they want done.
The parent company wants periodic invoices for all work done from the
installer. These must include information from the work orders. They also would
ask for them at strange times, so reporting by month was out of the question.
This workbook has 3 sheets:
The first sheet (Data) is where all of the raw data goes. There is a field for
quantity, but there was never a call to perform more than one service at a time
so the field isn't used in any of the formulas. It's just there so it will
print on the invoice. The current invoice number is also entered here.
The second sheet (Report) is the invoice itself. Cell B4 is where you designate
what invoice needs to be printed. Formulas in the hidden column H then go
though the Data sheet and find all of the entries that have that invoice
number.
The third sheet (Pull) contains all of the pricing information for each
service.