Creating your own function in Excel

 Excel VBA function to create a delimited file

I use this if I have a spreadsheet of values in a row of cells and I want to turn that row into something I can use as a text import file. This article walks you through how to create a custom Excel function named CREATE_DELIMITED.

Let’s say we start with a spreadsheet similar to the one below. If you want to follow along, create a similar spreadsheet with a few rows of data. We want to turn this into a bar (|) delimited text file that we can use in a table import.

 

To accomplish this, we are going to produce the CREATE_DELIMITED custom function in this workbook. Later we will add it as an Excel add-in so it will be available to any workbook.

 

Follow these steps to create the function:

1.    Open a new blank workbook.

2.    Press Alt+F11 to open the Visual Basic Editor, and then click Insert > Module.  A new module window appears on the right-hand side of the Visual Basic Editor.

3.    Copy and paste the following code into the new module

 

'Custom Function to join a range of cells together with an optional delimiter

 

Function CREATE_DELIMITED(ByVal cellRange As Range, Optional ByVal delimiter As String)

 Dim c As Range

 Dim DelimitedText As String

 Dim Count As Integer

 Count = 0

 DelimitedText = ""

 For Each c In cellRange

  Count = Count + 1

  DelimitedText = DelimitedText & c.Value

  If Count < cellRange.Count Then

   DelimitedText = DelimitedText & delimiter

  End If

 Next

 CREATE_DELIMITED = DelimitedText

End Function

 

Using custom functions

 Now you’re ready to use the CREATE_DELIMITED function that you just produced. Close the Visual Basic Editor and select cell A14 and type one of the following:

 For a bar (|) delimited file, use this:

=CREATE_DELIMITED(A2:G2,"|")

It will produce this:

10599|MICLINK|Alesis MicLink AudioLink Series XLR-to-USB Cable|36|27|79|2

------------------------------------------------------------------------------------------------------

For a quote delimited file separated with commas, use this:

=""""&CREATE_DELIMITED(A2:G2,""",""")&""""

 It will produce this:

 "10599","MICLINK","Alesis MicLink AudioLink Series XLR-to-USB Cable","36","27","79","2"

------------------------------------------------------------------------------------------------------

For a comma delimited file use this:

=CREATE_DELIMITED(A2:G2,",")

 It will produce this:

10599,MICLINK,Alesis MicLink AudioLink Series XLR-to-USB Cable,36,27,79,2

------------------------------------------------------------------------------------------------------

 

Creating the Add-in

To make this function available to any workbook, delete all of the data on the worksheet and save the file as an Excel Add-In.

 

Accept the location that Excel suggests, name the file something descriptive. I saved mine as CREATE_DELIMITED (so original, I know).

Select the Developer tab from your toolbar (you may have to add this, it doesn’t appear automatically). Then select Excel Add-Ins. Your list of add-ins appears, just check the one that contains your CREATE_DELIMITED function and you’re good to go.

 

Now you should be able to use this function in any workbook you create. If you don’t see it, check the Excel Add-ins toolbar item to make sure the Add-in is marked.

 

Until next time!

Leslie

Setting a New Default Checkbook

So, you’ve changed banks and opened a new account. Now it’s time to create a new checkbook in GP and assign it as the default Checkbook ID. No problem, you’ve got this:

1. Create a New GL Account
2. Create a New Checkbook ID
3. Assign the new Checkbook as the default.

This is where the trouble starts. Where in the world can you set the default company Checkbook ID? The truth is, there is no single place to set the default Checkbook ID. You need to set it in several places if you want to use the same checkbook for all of the modules. Each of the following modules has a default checkbook setting.

• Payables Management
• Purchasing | Setup | Payables
• 
• Receivables Management
• Sales | Setup | Receivables
• 
• Sales Order Processing
• Sales | Setup | Sales Order Processing
• 
• Payroll
• HR & Payroll | Setup | Payroll | Payroll
• 
• Bank Reconciliation
• Financial | Setup | Financial | Bank Reconciliation
• 

Now, you need to check to see if you have any checkbook default identified on the Vendor card or Customer card. When processing a receivables or SOP transaction, GP will look for a checkbook ID at the customer card first. If it doesn’t find one there, it will look to the Receivables Management Setup window or the Sales Order Processing Setup window, depending on what kind of transaction you are entering. For payables, it will look first to the Vendor card and then to the Payables Setup window.

You should never have the same Checkbook ID on a card that you have on the setup window. Let the program do the work for you. Only put a separate Checkbook ID at the card level if you have an exception from your usual checking account.

If you find that you do have a Checkbook ID set on the cards and want to remove it, you can use the Class ID to remove it and then roll down the changes. If you have too many classes to do it manually, or no classes at all, you can use SQL to remove the Checkbook IDs. Your update statements would look similar to those below if you wanted to remove them all.

