Basic Visual Basic for Excel Part IV

This is the fourth and last post in my introduction to learning and utilizing Visual Basic in Excel. As you may have guessed or experienced, writing Visual Basic code from scratch is extremely difficult. Realistically, there are simply too may classes, properties, commands, and syntax rules to learn before a beginner can automate a task by coding from a blank slate. Fortunately, there is an easier way.

If you need to automate a repetitive task in Excel, consider recording a macro, whereby the code for the actions you take is written automatically. The Record Macro button is located in the Macros menu, which is within Excel's View menu. The "Use Relative References" button at the bottom toggles between relative and absolute references on your spreadsheet. If the task you are trying to automate will always occur at the same location on the spreadsheet, make sure Use Relative References is off. However, if the task you are trying to automate will happen at different locations on the spreadsheet, highlight Use Relative References.

One task I find myself repeating a lot, when working with a filtered table in Excel and after having implemented multiple filters, is needing to unfilter the whole table. To do this, I usually just click the Filter button from the Data menu to turn off filtering, select the whole sheet, and click the Filter button again. To demonstrate how to automate this repetitive task, I will record a macro. In order to follow along, you will need a filtered table in Excel (if you need a quick data set, feel free to copy the entity table I used in this previous post).

After filtering one of the columns in your table, click View, from the Macros menu click Record Macro, select a shortcut key (such as Ctrl+Shift+A), and press OK. Everything you do until you click Stop Recording from the same Macros menu will be automatically coded into a new module. While recording, first unfilter the entire sheet by clicking View, Filter; second, select the entire sheet by clicking in the blue box to the top left of A1; and third, click Filter again. Press the Stop Recording button, and within your Visual Basic editor (click Alt+F11 to get there) you should have the following code in Module1:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Selection.AutoFilter
    Cells.Select
    Selection.AutoFilter

End Sub

From the spreadsheet, you are now able to unfilter the entire table easily by clicking Ctrl+Shift+A each time. This has been an example of recording a macro, and this concludes my four-part primer on how to automate tasks in Excel with Visual Basic. I anticipate posting subroutines that I use from time to time, but feel free to comment or contact me with your coding challenges or successes; I may address them in a future post as well.

Read More

Basic Visual Basic for Excel Part III

This is the third post in a series explaining how I use Visual Basic in Microsoft Excel to increase productivity. In my previous post, I discussed the If Then statement as well as different ways to reference cells on a sheet; in this post, I will discuss the Do Loop.

The Do Loop allows you to repeat the same block of code until a condition is satisfied. To demonstrate, I will be using the client entity table from this earlier post; this Do Loop will concatenate a list of all a client's entities into a single field.

After recreating the client entity table in your own spreadsheet, select blank cell G2.  The code below will list all the entities in the active cell, starting with the entity name in the same row as the active cell, and ending with the last entity name before the client's name changes.

Before moving further, however, it will be important to declare a variable that can be used to keep track of which of the client's entities will appear next on the list. A good practice when declaring any variable is to use Option Explicit (for an explanation why, see this article). Declaring a variable is accomplished by typing "Dim" followed by the name for the variable you select, followed by the data type, which can be an "Integer" in this case (other data types are capable of storing different sizes or kinds of data). After declaring the existence of the variable "r," we assign it a value of 0 for reasons described below. After doing all of this, I have the following as the starting point for my code:

Option Explicit
Sub concatenateEntities()

    Dim r As Integer
    r = 0

End Sub

With "r" set to zero, we will follow with our Do Loop. The basic Do Loop to accomplish the procedure described above is as follows:

    Do Until ActiveCell.Offset(0, -6) <> ActiveCell.Offset(r, -6)

        ActiveCell.Value = ActiveCell.Value & "; " & ActiveCell.Offset(r, -5)
        r = r + 1

    Loop

