VBA编程规则及方法技巧

VBA编程规则及方法技巧
The VBA programming rules and method skills are added to the collection
Site map
Site search
JianFanMo
Excel, excel, excel, excel, excel, excel, excel, excel, excel, excel, excel, excel
Return to excel BBS VBA, excel chart, excel, excel, excel, excel, excel, excel, excel, excel, excel, excel
Excel online helps VBA online help excel2007 Excel spreadsheet questions
Excel function application manual
Excel to VBA learning material
Reading the news
VBA programming rules and techniques
[date: 2007-07-09] source: anonymous [font size: large medium small]
1, before a custom function statement plus the Private key, the
function will not appear in the Excel function "paste" dialog box, but you can still use them in the formula. If it is a custom function developed specifically for other VBA processes, you should use the Private keyword to make a statement.
2,
Typically, after user-defined functions, the "paste function" dialog will appear in the "user-defined" category. If you want a custom function to appear in other categories, you must write and execute the VBA code for self-determination
The sense function specifies the category. Such as running Application. MacroOptions
Macro: = "SumPro", Category: = 4 statement, specifies the custom SumPro function to the "statistical function" Category.
3, it's different from the Sub process
Defining the Function Function does not appear in the macro dialog; If the cursor is in a Function, execute the "run child process/user form" command in the VBE editor
In the process, you cannot get the macro dialog box and select the macros that you want to run. Therefore, in the development process, you must take other methods to test the custom function, and you can set the procedure to invoke the function; If the function
In the worksheet formula, you can enter simple formulas for
testing in the worksheet.
If you use a custom function in the formula, return VALUE "VALUE!" It shows that there are errors in the function. The wrong reason may is the logic errors in the code and passed is not correct to function parameters, may perform the disabled actions such as trying to change the format of the cell.
When using parameters in a custom function, the optional parameter must be followed by any required parameter. You can specify an Optional parameter with the keyword Optional before the parameter name.
If you have to determine a certain optional parameters are passed to the function, the parameter declaration shall be for the Variant type, and then used in the procedure code to determine IsMissing function.
To create a custom function of indefinite number of parameters, using an array as the last parameter (or only), and add keyword ParamArray in front of the array. The keyword ParamArray can only be used for the last parameter in the argument list, whose data type is always Variant and is an optional parameter.
In the code of the custom function, you should ensure that at least once the appropriate value is assigned to the name of the function.
The name of the function must follow certain rules (same as the name of the variable). It is best not to take the same form as the cell address and avoid using the same name as the Excel
built-in function name, otherwise Excel will use the built-in function.
If you want to place the Case keyword and the corresponding statement on the same line, add the separator colon (:) between the Case name and the corresponding statement.
Usually,
The comparison of the text in VBA is case-sensitive, and the statement Option Compare
Text is added to the top of the module, which is not
case-sensitive when executed.
To handle the cell area, you don't have to select the cell area in the code first.
The second
When you write a custom function, you must be in the VBA module, not in the code module that is associated with the ThisWorkbook, the worksheet, and the user form.
If the code has to select the cell area, the worksheet that is in the area must be active, and the Activate method can be used to Activate a worksheet.
Try to use the named cell region in the VBA code.
4, when the cell through shortcuts selection area, if the code
is generated by the macro recorder, then carefully check in the code for the selected cell recording actual hard-coded references.
5, if a macro is applied to each cell of the area of the current cell, but the user can select the whole row or column, usually, we don't need to traverse district of each cell, then this macro should create a child district, make it only consists of the empty cell.
Excel allows multiple constituencies at the same time.
If 7, defines the name of the cell area, use the definitions in the code, the name of the area even in the name of the cell to add or delete rows or columns, also can ensure the correct use of the cell area.
Using annotations in the VBA code, you can briefly describe the purpose of each process written, any modifications to the process, and the purpose of describing the variable.
You can also temporarily set up the VBA statement as a comment block for debugging purposes by using the "edit the comment block/uncomment block" in the "edit" toolbar in VBE.
Edit the control in the worksheet. While dragging the control, press Alt, the button will align to the grid line of the worksheet. While dragging, press Shift, and the button is square
Normally, setting the EnableEvents property of the Application
object to False can prohibit the occurrence of an event. However, it is important to note that this property only works for real Excel objects such as Workbooks, Worksheets, Charts, and so on.
Reading: 2024 times
Entry: admin
[comment] 【 print 】
Last post: 50 answers to the common questions of excel vba
Next installment: complete tutorial for collections and classes in VB (download)
Related news
This article reviews all comments
comment
Comments: words
Name:
Respect online ethics and comply with the relevant laws and regulations of the People's Republic of China
Responsible for any civil or criminal liability arising directly or indirectly due to your actions
This site administrator shall have the right to reserve or delete any content of his jurisdiction message
This site has the right to reprint or quote your comments in the website
Participating in this review indicates that you have read and accepted the above terms
Webmaster statistical
Site introduction - advertising service - website map - help information - contact information
Copyright & copy; iwms
Links:
Powered by
Prepare the ICP for iwms 4.4 07008583。