For Vendors:
UPDATE PM00200 SET CHEKBKID = ''

For Customers:
UPDATE RM00101 SET CHEKBKID = ''

If you are using a third-party product such as Mekorma MICR or BGE Green Payable there will be other places where you’ll need to change the default. Take a look at the setup screens for any add-on products you are using and look for a Checkbook ID setting.

If you find some other places where the Checkbook ID may need to be changed, please leave a comment and I’ll include it in the body of the post.

Live the dream!

Leslie

Modified Form 1099 Misc. and the ‘1099s Printed’ report

While preparing our annual 1099s, we had the need to change the name on the 1099 from the Vendor Check Name to an individual’s name. We use the name and social that match the name of the sole proprietor. While the new(ish) field on the Vendor Maintenance window for the address code used on the 1099 is a good start, it doesn’t let you change the Name field that prints on the 1099. Regardless of the Address Code selected, the Vendor Check Name is used by GP to set the name appearing on the 1099.

Oddly, the Vendor Name is used on the ‘1099s Printed’ report instead of the Vendor Check Name.

How the modification works is this. If you need to change the name, use the Address Code of ‘1099’ as the 1099 Address Code. The system will look at the 1099 Address code and if it is ‘1099’ then the Vendor Contact Name is used on the 1099, otherwise the Vendor Check Name is used.

If you would like a copy of these modified reports, you can download the package file from HERE.

Live the dream!

Leslie

Making FRx work with unsupported versions of Dynamics

I know, I know, we should all be using Management Reporter. News flash! Not everybody wanted to change if I could get FRx to print the reports with their new GP versions. Let’s face it, getting that software installed and getting all of your FRx reports migrated is an expense many people would rather not incur. The secret to getting it up and operational is to find the compatible GPConn.dll you need to make it work. That file, by the way, is located in the following folder: C:\Program Files (x86)\Common Files\microsoft shared\Dexterity\GPConn.dll
The trick is getting the right version of the GPConn.dll. I’ve read that you can run the C:\Program Files (x86)\FRx Software\FRx 6.7\FRxReg67.cmd file and it will create the correct GPConn.dll file. While it does create and register the files, it has not always been the file that has worked for me. It’s certainly worth a try, because it has worked now and again. Another discovery I made the other day is that two workstations running the same operating system needed different GPConn.dll to coax FRx to work.
I’ve collected several different versions of the GPConn.dll file and have posted them to my Box account along with a full install of FRx with SP 9. You'll also find SP12 of FRx available there. If you’re looking for an older version, give one of these files a try. You can access them HERE. Try running the FRxReg67.cmd first, but if that doesn’t work, give these others a try.
The combination that worked on my machine, which is running Windows 10 Pro and GP 2016 R2 build 0641 is this:

• OS: Windows 10 Pro version 1709, Build 16299.371
• SQL Driver: SQL Server (not any of the Native Clients)
• GPConn.dll: version date 04/06/2006 (143 kb)
• 32-bit ODBC using SQL Native Client 10

Other combinations may very well work and I ask you to please comment and let us know which combinations worked for you.
If you are getting the error 'The current version of the application could not be determined. The setup will now terminate'  You need to go to Control Panel's Programs and Features applet and then, in the upper left-hand corner select Turn Windows Features on or Off and turn on .Net 2.0, which also enables .Net 3.0. I also installed the Prerequisite named 'Microsoft Data Access', but I don't think that was the key to getting it to work.

Live the dream!
Leslie

It’s Bonus Time!! How Should we Withhold??

Things are going great this year and your company has decided to pay bonuses to all of the employees. YAY!

So, how much should we withhold on the bonus? How should we set up that bonus pay in Dynamics GP?

The first question you need to answer is whether or not the payments are considered Supplemental Wages for Federal tax withholding purposes. Whether wages are classified as regular wages or Supplemental Wages may have significance in determining the amount of income tax required to be withheld. Most bonuses that I’ve seen are in fact Supplemental Wages.

The answer to the withholding question is as close as Internal Revenue Bulletin 2008-24, which outlines nine different scenarios and explains the withholding requirements for each scenario. The nine different scenarios include:

1. Commissions paid at fixed intervals with no regular wages paid to the employee;
2. Commissions paid at fixed intervals in addition to regular wages paid at different intervals;
3. Draws paid in connection with commissions;
4. Commissions paid to the employee only when the accumulated commission credit of the employee reaches a specific numerical threshold;
5. A signing bonus paid prior to the commencement of employment;
6. Severance pay paid after the termination of employment;
7. Lump sum payments of accumulated annual leave;
8. Annual payments of vacation and sick leave; and
9. Sick pay paid at a different rate than regular pay.

