Accounting Tax and IT Services

Microsoft Excel can be used extensively to work with data. A worksheet in Excel has 17 billion cells (17,142,120,448) that can contain information on one worksheet of the workbook. Excel has 1,048,576 rows and 16,348 columns (A to XFD). Excel versions before 2007 have16 million cells (16,777,216) that can contain information on one worksheet of the workbook. This consists of 65,536 rows and 256 columns (A to IV). Note that 65536 is a power of two,   2 to the 16th power (2^16). How we read this data on the worksheet, store it in our PC’s memory and write it back into the format that we want, can be achieved in various different ways . Microsoft provides numerous different techniques in their Excel application. We can choose the appropriate techniques that can achieve unheard of high speeds. We can also stick to techniques that achieve the same result but take more time and can even cause our worksheets fall over. This could result in a loss of unsaved data an...

We often spend a lot of time to write VBA code to assist us in completing tasks in Microsoft Applications whether an Excel spreadsheet, Access database, Outlook Message, Word Document. This code can be used by other users on the same scenario. We can make our code available to other users. This code can be protected to ensure integrity. The procedure below describe how to create distributed software for an Excel application. The process for other Microsoft applications will be similar. 1. Once you have completed, debugged and tested your program and are happy that it works, protect      your code as follows: Right click on your module or project name and select VBAProject Properties. Assume your sub name is "ProtectedCode" In the General Tab type in the name of your project e.g. "CodeForDistribution". In the Protection Tab select Lock project for viewing  and enter a password for your project. Click on OK. 2. Save your project but select the file type...

1.        Use auto complete word: Control + Space This invokes intellisense A further press of the Tab key will select the highlighted word If the Spacebar key instead of the Tab key is pressed it will add a space after selecting the highlighted word. 2.        View the definition: Shift + F2  and back to View the last position: Control + Shift + F2 2.1          If we have the following code in our module: Sub Calc()                   Dim result as Long                 Result = PerformCalc(5, 10)                 MsgBox “Result is “ & result                 End...

1.        When you look in your VBA project screen it will represent a worksheet name   “Sheet1” (which is the default name when you open a workbook) as follows in the VBA project explorer: Microsoft Excel Objects Sheet1(Sheet1)                 ß The name in brackets is the name as you see it in the workbook and is the worksheet name.                                                 ß The name on the left is the code name. The worksheet name can be changed in the workbook or in the code module by selecting the sheet object and then changing the Name property in the properties window. The code name can only be changed in the properties window under the (Name) property. 2.        To refer to the worksheet name in code w...

1. Display the immediate window: Control + G. This window is giving the result of debug.print commands 2. Auto Complete Word: Control + Space This invokes intellisense A further press of the Tab key will select the highlighted word If the Spacebar key instead of the Tab key is pressed it will add a space after selecting the highlighted word. 3. View the Watch Window: Alt + V + H 4. Tab: To move lines of code to the right (Indent). 5. Shift + Tab: To move lines of code to the left (Out dent). 6. Shift + F2: Get the definition of the item under the cursor. 7. Ctrl + Shift + F2: Go to the last cursor position. 8. Alt + F11: Switch between Excel and the VBA Editor. 9. Ctrl + R: View the Project Explorer Window. 10. Ctrl + Shift + 8 (or Ctrl + *): Get the current region on a worksheet. 11. F4: View the Properties Window. 12. F5: Run the code from the current sub. 13. F8: Step into the code. 14. F9 (or click left margin): Add a breakpoint to pause the code. ...

1        Use option explicit This will force you to declare variables. Option explicit can be typed in at the top of your module before any code or it can be automatically placed there by selecting the following items from the menu: à Tools Options and then select “require variable declaration”    2        Declare the variables as you use them If you declare the variables at the top of the code it becomes very messy 3        Indent your code and use blank lines Shift + Tab align all the code up Tab can then be used to indent the code line by line where appropriate                between loops and other programming statements. 4        Give variables nice names Use long descriptive names. You don’t have to use e.g. ‘lng’ in front if it is a long variable. Control + Space will help to complet...

VAT - 15% VAT amount back to VAT inclusive amount =  VAT amount x R7.6666                                                                            VAT amount  /   0.130434782 VAT inclusive to VAT exclusive = Vat inclusive / 1.15