Experience accumulated with VBA

1. When writing a VBA function, if the function returns a value, it is written as follows:

 

'The actual number of rows for obtaining real data
Public Function GetMaxRow () as integer
For I = MinRow To maxrow' loop the smallest row To the largest row
For j = MinCol To maxcol' loop minimum column To maximum Column
If Trim (Cells (I, j). Value) <> "" then', a valid row is considered If the Value of one column in the row is not empty.
Exit
Else' if each value in this row is empty and until the value in the last column is empty, this action is null.
Data after If j = MaxCol then' is considered invalid
GoTo a' jump out of all loops and get the largest row of actual data
End If
End If
Next

Next
A:
GetMaxRow = I
End Function

2. When writing some special functions, such as setting the validity of a column> column order, the code is complicated. You can directly set the settings and record macros, you can directly use the recorded macro code after saving it.

3. Several common events

If the event is opened or closed, you can find open or close in WeekBook. If you are operating the VB Control in a Sheet, add the Sheet name.

4 if you want to Replace some characters in the string, you can use the VBA function VBA. Replace (), such

For I = MinRow To MR
MTM = Cells (I, 3). Value
MTM = VBA. Replace (MTM ,"","")
MTM = VBA. Replace (MTM ,"-","")
Cells (I, 3) = MTM
Next

You can also use the worksheet function WorksheetFunction. Substitute (MTM ,"","")