- 1 – CREATING “USER DEFINED FUNCTIONS”
(Valid credit card number check – MOD-10)
2 – TELEPHONE BILL AUDIT SERVICE
CREATING “USER DEFINED FUNCTIONS”
Using visual basic macros(functions) we can develop custom functions not built into Excel. These functions can then be used in formulas like any other function currently available in Excel. To demonstrate this we will create the “CardOK” function to test the validity of a credit card number. The “CardOk” function will perform a mathematical check to determine if the number in a specific cell could be a valid credit card number (know as the MOD10 check). It makes no determination of credit worthiness, available credit or whether this is an actual credit card number.
On the “Tools” menu, click “Macro”, and then click “Macros”. Type the name of the Macro, in this example type “CardOk” then click “create”.
This should bring up the Microsoft Visual Basic Editor.
Delete all the text on that page by pressing [Ctrl + A], and then press “delete”.
Then cut and paste the following text onto the page:
Function CardOk(CardNum$) As Boolean
‘Logic statement by Richard Harris – obtained from Deja News
‘Implemented by Chui Tey – obtained from Deja News
‘Concatenation and minor correction by Phil Bornemeier
‘Modified for Excel by www.finance-analyst.com
Dim tmpCardNum$, OddSum, EvenSum, i, OneLetter$, Digit, CheckSum
- tmpCardNum$ = CardNum$
CardNum$ = “”
- OddSum = 0
- EvenSum = 0
- ‘Reverse order of cardnum drop non-number characters
- For i = Len(tmpCardNum$) To 1 Step -1
- OneLetter$ = Mid$(tmpCardNum$, i, 1)
- If OneLetter$ <= “9” And OneLetter$ >= “0” Then
CardNum$ = CardNum$ + OneLetter$
- Next i
- ‘Add numbers in odd positions
- For i = 1 To Len(CardNum$) Step 2
- OddSum = OddSum + Val(Mid$(CardNum$, i, 1))
- Next i
- ‘Double numbers in even positions, add up all digits, accumulate
- For i = 2 To Len(CardNum$) Step 2
- Digit = Val(Mid$(CardNum$, i, 1))
EvenSum = EvenSum + Digit * 2 + (Digit * 2 >= 10) * 9
- ‘Add OddSum and EvenSum
- CheckSum = OddSum + EvenSum
- ‘Check if CheckSum divisible by 10
- If CheckSum Mod 10 = 0 Then
- CardOk = True
- CardOk = False
- End If
End FunctionSponsored Links
Now press [Atl + Q] to quit Microsoft Visual Basic Editor and return to Excel.
The function is now ready to be used. To test it, format cell A1 for text by highlighting cell A1 then pressing [Ctrl+1], click “number”, then click “text” and finally “ok”. Then enter in cell A1 enter the following valid credit card number:
In cell A2 type:
The value in cell A2 should be “TRUE”
You can also access this function on the “Insert” menu, click “Function”, in the “Function Category” box select “User Defined”, “CardOk” should appear in “Function Name” box. Select “CardOk” and press “ok”. In the box that pops up enter “A1” (or the cell containing the credit card number), finally press “OK”.
One final note, this function (MOD10 check) is being used by many of the smaller e-commerce sites that can’t afford to have online credit card authorization!!
TELEPHONE BILL AUDIT SERVICE
Marketing Partners are now welcomed!
Do you know who is reviewing your Company’s telephone bill on a monthly basis?
If your Company is like most, the answer is a scary: NO ONE!
In fact, most US corporations pay their telephone bills without scrutinizing or challenging them. Department managers caught up in the operational demands of their departments, can’t possible do a detail analysis of their bill to discover errors, correct the problems, and obtain refunds. Usually a quick month-over-month comparison of the dollar amount on the bill is all that is done, before it is approved and sent off to accounts payable for payment.
Did you know that 60% of all US Corporations are being overcharged by as much as 30% on their telephone bills? While the majority of these overcharges are a direct result of the application of incorrect rates, other overcharges include incorrect tax rates, double billings, accounting errors, etc. Even though you may have negotiated excellent rates with the phone company, it doesn’t mean your bill reflects those rates. If you haven’t performed a complete detailed analysis of your telephone bill in the last two years, chances are your company is overpaying, maybe as much as 30%.
We, Finance-Analyst.com, can identify commercial telecom billing errors and overcharges for most businesses, and then negotiate for refunds, credits and lower costs. As your consultants, we will be constantly watching your telephone bill, striving to lower it and put more money back into your income statements’ bottom line.
This service is performed at no cost to you. You pay only a percentage of what we recover for you, and if we can’t reduce your costs for you, YOU PAY NOTHING.Sponsored Links
To get started we need the following information from you:
-A copy of your telephone contract, including the rates
-The actual paper bill for the last three months, or
-Electronic bills (CDR on ftp or CD-ROM) for same period, or
-CDR’s from your telephone company.
Once we have this information, we will perform a detailed analysis to verify the accuracy of your bills. The result of our analysis will be a report on errors and the amount of your money you can expect to recoup.
Remember, telephone billing-errors will continue indefinitely until you take the necessary steps to correct them! So reply to send us an email now and a senior consultant will call you to start the process.
You owe it to yourself and your bottom line to talk with us.