By running the macro with cell G2 selected on a spreadsheet containing my entity table, the active cell should now read as follows: ";  ABC Corporation;  Doe Family Partnership;  Doe Rental Property, LLC;  Doe Equipment, LLC". Here is how this was accomplished:

The "Do Until" line tells the computer to repeat the code that follows until it says "Loop," repeating until the client name changes. It starts by comparing the value in column A in the same row as the active cell with the value in column A in each subsequent row, one at a time. The "r = r + 1" line keeps increasing the count of the row after the prior line sets the active cell equal to whatever it's current value, followed by a semicolon, followed by the value of the cell in column B that contains the next entity name on the list.

Notice that we probably don't want to have the first semicolon that appears in the active cell, which occurs due to the fact that the first time the code sets the active cell equal to itself before adding the semicolon, the active cell has nothing in it. See if you can use an If Then statement to fix that problem; I will post my solution in the comments that follow. For a tutorial on recording a macro, please stay tuned for my next installment of Basic Visual Basic for Excel.

Read More

Basic Visual Basic for Excel Part II

In a previous post, I demonstrated how to create and execute a very simple macro in Microsoft Excel. In this post, I will demonstrate a number of fairly simple enhancements to that macro. The code we will start with is as follows:

Sub lastRun()

    ActiveCell.Value = "Subroutine last run at " & Time()

End Sub

Before beginning, we should assign a shortcut to the macro to make it easier to run. This is accomplished by clicking Alt+F8 from within Excel, selecting the lastRun macro, clicking "Options," and assigning a shortcut key such as Ctrl+Shift+A. Now you can run the macro from Excel simply by clicking Ctrl+Shift+A.

The first enhancement to lastRun is to have the macro enter the time it was last run not in the active cell, but in a cell relative to the active cell on the spreadsheet:

    ActiveCell.Offset(2, 4).Value = "Sub last run at " & Time()

The numbers following "Offset" are the number of respective rows and columns from the active cell into which the time will be entered. Positive numbers offset rows down and columns to the right, while negative numbers offset rows up and columns to the left.

We can also refer to cells without reference to whatever cell is currently selected; in the "Cells" reference below, the row number of the spreadsheet appears first and the column number follows. Of course, the cell we are changing can be set equal to any value we describe as well. Type a message into cell A1 on your spreadsheet; then try the following code:

    Cells(2, 1).Value = "Cell A1 said " & Cells(1, 1).Value & " when the sub ran at " & Time()

Another useful function in Visual Basic is an If Then statement, which works similar to the IF function in Excel. Here is an example of how the lastRun function can run an exception if cell A1 is blank by using an If Then statement:

    If Cells(1,1) <> "" Then

        Cells(2, 1).Value = "Cell A1 said " & Cells(1, 1).Value & " when the sub ran at " & Time()

    Else

        MsgBox "Cell A1 is blank!", vbOKOnly, "For your information..."
        Cells(2, 1).Value = "Cell A1 was blank at " & Time()
        'This is a sample comment in VB

    End If

Note that it is good practice to insert comments throughout your code, as above, so it is easy to remember what the section is supposed to accomplish; the first character of a comment line is an apostrophe, followed by your notes. Another tip is to use blank lines and tabs often to separate and group different sections of your code. In my next post, we will examine the Do Loop, a powerful tool in Visual Basic for people who use Excel frequently.

Read More

Basic Visual Basic for Excel

This will be the first post in a series of posts explaining how to use Visual Basic with Microsoft Excel to accomplish a number of useful tasks. While I am by no means a programmer, I hope I can demonstrate some practical ways to use Visual Basic to help you increase your productivity on a PC.

The first step after opening Excel is to open the Visual Basic Editor. This is accomplished by clicking on the Developer tab within Excel and clicking the Visual Basic button. Alternatively, you can press Alt+F11 to open the editor.

The next step is to insert a new module. In the column on the left, the Visual Basic Editor displays the various Excel spreadsheets that are open. Make sure that the spreadsheet you want the code associated with is selected, and click the Insert menu and select Module. This will bring up a blank screen into which you will type your code.

