Skip to main content

【VB】 Excel Tickmark, an add-in for auditors and accountants

· 4 min read
Yan-Ying Liao

Main page

https://noworneverev.github.io/TickMark/

Download

Click me

Repo

https://github.com/noworneverev/TickMark

Install

Execute the msi set-up file, and keep clicking next step til end. Then open your Excel, there should pop out a window asking your permission to install the program. Click yes or ok and wait a minute. If you want to remove this add-in, go to control panel to remove this Excel Ribbon like any other software.

Screenshot

Screenshot

Demo

Before start

This was my first Visual Basic program. Before I wrote this Visual Basic based add-in, I only had some basic knowledge of VBA. I spent the whole March building this add-in and self-learning Visual Basic simultaneously.

Why and Purpose

I had an internship in one of the Big 4 when I was in college, and from that experience I knew that there's customized tool called tickmark embedded in firm's Excel which is designed to help auditors to deal with working papers. In that month, I heard a senior staff said she would miss this add-in when she left. Therefore, after 2 years, when I prepared for the boring CPA exam, I suddenly had an idea that maybe I could create an Tickmark Excel add-in on my own, and there it is.

Details

1. PBC text-box

PBC

Pressing PBC button would add a text-box with "PBC" texting in your worksheet's top-left corner. "PBC" is abbreviated from "prepared by client".

2. Font-Family

Font-Family I isolated some commonly-used fonts, including "Arial", "Book Antiqua", "Calibri", "標楷體", "微軟正黑體", "新細明體". You could save some time from selecting these fonts from the drop-down.

3. Electronic working paper mark

Electronic working paper mark As far as I know, every accounting firm has its oftenly used mark, so I made some marks that maybe you could use. (These marks came from two of the Big 4.)

4. Graphic Symbol, Greek Alphabet & Roman Numerals

Graphic Symbol Greek Alphabet Roman Numerals

Several symbols that you could used to represent specific cells.

5. Sum a b c

Sum a b c These symbols could be useful when your need to sum some cells' value up, but they are scattering in the worksheet.

6. Down & right arrow

Down arrow Right arrow Select a range and you can draw an arrow that you need.

7. Arrow & textbox

Arrow textbox Select a cell to add a connecting arrow and a textbox to the corner.

8. Range arrow

Range arrow Select a range to draw an arrow connecting with multiple lines in the corner.

9. Others

Others Open the calendar pane, add a checkbox to the selected cell, the bottom double line, and the format with a thousands separator.

10. Fill Color

Fill Color 5 fill colors that you could choose and one button to remove fill color.

11. Direction

Direction Create two boxes pointing to each other. You could use this to imply that two selected cells are related.

12. Note

note Simply auditor's nightmare.

13. Left Brace

Left Brace Select a range to draw an entry brace.

14. Insert Column

Insert Column Insert a narrow column to let you put some marks.

Cell Link Create boxes linked with hyperlinks to the corner of both selected cells. This function is the most complicated and also the main reason why I created this add-in.

HyperLink Build hyperlinks on both of the selected cells and remove the default hyperlink format.

17. Signature

Signature To set up your name, go to File > Options > General > User name.

18. Marker

Marker For in-charge, manager or partner to review the working paper.

19. Get Motivated Every Day!

Get Motivated Every Day! 900 motivated quotes I collected from Reddit, fb/the idealist and other internet sources. I aimed at encouraging those who are also suffering in Big 4 by making this. I used rnd function to generate the quote you will see, so you might see the same quotes sometimes, and also some you might never see. 😊

I will add other quotes which I read after I completed this project to a VBA based function. Stay tuned on my my blog!

There will always be a space between the alt right and alt left.