Simply defined in IRS Publication 15, “Supplemental Wages are wage payments to an employee that aren't regular wages. They include, but aren't limited to, bonuses, commissions, overtime pay, payments for accumulated sick leave, severance pay, awards, prizes, back pay, retroactive pay increases, and payments for nondeductible moving expenses. Other payments subject to the supplemental wage rules include taxable fringe benefits and expense allowances paid under a non-accountable plan.”

How you withhold on supplemental wages depends on whether the supplemental payment is identified as a separate payment from regular wages. See Regulations section 31.3402(g)-1 for additional guidance.

If you are running a separate check run or separately identify the ‘non-regular’  wages in your records, they are treated as Supplemental Wages. The most obvious would be  a bonus check. The IRS provides that you should withhold at a flat rate of 25% rather than aggregating the bonus with the regular wages.

If the bonus exceeds $1,000,000, you are required to withhold at a rate of 39.7% on the amount in excess of $1,000,000. These amounts and percentages can change every year, you’ll want to check each year and make the appropriate adjustments to your relevant pay codes. The rules are quite complex regarding withholding on Supplemental Wages. The best explanation I’ve found are the scenarios described in Rev. Rul. 2008-29

If you are running a separate check and do not treat it as supplemental wages, the withheld amount will most likely be incorrect. For example, if you run the bonus with an annual frequency, it’s likely no tax would be withheld on a modest bonus. If you include it with regular pay in a semiweekly pay period, it’s likely that too much will be withheld.

FICA/s and FICA/m withholdings are a fixed amount, so those would be calculated the same no matter what kind of wages you're paying.

If you include the bonus with the regular pay, the withholding percentage could be much higher than 25% because of how pay is aggregated. The bonus pay plus the regular pay are added together and withholding is calculated on the annualized sum of the two.

This article speaks only to Federal tax withholding, you'll need to check your state withholding rules on supplemental wages to see how the state requires you to withhold.

Setting it up in Dynamics GP

For Dynamics GP, here’s how you would set up a Bonus pay type using flat rate withholding.

I have three elements highlighted on the screenshot above.

1. Pay Type
2. Pay Period
3. Flat Tax Rate

I use the Pay Type of ‘Other’ because a bonus doesn’t fit into any of the other categories

I use the Pay Period of annually because this is a bonus given just once per year.  If they get a bonus quarterly, then select a Pay Period of Quarterly.

The Flat Tax Rates section is where you would enter the pre-defined percentage for withholding against the bonus amount. This flat tax does not apply to any other pay codes

What difference does it make?.

This section will show you the amounts withheld for Federal Income Tax under several scenarios.

The following calculations are based on these facts: The bonus is $1,000 and it is a year-end bonus. . I used Fabrikam’s Pillar Ackerman for my test subject. She has $122.95 of ‘before tax’ deductions so I applied the tax rates to $877.05 ($1,000 – $122.95). This explains why the 25% flat tax on the bonus was less than $250.

1. Bonus Pay Period is Daily/Misc. Bonus is Included with regular pay:

FIT Withheld on bonus = $375.19

2. Bonus Pay Period is Annual. Bonus is included with regular pay:

FIT Withheld on bonus = $35.76

3. Bonus Pay Period is Daily/Misc. Bonus is a separate check run.

FIT Withheld on bonus = $190.57

4. Bonus Pay Period is Annual. Bonus is a separate check run.

FIT Withheld on bonus = $0

5. Bonus Pay Period is Daily/Misc. Flat Tax Rate is 25%. Bonus is a separate check run.

FIT Withheld on bonus = $219.26

6. Bonus Pay Period is Annual. Flat Tax Rate is 25%. Bonus is a separate check run.

FIT Withheld on bonus = $219.26

As you can see, the amount of tax withheld can vary substantially depending on how you have set up the Pay Code for the Bonus.

How do I get the bonus check to equal a set amount?

Many employers like the bonus check to be devoid of withholding amounts. One approach to this is to take the withholding amount from the regular check. This normally causes the employee be , um, dissatisfied. The answer to this conundrum is the gross-up.

Employers would have to pay a larger gross amount so that after the required withholding is deducted, the desired net amount is achieved. Dynamics GP can perform this calculation for you.

To access the Gross Up utility, navigate to:

HR & Payroll | Utilities | Payroll | Gross Up

Put the net check amount you want, and then adjust the percentages for tax withholding as appropriate. Click on the Calculate button and the system will provide the Gross Amount in order to arrive at the desired check amount. Pretty neat.

I hope this article will give you some food for thought on how to handle the withholding on bonus checks and other supplemental wages.

Live the dream!

Leslie