Accounting Tax and IT Services

Excel: LAMBDA function What is a LAMBDA function? The LAMBDA function come from Alan Turing's (English mathematician, computer scientist, logician, cryptanalyst, philosopher, and theoretical biologist) professor Alonzo Church who invented LAMBDA calculus. The LAMBDA function is named after Lambda calculus. LAMBDA function moves the VBA User-Defined-Functions to the name manager (compatible with Excel Online without learning Typescript). You can define your own custom function using Excel's formula language.  With LAMBDA you can take a formula that you have build in Excel and wrap it up in a LAMBDA function and give it a name (like "MYFUNCTION"). Then anywhere in your sheet you can refer to MYFUNCTION, re-using that custom function throughout your sheet. It facilitates using recursion within a formula, which means you can use the function to call itself one or more times until a specified condition is met at which time the rest of each repetition is processed from the ...

 Excel : CHOOSE Function - Basic understanding and examples Function =CHOOSE(index_num,value1,[value2]…) Basic Understanding The CHOOSE function use index_num to return a value from the list of value arguments.  Up to 254 values can be in the list of arguments. Index_num must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 245. If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error. The arguments can be numbers, cell references, defined names, formulas, functions or text. If index_num is an array, every value is evaluated when CHOOSE is evaluated.  The value arguments to CHOOSE can be range references as well as single cell values. CHOOSE can be used in non-contiguous ranges, unlike INDEX, LOOKUP, VLOOKUP and HLOOKUP. CHOOSE is useful when you need to refer to cells on different worksheets or in other workbooks. Examples 1. =CHOOSE(3,70,90,54)    ...

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...