The next step is to create a subroutine within the module window. This is accomplished by typing "Sub" followed by the name of subroutine followed by "()". The name can be almost anything you want, but it can't have spaces and should describe what the code does so that you can select the subroutine you want from a potentially long list. For a very simple example, I will create a subroutine named lastRun; it will display the time that the subroutine was last executed. Type the following to set up the lastRun macro (the "End Sub" usually appears automatically):

    Sub lastRun()

    End Sub

Between these two lines will be the rest of the code, which in the lastRun example, will be the following:

    ActiveCell.Value = "Subroutine last run at " & Time()

The lastRun subroutine will change the value of the active cell, or the cell that is highlighted on the spreadsheet, to the text in parentheses followed by the time that the subroutine was run. In order to run the subroutine, save your spreadsheet as an .xlsm file (always, always save before testing new code!) and then click the green triangle "Play" button at the top of the Visual Basic Editor. Alternatively, from within the spreadsheet, click View, Macros, make sure lastRun is highlighted, and click the Run button. Note that the text and time appear in whatever cell on the spreadsheet is highlighted when the macro is run.

This has been an introduction to utilizing Visual Basic with a very simple subroutine; in my next post, I will show how to increase the functionality of this simple bit of code.

Read More

Federal Tax Classifications for Business Entities

For federal tax classification purposes, a business is classified as either a "business entity," which is any entity recognized for federal tax purposes; or a "disregarded entity," which is any entity not recognized or treated separate from its owner for tax purposes. The most common federal tax business entities include a C-Corporation, S-Corporation, and Partnership. Various rules under the Internal Revenue Code determine how a business is treated for federal tax purposes.

The default tax classification for a corporate entity is a C-Corporation. Alternatively, a corporation can elect to be taxed as an S-Corporation by filing Form 2553 with the IRS and meeting certain requirements.

A single-member limited liability company is the only state-formed entity eligible to be classified as a disregarded entity; this is the default classification. The one exception is where a business is owned equally by a husband and wife in a community property state; this business can also be treated as a disregarded entity. A single-member LLC can also elect to be taxed as a C-Corporation by filing Form 8832 with the IRS; alternatively, it can elect to be taxed as an S-Corporation by filing Form 2553.

The default tax classification for non-corporate, multi-member legal entities (including LLCs and state-law partnerships) is a partnership. A multi-member business may elect to be taxed as a C-Corporation or an S-Corporation in the same manner as a single-member LLC. One of the prerequisites for S-Corporation status is a single class of interest, disregarding differences in voting rights; multi-member LLCs with one class of interest or general partnerships can meet the requirements to be treated as an S-Corporation.

The following chart summarizes these rules:

               State-Law Entity

	Corporation	Single-Member LLC	Multi-Member Eligible Entity
Disregarded	No	Yes	No*
Partnership (Form 1065)	No	No	Yes
C-Corporation (Form 1120)	Yes	Yes	Yes
S-Corporation (Form 1120S)	Yes	Yes	Yes**

*A business owned equally by a husband and wife in a community property state can be treated as a disregarded entity.
**Only certain entities, such as multi-member LLCs with one class of interest and general partnerships, can qualify for S-Corporation status.

Read More

Self-Employment Tax Avoidance for LLC Members

In addition to the income tax, sole proprietors and partners in a partnership must pay self-employment tax on net self-employment earnings. This tax is imposed on "active" trade or business income, as opposed to "passive" income from rents, interest, dividends, capital gains, and income of a limited partner in a partnership.

Corporations pay tax at the corporate level and accordingly, corporate net income is not subject to self-employment tax. As I wrote previously, S-Corporation earnings flow through to the individual shareholders' personal income tax returns and also avoids self-employment tax. A special set of rules applies to income generated by LLCs taxed as partnerships.

