Excel VBA Custom Function Authoring (UDF, user-defined function)

While it is known that Microsoft Office Excel can support the writing of complex programming and custom functions in the VB language, there has been no such requirement.

The problem is to calculate a value based on a list of arrays, but the computational process is more complex, it takes a few steps, if you do not program, you have to go through some intermediate cells to complete the calculation, but this will make the whole table is very bloated, and it is inconvenient to add new columns.

In this case, writing a custom function is almost the only way out. Fortunately, it is not too much trouble.

1. First, you may want to enable the development tool in the options, save the file type will be replaced by XLSM.

2. Open the VB Editor to write code, add modules, write their own functions, write finished do not need to compile, in the Excel table can be used.

3. This VB compiler or gray often the primary, so write code is not very convenient is inevitable, hint and complement the function are weaker, endure. In addition, not familiar with the class structure of Excel related to writing code is also difficult to write, fortunately I just wrote some simple operation.

4. Even for simple operation of cells, because there are many situations, such as not valid values, it is very troublesome to write themselves, and the most convenient way is to call the system the original method.

Post a function of this write, is a list of proceeds to seek the maximum drawdown.

1 FunctionJdrawback (rrange asRange)2     DimN as Long3     DimD as Double4     DimCurrentmax as Double5     DimCurrentmaxdrawback as Double6Currentmaxdrawback =07 8      for  eachClinchrrange9D =Worksheetfunction.sum (cl, D)Ten         IfD > Currentmax Then OneCurrentmax =D A         End If -  -         Ifcurrentmax-d > Currentmaxdrawback Then theCurrentmaxdrawback = Currentmax-D -         End If -     NextCL -  +Jdrawback =Currentmaxdrawback - End Function            

Excel VBA Custom Function Authoring (UDF, user-defined function)