These rules come from Prop. Reg. 1.1402(a)-2(h)(2). Technically, these regulations are not binding since the IRS has not issued final regulations, but they are the only administrative guidance available, "they can be relied on to avoid a penalty under IRC section 6406(f), and there is judicial precedence, in Elkins [81 T.C. 669 (1983)], [to] reasonably conclude that the courts will sustain the position of a taxpayer who relies on proposed regulations." Janet Meade, Minimizing Self-Employment Tax of LLC Managing Members, The CPA Journal, June 2006.

Under the proposed regulations, an individual will be treated as a limited partner (and thus avoid self-employment tax) unless "the individual (1) has personal liability for the debts of or claims against the partnership by reason of being a partner; (2) has authority to contract on behalf of the partnership under the statute or law pursuant to which the partnership is organized; or, (3) participates in the partnership's trade or business for more than 500 hours during the taxable year." Individuals who provide health, law, engineering, architecture, accounting, actuarial science, or consulting services cannot take advantage of this rule.

Even if members of the LLC don't meet all of the above tests, self-employment taxes can still be avoided with respect to "amounts that are demonstrably returns on capital invested in the partnership." In general, in order to take advantage of the exceptions to the general rule, the LLC should be manager-managed and the operating agreement should provide for two classes of members, a managing class and an investor class.

The investor class will be treated as limited partners and the managing class will be treated as general partners for self-employment tax purposes. Members of the managing class can avoid self-employment tax on a portion of their share of the partnership's net income if their interests are bifurcated between the managing class and investor class interests. In other words, the "application of the SE tax to LLC members under the proposed regulations depends not only upon their formal status as members or managing-members but also on their level of participation in the entity" (Meade).

Read More

Paying Gift Tax Now vs. Estate Tax Later

In addition to the annual exclusion, which allows individuals to make annual gifts of up to $14,000 per donee without implicating any taxes, there is another way to utilize gifting to family members to minimize estate taxes upon death.

This strategy applies to estates with a value in excess of the federal estate and gift tax exemption. In 2014, this exemption will be $5,340,000, up from $5,250,000 in 2013. Spouses can generally combine their credit amounts, resulting in the passing of estates worth two times the individual estate and gift tax exemption upon the death of the second spouse. Any estates that are worth less will not be subject to any estate or gift tax.

For estates with a fair market value in excess of the exemption amounts, a tax will apply. The estate tax and gift tax statutes are designed so that the estate tax cannot be avoided by gifting assets before death. If assets in the estate in excess of the exemption amount are gifted during the lifetime of the donor, a gift tax will need to be paid at the same rate as the estate tax.

However, paying gift tax is less expensive than paying the estate tax because paying the gift tax permanently removes the tax paid from the donor's taxable estate. If the taxable gift were not made, the amount that would have been paid in gift tax remains in the taxable estate and is itself subject to the estate tax.

For example, assume the current gift and estate tax rate of 40% and a donor who has previously gifted his or her entire exemption amount. In order to gift $1 million to a donee, the donor will make the gift of $1 million and pay a $400,000 gift tax for a total of $1,400,000.

If the donor had not made the gift before he or she passed away, the estate would include the $1,400,000 and be subject to the 40% tax rate. The donee would only receive $840,000 after the estate tax of $560,000 is paid ($1,400,000 * 40%). In other words, the estate would save and the beneficiary would receive $160,000 more simply because the donor made a gift before death instead of waiting to pass his or her estate afterwards.

Timing is key to the success of this planning technique. Under IRC 2035(b), the taxable estate of the donor includes any gift tax paid on transfers made within three years before the donor's death; accordingly, if the donor dies less than three years after making the taxable gift, the strategy fails. On the other hand, the donor is choosing to accelerate the payment of taxes and give up the use of the money paid as tax. "The donor must survive for three years to avoid the §2035 gross up, but must not survive for so long that the value of the loss of use of the money paid as gift taxes exceeds the value of the estate tax savings." Lischer, 846-2nd T.M., Gifts to Minors

Read More