This example is a password window (1)
IF Application.inputbox ("Please enter your password:") = 1234 THEN
[A1] = 1 'The password is executed correctly
Else: msgbox "Password error, will be exited!" 'This line is set with the second line together
END IF
This example is a password window (1)
X = msgbox ("Is it really necessary to check out?", Vbyesno
IF x = vbyes then
Close
This example is set a work table password
Activeesheet.protect password: = 641112 'Protect worksheets and set passwords
Activeesheet.unprotect password: = 641112 'Undo worksheet protection and cancel password
'This example closes all other workbooks other than the workbook that is running this example and saves its changes.
For Each W in Workbooks
If W.NAME <> thisworkbook.name dam
W.Close SaveChanges: = TRUE
END IF
Next w
'When you open a workbook each time, this example maximizes the Microsoft Excel window.
Application.windowState = XLmaximized
'This example shows the name of the active worksheet.
Msgbox "The name of the agent sheet is" & activesheet.name
'This example saves a copy of the current active workbook.
ActiveWorkbook.savecopyas "c: /temp/xxxx.xls"
'The following process activates the fourth working table in the workbook.
Sheets (4) .activate
'The following process activates the first workbook in the workbook.
Worksheets (1) .activate
'This example closes the workbook containing this paragraph code by setting the Saved property to True, and discard any changes to the workbook.
Thisworkbook.saved = true
THISWORKBOOK.CLOSE
'This example sets the automatic recalculation function so that Microsoft Excel does not automatically recalculate the first worksheet.
Worksheets (1) .enablecalculation = false
'The following process opens the MyBook.xls workbook in the folder named myfolder on the C drive.
Workbooks.open ("c: /myfolder/mybook.xls")
'This example shows the value in cell A1 on the worksheet Sheet1 in the active workbook.
Msgbox Worksheets ("Sheet1"). Range ("A1"). Value
This example shows the name of each worksheet in the active workbook
For Each WS in Worksheets
Msgbox ws.name
Next ws
Add a new worksheet to the active workbook and set the name of the worksheet?
Set newsheet = worksheets.add
Newsheet.name = "current budget"
This example moves the new work surface to the end of the workbook
'Private subworkbook_newsheet (Byval SH as Object)
Sh.move instance: = Sheets (Sheets.count)
End Sub
This example moves the new work surface to the end of the workbook
'Private sub app_workbookNewsheet (Byval WB As Workbook, _Byval SH As Object)
Sh.move instance: = wb.sheets (wb.sheets.count)
End Sub
This example creates a new work table and then lists the names of all worksheets in the active workbook in the first column.
Set newsheet = sheets.add (type: = xlworksheet)
For i = 1 to sheets.count
Newsheet.cells (i, 1) .value = Sheets (i) .Name
Next I
This example moves the tenth line to the top of the window?
Worksheets ("sheet1"). Activate
ActiveWindow.Scrollrow = 10
When calculating any worksheet in a workbook, this example is sorted by the A1: A100 area of the first worksheet.
'Private subworkbook_sheetcalculate (Byval SH as Object)
WITH WORKSHEETS (1)
.Range ("A1: A100"). Sort key1: =. RANGE ("a1")
End with
End Sub
This example shows the print preview of the worksheet Sheet1.
Worksheets ("sheet1"). PrintPreview
This example saves the current active workbook?
ActiveWorkbook.save
This example saves all open workbooks and then close Microsoft Excel.
For Each W In Application.Workbooks
W.Save
Next w
Application.quit
The following example adds two new worksheets in front of the first worksheet of the active workbook?
Worksheets.add count: = 2, Before: = Sheets (1)
This example is set for 15 seconds to run the My_Procedure process, starting from now.
Application.ontime Now TimeValue ("00:00:15"), "My_Procedure"
This example sets MY_PROCEDURE to run in the afternoon.
Application.ontime TimeValue ("17:00:00"), "My_Procedure"
This example undo the previous example to the ONTime setting.
Application.ontime earliestime: = TimeValue ("17:00:00"), _
Procedure: = "MY_PROCEDURE", Schedule: = FALSE
Whenever a worksheet is recalculated, this example adjusts the width of the A column to the F column.
'Private sub worksheet_calculate ()
Column ("a: f"). Autofit
End Sub
This example enables calculations in the active workbook to use only the displayed digital precision.
ActiveWorkbook.PrecisionAsDisplayed = true
This example cuts the A1: G37 area on the worksheet Sheet1 and puts the clipboard.
Worksheets ("sheet1"). Range ("a1: g37"). CUT
Calculate method
Calculate all open workbooks, a specific worksheet in a workbook or a cell specified in the workheet, as shown in the following table:
'To calculate' according to this example
All open workbook 'Application.calculate (or just call "specifies the worksheet' calculates the specified worksheet Sheet1 Worksheets (" Sheet1 "). Calculate
Specified area 'Worksheets (1) .ROWS (2) .calculate
This example sets the automatic recalculation function so that Microsoft Excel does not automatically recalculate the first worksheet.
Worksheets (1) .enablecalculation = false
This example calculates the formula of the a column, the B column, and C columns in the Sheet1 area.
Worksheets ("sheet1"). UsedRange.columns ("a: c"). Calculate
This example updates all links in the current active workbook?
ActiveWorkbook.Updatelink name: = Activeworkbook.linksources
This example sets the scroll area of the first worksheet?
Worksheets (1). Scrollarea = "a1: f10"
This example has created a new workbook, prompting the user to enter the file name, and then save the workbook.
Set newbook = workbooks.add
DO
FNAME = Application.getsaveasfilename
Loop unsil fname <> false
Newbook.saveas filename: = fname
This example opens an Analysis.xls workbook and then runs the Auto_Open macro.
Workbooks.open "analyysis.xls"
Activeworkbook.runautomacros xlautoopen
This example runs the auto_close macro on the active workbook and then close the workbook.
With Activeworkbook
.Runautomacros XlautoClose
.Close
End with
In this example, Microsoft Excel displays the path and file name of the active workbook to the user.
'Sub usecanonical ()
Display the full path to user.
Msgbox ActiveWorkbook.FullNameurlencoded
End Sub
This example shows the path and file name of the current workbook (assuming that this workbook is not saved).
Msgbox ActiveWorkbook.Fullname
This example turns off Book1.xls and discards all changes to this workbook.
Workbooks ("Book1.xls"). Close SaveChanges: = FALSE
This example closes all open workbooks. If an open workbook has changed, Microsoft Excel will display the interrogation to save the dialog box and the corresponding prompt.
Workbooks.close
This example recalculates all worksheets of the current active workbook before printing?
'Private Sub Workbook_beforeprint (Cancel As Boolean)
For Each Wk in Worksheets
wk.calculate
NEXT
End Sub
This example summarizes the first column data in the query table one, and the sum of the first column data is displayed below the data area.
Set c1 = sheets ("sheet1"). QueryTables (1) .resultRange.columns (1)
C1.NAME = "Column1"
C1.End (XLDown) .offset (2, 0) .formula = "= SUM (Column1)" This example cancels all changes in the active workbook?
Activeworkbook.rejectallChanges
This example uses the planning solve function in business problems to maximize the total profit. The Solversave function saves the current problem to a certain area on the active worksheet.
Worksheets ("sheet1"). Activate
Solverreset
SolverOptions precision: = 0.001
Solverok Setcell: = Range ("Totalprofit"), _
Maxminval: = 1, _
BYCHANGE: = Range ("C4: E6")
SolverAdd Cellref: = Range ("F4: F6"), _
RELATION: = 1, _
Formulatext: = 100
SolverAdd Cellref: = Range ("C4: E6"), _
RELATION: = 3, _
Formulatext: = 0
SolverAdd Cellref: = Range ("C4: E6"), _
RELATION: = 4
Solversolve Userfinish: = FALSE
Solversave Savearea: = Range ("A33")
This example hides Chart1, Chart3, and Chart5.
Charts (Array ("Charop1", "Chart3", "Chart5"). Visible = false
When a worksheet is activated, this example is sorted by the A1: A10 area.
'Private sub worksheet_activate ()
Range ("a1: a10"). Sort key1: = range ("a1"), Order: = Xlascending
End Sub
This example changes the Microsoft Excel link.
ActiveWorkbook.changelink "C: /excel/book1.xls", _
"c: /excel/book2.xls", Xlexcellinks
This example enables automatic screening arrows on protected worksheet?
ActiveSheet.enableautofilter = TRUE
Activesheet.Protect Contents: = true, userinterfaceOnly: = TRUE
This example will the active workbook be read-only?
ActiveWorkbook.changefileAccess mode: = xlreadonly
This example automatically updates a shared workbook every three minutes?
ActiveWorkbook.AutOupDatefrequency = 3
The following SUB procedure clears the content of all cells on the SHEET1 in the active workbook.
'Sub Clearsheet ()
Worksheets ("sheet1"). Cells.clearContents
End Sub
This example shuts down the scroll bar for all workbooks?
Application.displayscrollbars = false
If the file attribute with a password protected workbook is not encrypted, this example sets the password encryption option for the specified workbook.
'Sub setPasswordOptions ()
With Activeworkbook
If .passwordncryptionProvider <> "Microsoft RSA Schannel Cryptographic Province" Then.SetPasswordEncryptionOptions _
PasswordncryptionProvider: = "Microsoft RSA Schannel Cryptographic Province", _
Passwordncryptionalgorithm: = "RC4", _
PasswordncryptionKeyLength: = 56, _
PasswordEncryptionFileProperties: = TRUE
END IF
End with
End Sub
In this example, if the active workbook cannot be written, the Microsoft Excel sets the string password to write a password for the active workbook.
'Sub usewritepassword ()
DIM STRPASSWORD AS STRING
Strpassword = "secret"
'Set Password to a string if allowed.
If ActiveWorkbook.writereServed = False Then
ActiveWordBook.writePassword = strpassword
END IF
End Sub
In this example, Microsoft Excel opens a workbook called Password.xls, sets its password, and then close the workbook. This example is falsely named Password.xls on the C: / drive.
'Sub UsePassword ()
Dim wkbone as workbook
Set wkbone = Application.Workbooks.open ("c: /password.xls")
WKBONE.PASSWORD = "Secret"
WKBONE.CLOSE
'Pay attention to the Password property readable and returns "********".
End Sub
This example changes the current window of Book1.xls to the display formula.
Workbooks ("Book1.xls"). Worksheets ("Sheet1"). Activate
ActiveWindow.displayFormulas = TRUE
'This example accepts all changes in the event workbook?
ActiveWorkbook.acceptallChanges
This example shows the path and name of the active workbook
SUB Usecanonical ()
MSGBOX 'message box
[b7] = ActiveWorkbook.FullName 'current workbook
[b8] = ActiveWorkbook.FullNameurlencoded 'Activity Workbook
End Sub
This example shows the full path to the Microsoft Excel launch folder.
Msgbox application.startuppath
This example shows the name of each worksheet in the active workbook.
For Each WS in Worksheets
Msgbox ws.name
Next ws
This example closes all other workbooks outside the workbook that is running this example and saves its changes.
For Each W in Workbooks
If W.NAME <> thisworkbook.name dam
W.Close SaveChanges: = TRUE
END IF
Next w
ACTIVATE event
This event is generated when a workbook, worksheet, chart, or embedded chart. When a worksheet is activated, this example is sorted by the A1: A10 area.
Private subworksheet_Activate ()
Range ("a1: a10"). Sort key1: = range ("a1"), Order: = Xlascending
End Sub
Calculate event
For the Worksheet object, this event is generated after recalculating the worksheet
Whenever a worksheet is recalculated, this example adjusts the width of the A column to the F column.
Private subworksheet_calculate ()
Column ("a: f"). Autofit
End Sub
BEFOREDOUBLECLICK event
Activate method applied to the Worksheet object.
This event is generated when double-clicking a worksheet. This event is first click on the default.
Private Sub Expression_BeForedoubleClick (Byval Target As Range, Cancel AS Boolean)
Expression references the variables of the Worksheet type object with event declarations in the class module.
Target is required. Double-click the cell that happens closer to the mouse pointer.
Cancel is optional. When the event occurs, it is false. If the event process is set to True, the process will not perform the default double-click operation after execution.
BEFORERIGHTCLICK event
Activate method applied to the Worksheet object.
This event is generated when you right-click a worksheet, and this event is first configured with the default.
Private sub expression_beforerightclick (ByVal Target Asheng, Cancel As Boolean)
Expression references the variables of the Worksheet type object with event declarations in the class module.
Target is required. Right-click the cell that closer to the mouse pointer occurs.
Cancel is optional. When the event occurs, it is false. If the event process sets this parameter to True, the process does not perform the default right-click operation after the end of the process.
Change event
This event is generated when the user changes the cell in the worksheet, or the external link causes this event when the cell changes.
Private subworksheet_change (byval Target As Range)
Target changes the area. Can be multiple cells.
Description
Recalculating cell changes do not trigger this event. You can recalculate the operation using the Calculate event capture worksheet.
This example sets the color of the changed cell to blue.
Private subworksheet_change (byval Target As Range)
Target.font.colorindex = 5
End Sub
Deactivate event
This event is generated when chart, worksheet, or workbook is turned from active state to non-active state.
Private subject_deactivate ()
Object Chart, Workbook or Worksheet. For more information on using events for Chart objects, see the Usage of the Chart object event.
This example is arranged in all open windows when the workbook is turned in an inactive state.
Private subworkbook_deactivate ()
Application.windows.Arrange XlarRangestyleletiled
End Sub
FOLLOWHYPERLINK event
This event occurs when you click any hyperlink on the worksheet. For an application level or workbook level, see the SheetFollowHyperLink event.
Private subworksheet_followhyperlink (Byval Target As HyperLink) Target HyperLink type, required. A HyperLink object representing the hyperlink target.
This example retains a list or history on all links accessed in the current active workbook.
Private subworksheet_followhyperlink (byval target as hyperlink)
With userform1
.Listbox1.additem target.address
.Show
End with
End Sub
PivottableUpdate event
After the data perspective update in the workbook.
Private sub expression_pivottableUpdate (byval target as pivottable)
Expression references the variables of the Worksheet type object with event declarations in the class module.
Target is required. The selected data perspective.
This example shows a message indicating that the PivotTable has been updated. This example assumes that you have declared an object of the Worksheet type with an event in the class module.
Private subworksheet_pivottableUpdate (Byval Target as pivottable)
MsgBox "The Pivottable Connection Has Been Updated."
End Sub
SelectionChange event
This event will be generated when the selected area on the worksheet changes.
Private Sub Worksheet_SelectionChange (Byval Target as Excel.Range)
Target newly selected area.
This example scrolls the workbook window until the selected area is on the upper left corner of the window.
Private subworksheet_selectionchange (Byval Target As Range)
With activeWindow
. Scrollrow = target.row
. ScrollColumn = target.column
End with
End Sub
This example shows the values in the cell A1 on the worksheet Sheet1 in the active workbook.
Msgbox Worksheets ("Sheet1"). Range ("A1"). Value
This example shows the name of each worksheet in the active workbook.
For Each WS in Worksheets
Msgbox ws.name
Next ws
This presentation adds a new worksheet to the active workbook and set the name of the worksheet.
Set newsheet = worksheets.add
Newsheet.name = "current budget"
This example turns off the workbook book1.xls, but does not prompt the user to save the changes. All changes in book1.xls will not be saved.
Application.displayAlerts = false
Workbooks ("Book1.xls"). Close
Application.displayalerts = true
This example sets the prompt when the file is saved, requiring the user to enter summary information.
Application.promptforsummaryInfo = TRUE
This example shows the full path to Microsoft Excel.
Private Sub aa ()
Msgbox "The path is" & applibility.path
End Sub
Example Displays the path and file name of each available load macro.
For Each a in Addins
Msgbox a.fullname
Next A
The chDIR statement changes the current directory or folder.
Chdir path
In Power Macintosh, the default drive is always changed to the drive specified in the PATH statement. The full path is specified by the volume label name, the relative path begins by the colon (:). Chdir can identify the alias specified in the path:
ChDIR "MacDrive: TMP" 'in Macintosh
This example shows the current path separator.
Msgbox "The Path Separator Character IS" & _
Application.pathseparator
MOVE method
Move a specified file or folder from one place to another.
grammar
Object.move destination
Move method syntax has the following sections:
Partial description
Object is required. Always a name of a FILE or FOLDER object.
Destination must be. Documents or folders to move to the target. There is no wildcard.
CreateFolder method
Create a folder.
grammar
Object.createfolder (Foldername)
The REATEFOLDER method has the following sections:
Partial description
Object is required. Always a name of FileSystemObject.
Foldername must be required. String expressions, it identifies the created folder.
This example uses the MKDIR statement to create a directory or folder. If you do not specify a drive, a new directory or folder will be built in the current drive.
MKDIR "mydir" 'creates a new directory or folder.
NAME statement example
This example uses the Name statement to change the name of the file. In the example, it is assumed that all the directory or folders used are existing. In Macintosh, the default drive name is "HD" and the path portion is separated from the colon.
Dim Oldname, NewName
OldName = "oldfile": newname = "newfile" defines the file name.
Name OldName As newName 'changes the file name.
OldName = "c: / mydir / oldfile": newname = "c: / YourDir / newfile"
Name OldName As NewName 'changes the file name and moves the file.
This example shows the current default file path.
Msgbox "The Current Default File Path IS" & _
Application.defaultFilePath
This example setting replaces the startup folder.
Application.AltStartuppath = "C: / Excel / Macros"
FolderExists method
If the specified folder exists returns True, there is no false to return.
grammar
Object.foldeRexists (Folderspec)
This example enables editing in the cell.
Application.editDirectlyNCell = true
VBA Getting Started Http://www.cpearson.com/excel/topic.htm
Advanced Office 2000 Password Recovery Cracked the program of VBA
I have two books when I learn VBA! "Excle2000VBA Development Example Guide" Jingchen Studio
"EXCLE2002 function application secret" China Railway Publishing House
Program description:
Several methods of entering data in cells:
Public Sub Writes ()
1 - 2 Method, the simplest in "[]" inputs the cell name.
1 [A1] = 100 'Input 100 in the A1 cell.
2 [A2: A4] = 10 'In A2: A4 cell input 10.
3 - 4 methods, enter the cell name in Range (")," ".
3 RANGE ("B1") = 200 'Input 200 in the B1 cell.
4 RANGE ("C1: C3") = 300 'At C1: C3 cell input 300.
5 - 6 Method, using Cells (Row, Column), ROW is the number of units of cells, and Column is the number of cells.
5 Cells (1, 4) = 400 'In the D1 cell input 400.
6 RANGE (Cells (1, 5), Cells (5, 5)) = 50 'At E1: E 5 Cell Enter 50.
End Sub
You click any cell, press the Selection button, then the selected cell will be entered "Test".
Public subsection1 ()
Selection.Value = "Test" 'Enter text "test" in any cell you click.
End Sub
Vbalesson2 program description:
Several methods of how to read other worksheets, read this worksheet: Add the worksheet name Sheet4 before being read.
Public Sub Writes ()
1-- 2 Method, the simplest in the read "[]" plus the read worksheet name Sheet4.
1 [A1] = Sheet4. [A1] 'The data of the Sheet4 A1 cell reads the A1 cell.
2 [A2: A4] = SHEET4. [B1] '' Put the Shee4 works table cell B1 data, reads to the A2: A4 cell.
3 - 4 Method, before the Worksheet Range ("") of the work table, adds the name Sheet4.
3 Range ("B1") = Sheet4.Range ("B1") '' Put the Shee4 works table cell B1 data, reads the B1 cell.
4 RANGE ("C1: C3") = Sheet4.Range ("C1") 'Putting the Shee4 works table cell C1 data, reads C1: C3 cells.
5 - 6 Method, in the read worksheet Cells (Row, Column), Cells before adding the read worksheet name Sheet4.
5 Cells (1, 4) = Sheet4.cells (1, 4) 'Put the Shee4 works table cell D1 data, read the D1 cell.
6 Range (Cells (1, 5), Cells (5, 5)) = Sheet4.cells (1, 5) 'Put the Shee4 works table cell E1 data, reads the E1: E 5 cell.
End Sub
You click any cell, press the Selection button, then the selected cell will be entered by the SHEE4 workfinder F1 data.
Public subsection1 ()
Selection.Value = Sheet4. [F1] 'Putting the Shee4 works table cell F1 data, reads any cells you click.
End Sub
Vbalesson3 program description:
How to use Worksheet_SelectionChange to enter data. Private subworksheet_selectionchange (Byval Target As Range)
Target = 100
End Sub
Target refers to the cell selected by your mouse, the parameters of the Worksheet_SelectionChange () event.
Can be one or a few cells.
Range is an Excel unique variable form, scope.
Target as rang is set to the Range variable form with this parameter of Target.
Target = 100 is the cell input number 100 in the cell.
Vbalesson4 program description:
How to use Worksheet_SelectionChange to enter data in qualified cells.
Private subworksheet_selectionchange (Byval Target As Range)
If Target.Row> = 2 and target.column = 2 THEN
Target = 100
END IF
End Sub
If ... then ... end if this is our logical judgment statement.
Target.row> = 2, refers to the row of the unit selected units greater than or equal to 2.
Target.column = 2, refers to the column of the selected cell, equal to 2.
If Target.Row> = 2 and target.column = 2 Then refers to only in Target.Row> = 2 and Target.column = 2.
Just (target.row> = 2) True and (target.column = 2), the following program target = 100 is performed.
That is, when the second line of the B column and the following rows are selected, it will be input 100, and other cells are not input.
Vbalesson5 program description:
Compare Worksheet_SELECTIONCHANGE () and the method of using the button commandbutton1_click () to execute the program different from the writing method.
Worksheet_selectionchange () event
Private subworksheet_selectionchange (Byval Target As Range)
If Target.Row> = 2 and target.column = 2 THEN
Target = 100
END IF
End Sub
Button CommandButton1_click ()
Private sub fascistbutton1_click ()
If Activecell.Row> = 2 And Activecell.column> = 3 THEN
ActiveCell = 100
END IF
End Sub
The two most ways to perform methods is that Worksheet_SelectionChange () is automatic, you don't have to understand how he finished working.
Button commandbutton1_click () is artificial, more procedures more than SelectionChange (), just press that button, the program will execute.
SelectionChange () has a parameter TARGET available; CommandButton1_click () is not.
So we have to use the ActiveCell's internal function to replace Target, Activecell and Target, you can only specify a cell. It is to select multiple cells and only the uppermost cell will add data; replace ActiveCell with Selection, usage is the same as Target.
Vbalesson 6 Program Description:
Complete IF ... Then ┅ END logic judgment.
Private subworksheet_selectionchange (Byval Target As Range)
If Target.Row> = 2 and target.column = 2 THEN
Target = 200
Elseif target.row> = 2 and target.column = 3 THEN
Target = 300
Elseif target.row> = 2 and target.column = 2 THEN
Target = 400
Else
Target = 500
END IF
End Sub
This is a complete IF logic judgment, meaning that if the judgment condition after IF is established, the second procedure is executed, otherwise, if the judgment condition after Elseif is established, the fourth program is executed, otherwise If another elseif is established, the sixth procedure is executed.
Else means that if the above conditions are not established, the Article 8 will be implemented.
His execution method is if the condition of IF is established, the logical judgment of other elseif and else is not performed. If the IF is not established, the elseif or ELSE logic judgment will be performed. The condition after the second elseif is the same as the conditions after IF, so this determination behind Target = 400 will be a program that will never be executed.
Vbalesson 7 Program Description: Why should we use variables.
Private subworksheet_selectionchange (Byval Target As Range)
DIM I, J AS Integer
DIM K As Range
I = target.row
J = target.column
Set K = Target
IF i> = 2 and j = 2 THEN
K = 200
Elseif I> = 2 and j = 3 THEN
K = 300
Elseif I> = 2 and j = 4 THEN
K = 400
Else
K = 500
END IF
End Sub
Compared with Vbalesson 6, the program is not clear, and it is more annoying to write the program with Target.Row, target.column and target. If you feel that you can simplify your program.
You have to declare the variable before using the variable. The method of declaring the variable is to write the form "Integer" on the back of the variable "I" AS after "DIM".
DIM I, J AS Integer is the declaration I and J as an integer variable, which is the two variables I and J, and then add a ",", ",", "
DIM K As Range is the declaration of K as a range of data, and Range is an Excel unique data form.
i = target.row is the number of rows of the current cell to the variable I. j = target.column is the number of columns of the current cell to the variable j.
Set k = target is the current cell, specifying the variable k.
With simple variables like I and J, you may still remember that i or J represents. The program is written, you may forget i or J represents. So the best way is to replace I and J for the variables such as IROW or ICOL with more meaningless code.
Vbalesson 8 Program Description: I realized the Worksheet_change () event.
Private subworksheet_change (byval Target As Range)
DIM IROW, ICOL AS INTEGER
Irow = target.row
Icol = target.column
If irow> = 2 and iCol = 2 and target <> "" "" "
Application.enableevents = false
Cells (IROW, ICOL 1) = Cells (IROW, ICOL) * 2
Application.enableevens = true
Elseif Irow> = 2 and ICol = 2 and target = "" "
Cells (IROW, ICOL 1) = ""
Else
Cells (IROW, ICOL 1) = ""
END IF
End Sub
The first few tutorials have been used in Worksheet_SelectionChange events. If you have to experience how he is, let's go.
This tutorial is to let you experience the Worksheet_chang () event. Because these two events are very useful in VBA, we must understand.
Simply put, the former is your mouse to move to that cell, triggering the execution of that event. The latter is to wait until you choose the cell, and the number has changed to trigger the execution of the event. The timing of the two executed one after another.
TARGET <> "" It is the program that represents the current cell if there is a few , will execute the following three rows.
Cells (IROW, ICOL 1) = Cells (IROW, ICOL) * 2, when you enter the number of inputs in the B bar, the C-column will receive twice the B column twice.
Target = "" is the procedure of the following line if there is no number of current cells, if there is no number of cells.
Cells (IROW, ICOL 1) = "", is the number of c-columns to clear the space.
Application.enableEvents = false and Application.enableEvents = True, this is a dual program that you want to write the program after performing other programs. Its purpose is in inhibiting event chain. Simply put, the event triggered in the B field, is not willing to trigger another Worksheet_change () event in other cells.
Vbalesson 9 Program Description: I realized the Worksheet_Change () event chain reaction.
Private subworksheet_change (byval Target As Range)
DIM IROW AS INTEGER
Irow = target.row
Application.enableEvents = falsecells (irow, 3) = cells (irow, 3) Cells (IROW, 2)
Application.enableevens = true
End Sub
Private subworksheet_change (byval Target As Range)
DIM IROW AS INTEGER
Irow = target.row
'Application.enableEvents = FALSE
Cells (irow, 3) = cells (irow, 3) Cells (irow, 2)
'Application.enableEvents = TRUE
End Sub
The purpose of this program is to rendering the new number of B2 inputs to C2 when the B2 is entered.
There are of course no problem with the application.enableevents = false program.
Now you are adding "'" before application.enableevents = false and Application.enableEvents = True.
Plus "'" before the program is to make the text after "'" become a description text, the program is executed, and the text will be skipped, and the contents of the description text are not performed.
After the program is added to the "'" symbol, the text will become green.
When executing the second program, you will find that C2 does not press the result of what you ask.
This is the so-called event chain reaction.
How can this macro write!
I want to run a macro, I can fill in a formula on the current worksheet B3; the result of this formula is all work
The B4 cell on the table. How can this macro write. Thank you!
SUB GG ()
Dim sh as worksheet, shName $
For Each Shin Worksheets
Shname = sh.name
Activesheet.Range ("b3"). Value = activesheet.range ("b3"). Value Worksheets (shName) .Range ("b4")
NEXT
End Sub
How to create a new work table named "Table" in VBA
Programming via VBA, it is easy to add new worksheets, but the name of the new table does not know how to control, for newly created worksheets, because its name is not a specific, so it is not good to use the new table created. I don't know why everyone is seen. . . .
Sheets.add
Activeesheet.name = "table"
Ask: How to use the VBA search table 1 in column A column A column in the same line and copy the latter to Table 1 Retrieve in Table 1, thank you !! !!
To yxptwq: Use this program to see.
SUB COPY1 ()
DIM Row_DN1, Row_DNN, I, J, N AS INTEGER
Row_dn1 = Sheet1.Range ("A65536"). End (xlup) .row
K = 1: n = 1
For Each Wsheet in ActiveWorkbook.worksheets
With wsheet
IF .Name <> "sheet1" THEN
Row_dnn = .range ("a65536"). End (xlup) .row
For i = 2 to row_dn1
For j = 2 to row_dnn
If .cells (j, 1) = sheet1.cells (i, 1) Then.Rows (J & ":" & j) .copy destination: = sheet1.rows (row_dn1 n & ":" & row_dn1 n)
n = n 1
END IF
NEXT J
Next I
END IF
End with
Next wsheet
End Sub
If you want to enter your password with the VBA program to use the following code
Sub EnterneWPW ()
'Program Description: Enter vbaproject password with SendKey
'Precautions: Performing this translation requires in Excel window, not in VBE window
Application.sendKeys "% {f11}", True 'Alt F11 Switch to VBA Window
Application.sendKeys "% T", TRUE 'Alt T Tool (Traditional Chinese is (T))
Application.sendKeys "E", True 'Tool (T) -vbProject Attribute (e)
Application.sendKeys "^ {tab}", True 'Tab key (switched to the Page2 Protection page)
Application.sendKeys "{ }", True 'Choosing the Checkbox block (lock outline for inspection)
'({ } Selection, {-} Cancel)
Application.sendKeys "{tab}", TRUE 'TAB button (jump to the first input password textbox
mypw = "chijanzen" 'assumes password Chijanzen
Application.sendKeys mypw, true 'Enter a password
Application.sendKeys "{tab}", True 'Tab key (jump to the second input password textbox
Application.sendKeys mypw, true 'Enter a password
Application.sendKeys "{enter}", true 'Press the OK button (preset value)
Application.sendKeys "% {f11}", True 'Returns Excel Window
End Sub
Bubbling sorting method:
The sputum sorting method is "bubble sort" is because the value is smaller or lighter element floats to the top of the number of continued sorting.
Sub macro1 ()
DIM I as integer
DIM J AS INTEGER
Dim t as integer
Static Number (1 to 10) AS INTEGER
FOR i = 1 to 10
Number (i) = inputbox "Enter the number of sorted:"
Next I
For i = 10to 2 Step -1
For j = 1 to i - 1
'Location exchange
IF number (j)> Number (j 1) THEN
T = Number (j 1)
Number (J 1) = Number (j)
Number (j) = t
END IF
NEXT J
Next I
For i = 1 to 20
Print Number (i)
Next I
End Sub
First define an array: By recurring 10 integers, then use a two-turn test to test whether the previous number is greater than the last number. If it is larger than the two numbers of subscribers, the two counts are swapped in the group, and the exchange passes through a variable. I first solve this problem with a traditional method. After comparing, it is a simple and efficient sorting method.
- "Quick Sort", specific algorithm can refer to the data structure and other related books. Sort all data, then
And the same data, the merging procedure is simpler, I started this method, but later found for these
Data, first merger is faster because there is a large amount of data. The merge is "marker"
The method is as follows: (Set data has been stored in the SDATA () array, and the result saves the queryp () array.
Amount is the number of data)
'Set the same element 0
For i = 1 to Amount
IF SDATA (i) <> 0 THEN
For J = i 1 to Amount
IF SDATA (I) = SDATA (j) Then SDATA (j) = 0
NEXT J
END IF
Next I
'Delete the same element
Queryp (1) = SDATA (1)
K = 1
For i = 2 to Amount
IF not (SDATA (I) = 0) THEN
K = K 1
Queryp (k) = SDATA (i)
END IF
Next I
Kmax = k
Redim Preserve Queryp (kmax)
Although this makes the computing speed, it is still necessary to carry out a lot of loop operation, accounting for most of the program.
Division operation time. So I have been looking for a more efficient algorithm.
Kung Fu pays off, after careful analysis of the characteristics of the data, after a variety of programs, I finally found a
A comparable successful algorithm, the original operation of 3 to 4 seconds is shortened to only 0.1 to 0.2 seconds.
The data I have encountered has the following characteristics: 1 There are many data, the maximum, the minimum difference is less than 3,
3 is a positive number with two decimals.
For data characteristics, I adopted the following algorithm:
For data characteristics, I adopted the following algorithm:
step:
1. Use a loop to find the maximum and minimum portions of the score. The largest and minimum number of decimal parts
Turn with 100 to an integer.
2. Define a two-dimensional array, the subscript range is the minimum value to the maximum of integers and fractional parts, respectively.
3. Use a loop to fill all source data into the two-dimensional array of proven, fill in the rules is, source data
The integers and fractions correspond to two subscripts of the two-dimensional array. For example, "13.51" fills in "a (13,51)"
in.
4. Finally, read the non-zero data in the two-dimensional array to the two-dimensional array to obtain a small to large or from large to a small arrangement.
Data and do not contain duplicate data.
The programs written in VB are as follows:
'**** intensive data processing ****
DIM I As Long, J AS Long, K As Long, Kmax As Long
DIM Queryp () AS Single
Redim Queryp (Amount)
DIM INTEGERPART AS INTEGER, DECIMALPART AS INTEGER
DIM IPMAX AS INTEGER, IPMIN AS INTEGER
DIM DPMAX AS INTEGER, DPMIN AS INTEGERDIM DIFFDATAARRAY ()
'Read data
Readdata
IPMAX = 0: ipmin = 1000
DPMAX = 0: DPMIN = 99
For i = 1 to Amount
'Find the largest, minimum of the integer and the decimal part
IntegerPart = INT (SDATA (I))
DecimalPart = (SDATA (I) - IntegerPart * 100
IF integerpart> ipmax then
IPMax = INTEGERPART
Elseif integerpart Ipmin = INTEGERPART END IF IF DeciMalpart> DPMAX THEN DPMAX = Decimalpart Elseif Decimalpart DPMIN = DECIMALPART END IF Next I Redim DiffDataArray (IPMIN to IPMAX, DPMIN to DPMAX) 'Fill in data For i = 1 to Amount IntegerPart = INT (SDATA (I)) DecimalPart = (SDATA (I) - IntegerPart * 100 DiffDataArray (IntegerPart, Decimalpart) = SDATA (i) Next I Next I 'Extract data K = 0 For i = ipmax to ipmin STEP -1 For J = DPMAX to DPMIN STEP -1 IF DiffDataArray (i, j) <> 0 THEN K = K 1 Queryp (k) = DiffDataArray (i, j) END IF NEXT J Next I Kmax = k Redim Preserve Queryp (kmax) This method is most effective for this "intensive" data encountered, but if you encounter a "sparse" number According to the largest, the minimum, the minimum is much different, and even tens of data, there is no advantage, and it will take up Larger memory. After improvement, I got the efficient algorithm for processing sparse data. Efficient prerequisites are also the source data There is a large amount of data. The idea is to add a single-dimensional array on the basis of the former approach to save an integer. Some data, the insert method is sorted by the insert method during storage. Because there are a lot of repetitive data, you want to sort The volume is relatively small. When reading data from a two-dimensional array, use a single-dimensional array to enter the first under the two-dimensional array. Sign, specific code: '*** Sparse data processing **** DIM I As Long, J AS Long, K As Long, Kmax As Long DIM Queryp () AS Single Redim Queryp (Amount) DIM INTEGERPART AS INTEGER, DECIMALPART AS INTEGER DIM IPMAX AS INTEGER, IPMIN AS INTEGER DIM DPMAX AS INTEGER, DPMIN AS INTEGER DIM iPARRAY () AS Integer, iPaamount AS IntederRedim iParray (Amount) DIM DIFFDATAARRAY () 'Read data Readdata IPMAX = 0: ipmin = 1000 DPMAX = 0: DPMIN = 99 IpAAMOUNT = 0 For i = 1 to Amount 'Get the maximum minimum number of integers and fractional parts IntegerPart = INT (SDATA (I)) DecimalPart = (SDATA (I) - IntegerPart * 100 IF integerpart> ipmax then IPMax = INTEGERPART Elseif integerpart Ipmin = INTEGERPART Ipmin = INTEGERPART END IF IF DeciMalpart> DPMAX THEN DPMAX = Decimalpart Elseif Decimalpart DPMIN = DECIMALPART END IF 'Sort the integer part "iParray ()" insert (from large to small) For j = 1 to ipaamount IPARRAY (J) THEN Ipaamount = ipaamount 1 Fork = ipaamount to j 1 step -1 IParray (k) = iParray (k - 1) Next K IParray (j) = integerpart EXIT for Elseif integerpart = iParray (j) THEN EXIT for END IF NEXT J IF j> iPaamount Then Ipaamount = ipaamount 1 IParray (ipaamount) = integerpart END IF Next I Redim DiffDataArray (IPMIN to IPMAX, DPMIN to DPMAX) 'Fill in data For i = 1 to Amount IntegerPart = INT (SDATA (I)) DecimalPart = (SDATA (I) - IntegerPart * 100 DiffDataArray (IntegerPart, Decimalpart) = SDATA (i) Next I 'Extract data K = 0 For i = 1 to ipaamount For J = DPMAX to DPMIN STEP -1 IF DiffDataArray (iParray (i), j) <> 0 THEN K = K 1 Queryp (k) = DiffDataArray (iParray (I), j) END IF NEXT J Next I Kmax = k Redim Preserve Queryp (kmax) k Redim Preserve Queryp (kmax) Which algorithm is specifically used, it depends on the nature of the data. The following is some of the measured data, for reference only . If you have a better way, don't forget to share with friends. Automatically hide no data in the form Table 1 is a data source, often changing; Table 2 Table 2 Reference The cells with data in Table 1 (using the dynamic address have been implemented.) Due to the change in Table 1, the size of Table 2 changes. Question: How to implement the row (formula) without data in Table 2 automatically hide? Thank you for your enlightening! SUB ABC () FOR i = 1 to 300 IF Cells (i, 1) .value = "" "" Then Rows (i) .hidden = true Next I End Sub What you write can solve the hidden problem, but if I execute it, add data in Table 1, Table 2 does not automatically display the data. How to modify? Set this macro to automatic operation (when the file is opened) SUB ABC () FOR i = 1 to 300 IF Cells (i, 1) .value <> "" "" The rows (i) .hidden = false Next I End Sub How to automatically merge the column with VBA? How to automatically merge the column with VBA? To hongjian: Sub mergetest () For i = 3 TO 30 Cells (I, 3) = Cells (i, 1) & chr (10) & Cells (i, 2) NEXT End Sub Report design and printing based on VB and Excel In the development of modern management information systems, the analysis, processing of data information is often involved. In the end, it is also necessary to provide a variety of reports of the statistics to the leadership decision-making reference, or Ministry exchange. Making reports in Visual Basic, usually using data environment designers (data Environment Designer (Data Report Designer), or Use a third-party product to complete. But for most users who are accustomed to the Excel report, it is used The report generated above is often unable to meet their requirements in terms of format and function. Since Excel has its own object library, you can reference in the Visual Basic project. By using OLE automation with Excel, you can create some unique reports, and then print Out. This implements the control of the Visual Basi application to Excel. This article will be directed to one State example, elaborate on report design and printing process based on VB and Excel. 1) Create an Excel object The Excel object model includes 128 different objects, from rectangular, text boxes, etc. Complicated objects such as perspective table, chart. Let's take a brief introduction to the most important, use Gest to five objects. (1) Application object The Application object is in the top floor of the Excel object hierarchy, indicating Excel himself. Operating the environment. (2) Workbook object The Workbook object is directly under the next layer of the Application object, indicating an Excel. Thinking files. (3) Worksheet object The Worksheet object is included in the Workbook object, indicating an Excel worksheet. (4) RANGE object The Range object is included in the Worksheet object, indicating one or more of the Excel worksheet. Cell. (5) Cells object The Cells object is included in the Worksheet object, indicating a cell in the Excel worksheet. If you want to launch an Excel, use the workbook and worksheet object, the following code Started Excel and created a new work meter: DIM ZSBEXCEL AS Excel.Application Set zsbexcel = new excel.Application Zsbexcel.visible = true To excel is invisible, you can make zsbexcel.visible = false zsbexcel.sheetsinnewwkebook = 1 Set zsbworkbook = zsbexcel.workbooks.add 2) Set cell and region value To set the value of each cell in a worksheet, you can use the Worksheet object Range property or Cells property. WITH ZSBEXCEL.Activeesheet .Cells (1, 2) .value = "100" .Cells (2, 2) .value = "200" .Cells (3, 2) .value = "= SUM (B1: B2)" .Range ("A3: A9") = "Chinese People's Liberation Army" End with To set fonts, borders, borders, borders, can take advantage of RANGE objects or Cells objects Border attribute and font properties: With objexcel.activeesheet.range ("A2: K9"). Borders' Border Settings .LINESTYLE = XLborderLinesTyleContinuous .Weight = xltin .COLORINDEX = 1 End with With objexcel.activesheet.range ("a3: k9"). Font 'font settings .Size = 14 .Bold = true .Italic = TRUE .COLORINDEX = 3 End with You can create a variety of complexs by an in-depth understanding of the various settings of the Excel cells and regional values. Miscellaneous, beautiful, meet the needs of the report, and its own characteristics. 3) Preview and print After generating the required worksheet, you can make a preview, print instructions. zsbexcel.activeesheet.pagesetup.orientation = xlportrait ' Set the print direction Zsbexcel.activeesheet.pagesetup.papersize = xlpapera4 ' Set up the printing paper Zsbexcel.caption = "Print Preview" Set the preview window title Zsbexcel.activeesheet.PrintPreview 'print preview ZSBEXCEL.Activeesheet.Printout 'printout By printing the direction, printing the size of the paper size, constantly previewing until satisfaction, Finally print output. In order to exit the application, Excel does not prompt whether the user saves the modified file, Use the following statement: zsbexcel.displayalerts = false Zsbexcel.quit 'Exits Excel Zsbexcel.displayalerts = true This design of the report print is achieved through the Excel program. For users Say, I can't see the specific process at all, just seeing a beautiful report is easily printed. 4) Specific example A specific example is given below, it is in Window98, Visual Basic 6.0, Try to pass the Microsoft Office97 environment. Start a new Standard EXE project in VB, "reference" in the Project menu Reference Excel Object Library under the option; then add a command button in the Form Cmdexcel; Finally, enter the following code in the form: DIM ZSBEXCEL AS Excel.Application Private subdexcel_click () Set zsbexcel = new excel.Application Zsbexcel.visible = true zsbexcel.sheetsinnewwkebook = 1 Set zsbworkbook = zsbexcel.workbooks.add With zsbexcel.activesheet.range ("A2: C9"). Borders' Border Settings .LINESTYLE = XLborderLinesTyleContinuous .Weight = xltin .COLORINDEX = 1 End with WITH ZSBEXCEL.Activeesheet.Range ("A3: C9"). Font 'font settings .Size = 14 .Bold = true .Italic = TRUE .COLORINDEX = 3 End with Zsbexcel.activeesheet.Rows.horizontalaLAlignment = XlvalignCenter 'level zsbexcel.activeesheet.Rows.VerticalAlignment = XlvalignCenter 'vertical WITH ZSBEXCEL.Activeesheet .Cells (1, 2) .value = "100" .Cells (2, 2) .value = "200" .Cells (3, 2) .value = "= SUM (B1: B2)" .Cells (1, 3) .value = "Chinese People's Liberation Army" .Range ("A3: A9") = "50" End with zsbexcel.activeesheet.pagesetup.orientation = xlportrait ' XLLANDSCAPE zsbexcel.activeesheet.pagesetup.papersize = xlpapera4 zsbexcel.activeesheet.printout zsbexcel.displayalerts = false Zsbexcel.quit Zsbexcel.displayalerts = true Set zsbexcel = Nothing Improve the efficiency of VBA in Excel Method 1: Try to use VBA's original properties, methods and worksheet functions Due to the exception of Excel objects, the properties, methods, and events of the object may not know all for beginners, this produces the programmer to write the properties of the Excel object, the same functionality The VBA code segment, and the operational efficiency of these code segments is obviously equal to the properties of the Excel object, and the speed of the task is very different. For example, with the property of the property CurrentRegion, the RANGE object is returned, which represents the current area. (The current area refers to the boundaries of any blank row and blank columns). The same function is required to do dozens of lines. Therefore, the properties and methods of Excel objects should be understood as much as possible before programming. Take advantage of the Worksheet function is an extremely effective way to improve program running speeds. Examples of average salary: for Each C in Worksheet (1). Range ("A1: A1000") TotalValue = TotalValue C.Value NEXT AverageValue = Totalue / Worksheet (1) .range ("A1: A1000"). Rows.count The following code programs are much more than the above example: AverageValue = Application.WorksheetFunction.Average (1) .range ("A1: A1000")) Other functions such as count, counta, countif, match, lookup, etc. can replace the VBA program code of the same function, improve the running speed of the program. Method 2: Try to minimize the use of object reference, especially in the cycle The properties of each Excel object require one or more calls to the OLE interface, which is required to reduce the operation of the VBA code to speed up the VBA code. E.g 1. Use the WITH statement. Workbooks (1) .sheets (1). Range ("A1: A1000"). Font.name = "pay" Workbooks (1). Sheets (1). Range ("A1: A1000"). Font.FontStyle = "bold" ... The following statement is more fast than the above WITH WORKBOOKS (1) .sheets (1) .RANGE ("A1: A1000"). Font .Name = "pay" .Fontstyle = "bold" ... End with 2. Use object variables. If you find an object reference to be used multiple times, you can set this object to object variables with SET to reduce access to objects. Such as: Workbooks (1). Sheets (1) .Range ("a1"). Value = 100 Workbooks (1). Sheets (1) .range ("a2"). Value = 200 The following code is faster than the above: Set mysheet = workbooks (1) .sheets (1) MySheet.Range ("a1"). Value = 100 MySheet.Range ("a2"). Value = 200 3. Try to minimize access to objects in the loop. Fork = 1 to 1000 Sheets ("sheet1"). SELECT Cells (k, 1) .Value = cells (1, 1) .value Next K The following code is faster than the above: Set Thevalue = Cells (1, 1) .value Sheets ("sheet1"). SELECT Fork = 1 to 1000 Cells (k, 1) .value = thevalue Next K Method 3: Reduce the activation and selection of objects If you learn VBA by recording macro, your VBA program must be full of object activation and selection, such as Workbooks (XXX) .ct, Sheets (XXX) .Select, Range (xxx) .select, but In fact, most of these operations are not required. For example, Sheets ("Sheet3"). SELECT Range ("a1"). Value = 100 Range ("a2"). Value = 200 Can be changed to: With sheets ("sheet3") .Range ("a1"). Value = 100 .RANGE ("A2"). Value = 200 End with Method 4: Close the screen update If your VBA program is done in front, the shutdown screen update is the most effective way to improve the speed of the VBA program, and the runtime is 2/3. Turn off the screen update: Application.screenupdate = false Please don't forget that the VBA program is running back: Application.screenupdate = true The above is several ways to improve the efficiency of VBA operational efficiency This example repeats the most recent user interface command. This example must be placed in the first line of the macro. Application.repeat In the following example, the variable counter replaces the line number. This process will be circulated in the cell area C1: C20, and all the numbers that are less than 0.01 are set to 0 (zero). Sub roundTozero1 () For counter = 1 to 20 Set curcell = Worksheets ("sheet1"). Cells (Counter, 3) IF ABS (Curcell.Value) <0.01 Ten Curcell.Value = 0 Next Counter End Sub The process is circulated in the cell zone A1: D10, which is set to 0 (zero) of the numbers less than 0.01. Sub roundTozero2 () For Each C in Worksheets ("Sheet1"). Range ("A1: D10"). Cells IF ABS (C.Value) <0.01 Then C.Value = 0 NEXT End Sub When the following process is running on the worksheet, it will cycle in the area around the active cell, and all the numbers that are less than 0.01 are set to 0 (zero). Sub roundTozero3 () For Each C in ActiveCell.currentregion.Cells IF ABS (C.Value) <0.01 Then C.Value = 0 NEXT End Sub The following process writes data in the air line of work SUB input () X = 3 'starts from line 3 Do While Not (ISempty (Cells (x, 2) .value) "Judging the last line of the second column (ie the upper line of the space) X = x 1 'in the last line plus a row Loop 'The following is written Cells (X, 1) = Sheets ("Sheet1"). Cells (1, 3) Cells (X, 2) = Sheets ("Sheet1"). Cells (2, 3) Cells (X, 3) = Sheets ("Sheet1"). Cells (3, 3) Cells (X, 4) = Sheets ("Sheet1"). Cells (4, 3) Sheets ("Sheet1"). SELECT Cells (2, 3) = Cells (2, 3) 1 'Automatically join the serial number every word Sheets ("Sheet2"). SELECT End Sub SUB daily () 'Enter the incidence Macro '30 recorded Hong 2002-12-18 Application.screenupdating = false 'Close screen display If Application.inputbox ("Please enter your password:") = 1234 Then 'This line is set by 3-5 rows DIM MSG, Style, Title, X, MyString 'Settings Variables Msg = "!!! Cannot recover after checkout?" 'Define information. Style = Vbyesno Vbcritical VBDefaultButton2 'definition button. Title = "This day checkout!" 'Defines the title. X = MSGBOX (MSG, Style, Title) IF x = vbyes then 'user presses "Yes". Close Call backup Call recombines all tables Sheets ("Daily Report") .select Activeesheet.unprotect Selection.autofilter Field: = 1 'all display Sheets ("balance sheet") .Select Activeesheet.unprotect Selection.autofilter Field: = 1 'all display Range ("E6: G183, I6: K183"). COPY Sheets ("Daily Report") .select Range ("n6"). SELECT Selection.Pastespecial Paste: = xlpastevaluesandNumberFormats, Operation: = _ Xlnone, Skipblanks: = false, transpose: = false Activesheet.Protect DrawingObjects: = true, contents: = true, scenarios: = true_ Allowsorting: = true, allowfiltering: = true [A2] .copy Sheets ("Bank Account") .Select Range ("f2"). SELECT Selection.Pastespecial Paste: = xlpastevalues, Operation: = xlnone, Skipblanks_ : = False, Transpose: = FALSE Activesheet.Protect DrawingObjects: = true, contents: = true, scenarios: = true_ Allowsorting: = true, allowfiltering: = true Application.cutcopyMode = false END IF Else: msgbox "Password error, will be exited!" 'This line is set with the second line together End ifApplication.screenupdating = true 'Open the screen display End Sub Sub Month End Checkout () Application.screenupdating = false If Application.inputbox ("Please enter your password:") = 1234 Then 'This line is set by 3-5 rows 'The following three behaviors message box DIM MSG, Style, Title, X, MyString Msg = "!!! Cannot recover after checkout?" 'Define information. Style = Vbyesno Vbcritical VBDefaultButton2 'definition button. Title = "Month end checkout!" 'Defines the title. X = MSGBOX (MSG, Style, Title) IF x = vbyes then 'user presses "Yes". Close Call backup Call recombines all tables Sheets ("balance sheet") .Select [L6: l183] .copy Sheets ("Daily Report") .select Range ("m6"). SELECT Selection.Pastespecial Paste: = xlpastevalues, Operation: = xlnone, Skipblanks_ : = False, Transpose: = FALSE Range ("n6"). SELECT [N6: S183] = "" " Sheets ("Bank Account") .Select [A7: U3000] = "" " [K5] .copy Range ("k6"). SELECT Selection.Pastespecial Paste: = xlpastevalues, Operation: = xlnone, Skipblanks_ : = False, Transpose: = FALSE Application.cutcopyMode = false Range ("a1"). SELECT END IF Else: msgbox "Password error, will be exited!" 'This line is set with the second line together END IF Application.screenupdating = true End Sub SUB Time Report () Application.screenupdating = false Sheets ("Daily Report") .select Call recombines all tables Activeesheet.unprotect password: = 641112 'Undo worksheet protection and cancel password Selection.autofilter Field: = 1, criteria1: = "1.00" 'Automatic screening 'The following 10-line pop-up window input print information DIM MyPrintnum as in integer DIM MyPROMPT, MyTITLE AS STRING MyPROMPT = "Please enter the number of copies to print" myTitle = "Print Selection Range" Myprintnum = Application.inputBox (MyPrompt, Mytitle, 4,,,,,,, 1) IF MyPrintnum <> 0 THEN 'Application.ActivePrinter = "// zdserver2 / HP LaserJet 5000 PCL 6 in Ne00:"' specify a printer ActiveWindow.SelectedSheets.PrintOut Copies: = myPrintNum, Collate: = True 'set print information, wherein Copies: = myPrint of prints Else Msgbox "Please enter the number of copies to print" END IF Activesheet.ShowAllData 'all display Activeesheet.protect password: = 641112 'Protect worksheets and set passwords Sheets ("Cover") .Select Application.screenupdating = true End Sub SUB print balance () Application.screenupdating = false Sheets ("balance sheet") .Select Call recombines all tables Activeesheet.unprotect password: = 641112 'Undo worksheet protection and cancel password ActiveWindow.ScrollColumn = 10 Selection.autofilter Field: = 1, criteria1: = "<>" 'The following 10-line pop-up window input print information DIM MyPrintnum as in integer DIM MyPROMPT, MyTITLE AS STRING MyPROMPT = "Please enter the number of copies to print" myTitle = "Print Selection Range" Myprintnum = Application.inputBox (MyPrompt, Mytitle, 4,,,,,,, 1) IF MyPrintnum <> 0 THEN 'Application.ActivePrinter = "// ZDServer2 / HP LaserJet 5000 PCL 6 in Ne00:"' Specify printers ActiveWindow.SelectedSheets.PrintOut Copies: = myprintnum, collate: = true 'Sets print information, where copies: = myprint is the number of printnuas Else Msgbox "Please enter the number of copies to print" END IF Activesheet.ShowAllData 'all display Activeesheet.protect password: = 641112 'Protect worksheets and set passwords Sheets ("Cover") .Select Application.screenupdating = true End Sub SUB backup () Dim y 'variable declaration - to save the path and name of the worksheet [M1] = ActiveWorkbook.FullName 'cell m1 = The path and name of the current workbook Y = Cells (1, 14) 'y = value of cell N1, that is, the calculated path and name of the workbook must be saved. Worksheets ("Cover") .USEDRANGE.COLUMNS ("M: N"). Calculate 'calculates the specified area ActiveWorkbook.savecopyas y 'backup to the specified road? Y End Sub SUB Rules Activity Table () WITH APPLICATION .Calculation = xlmanual .Maxchange = 0.001nd with ActiveWorkbook.PrecisionAsDisplayed = true ActiveWindow.Displayzeros = true Activeesheet.calculate End Sub SUB recombssion specified table () Attribute rejoin specified table .vb_procdata.vb_invoke_func = "z / n14" Worksheets ("Bank Account") .calculate Worksheets ("Daily Report") .calculate End Sub Cell data change causes calculation activation process Private subworksheet_change (byval Target As Range) DIM IROW, ICOL AS INTEGER Irow = target.row 'variable row IROW Icol = target.column 'variable column ICOL If IROW> 6 and ICOL = 3 and cells (irow, 3)> = Cells (irow - 1, 3) Then '> greater than 6 lines, and third columns, when the Bank 3> 2 lines 3 columns Application.enableevents = false Cells (irow, 2) = Cells (Irow - 1, 2) 'This line 2 column = 2 columns Application.enableevens = true Elseif Irow> 6 and ICOL = 3 and cells (irow, 3) Application.enableevents = false Cells (irow, 2) = cells (irow - 1, 2) 1 'This line 2 column = upstream 2 column 1 Application.enableevens = true Elseif (ICOL = 3 or ICOL = 4 or iCol = 6 or iCol = 8 or iCol = 9 or iCol = 13) And irow> 6 Then 'and target <> " Application.enableevents = false Cells (Irow, 5) = "= Unit Name" Cells (irow, 7) = "= summary" Cells (irow, 11) = "= balance" Range (Cells (Irow, 14), Cells (IROW, 16)) = "= Pre-oriented revenue number NOP" Cells (irow, 17) = "= audit Q" Cells (irow, 18) = "= 对帐 U" Range (cells (irow, 19), cells (irow, 20)) = "= internal turnover xy" Cells (irow, 21) = "= political Z" Application.enableevens = true END IF End Sub 'Calculate the current worksheet path and the name of the name, can be used as a unit formula, or write a macro = Cell ("filename") 'Change the macro of the Excel interface title Private subworkbook_open () Application.caption = "Eat" End Sub 'Automatically brush the date / time of the new cell A1 Sub mytime () Range ("a1") = now () Application.ontime Now TimeValue ("00:00:01"), "MyTime" End Sub 'Save the current workbook as a file name with the content of the cell A1 SUB B () ActiveWorkbook.savecopyas Range ("A1") ".xls" End Sub 'Activate the macro of the form, this macro is written in a work table with the form Private submmandbutton1_click () 'Point data entry button control activation form Load Userform3 'Activation Form Userform3.startupposition = 3 'activation form Userform3.show 'activation form End Sub 'The following is a macro to run in the form, write into the form Public POS AS INTEGER 'Declaration Variable POS 'Comet godners determine the button statement Private sub fascistbutton1_click () Application.screenupdating = false 'This sentence and the last sentence are intended to display the implementation process of macros 'On Error Goto Errorhandle' 'Errorhandle:' 'If Err.Number = 13 TEN' 'Exit Sub' 'End if' Call WrittoWorksheet 'Performing Macro WriteToworksheet Userform3.hide 'Exit the form, continue the button less, exit button to execute this sentence Unload userform3 'exits the form, continue the button less, exit button to execute this sentence Call Batch Print '[here to receive the sequence 2] [L2] = "" "[end here] Sheets ("Print Information") .Select Application.screenupdating = true End Sub 'Exit button statement Private sub fascistbutton2_click () Userform3.hide UNLOAD Userform3 End Sub 'Write the data in the text box in the form into the unit of worksheet Private Sub WriteToWorksheet () Activeesheet.Range ("k2") = textbox1.value 'writes the text box in the K columns Activesheet.Range ("l2") = textbox2.value 'writes the text box in the L columns TextBox1.Value = "" "Clear text box content TextBox2.value = "" "Empty text box content Worksheets ("Print Information") .Range ("A2"). Value = 1 'Write the cell to the specified table Worksheets ("Print Information") .Range ("B3: E113"). Value = "" 'Clearing the cell data end SUB of the specified table 'The following is a macro printed according to conditions SUB print () 'department detail query and batch print Application.screenupdating = false 'Close screen update IF cells (1, 4) = "" and cells (1, 5) = "" "THEN 'printing conditions Cells (3, 13) = 1 and 'Application.ActivePrinter = "// ZDServer2 / HP LaserJet 5000 PCL 6 in Ne00:"' Specify printers ActiveWindow.SelectedSheets.PrintOut Copies: = 1, collate: = true 'Sets the print information of the default printer, where copies: = MyPrint is the number of printnuas Else Call print information 'is executed to time END IF Application.screenupdating = true 'Close screen update End Sub 'The following cycle process, also used for mass printing, the value of z = 1 to 5 (1 to 5), but the content of the cell Sub batch print () For z = cells (1, 11) to cells (1, 12) 'variable X is gradually incremented from the print start number K1 to the end number L1 The value of Cells (1, 13) = z 'm1 is equal to the variable X Next z End Sub 'The following is a macro that writes the print condition into a worksheet SUB print information () Application.screenupdating = false 'Close screen update DIM Y 'declaration variable Y = activesheet.name 'Decision Activity Worksheet Name Sheets ("Print Information") .Select X = 3 'starts from line 3 Do While Not (ISempty (Cells (X, 2) .value) "Judging the last line of the first column (ie the upper line of the space line) X = x 1 'in the last line plus a row Loop Cells (x, 2) = Cells (2, 1) Cells (x, 3) = Sheets (Y) .Cells (4, 3) Cells (2, 1) = Cells (2, 1) 1 Cells (x, 4) = Sheets (Y) .Cells (1, 4) Cells (x, 5) = Sheets (Y) .Cells (1, 5) [c1] = y Sheets (Y) .Select 'Returns the worksheet opened last time Application.screenupdating = true 'opens the screen update End Sub How to write files to the macro of the file name in a certain cell Suppose you want to save the value of the value in the A1 cell of Sheet1, apply the command: ActiveWorkbook.savecopyas Str (Range ("Sheet1! A1")) ".xls" In Excel, how to use the program to control a single cell is not editable? Thanks !!! Private subworkbook_open () ProtectspecialRange ("a1") End Sub Sub protectspecialrange (Rangeaddress as string) ON Error Resme nextwith Sheet1 .Cells.locked = false . Range (RangeAddress) .locked = TRUE .Protection.AlloweditRanges.add title: = "Region 1", Range: = range (RangeAddress) _ Password: = "pass" .Protect drawingObjects: = true, contents: = true, Scenarios: = TRUE End with End Sub Programming the worksheet, sometimes to determine the total number of worksheets, how to implement the VBA? X = 1 Do While Not (ISempty (""). Cells (x, 1) .value) X = x 1 loop What is the function of the SFF in VBA in Excele-Sum () -? Application.WorksheetFunction.Sum () Custom menu has three menu items, requiring manual order. To prevent misuse, make it as gray after the first menu item, how to write? Rowen It is more convenient to synchronize its enable property synchronization with a tool button. How to update a table? This is the case, for example, I already have an original form A, then someone inform me that I have an error, I must modify it, and give me a table B, Table B lists the parameters that must be modified (note B's column number The number of columns less than A, because other columns of A do not need to be modified). Now the problem is how to find the corresponding position in Table A according to the new value in Table B, and modify it. For example, the height and body weight of 10002 JOHN is listed in Table B, how to find 10002 corresponding position (height weight) in A, and modify it. It is recommended to copy Table B to the Sheet2 of Table A, and then perform the following macro SUB Change () DIM DD AS RANGE Sheets (2) .select LastCell = Range ("A65536"). End (xlup) .row For Each DD in Range (Cells (2, 1), Cells (Lastcell, 1)) IF dd = "" "the EXIT SUB FF = dd.value Set c = sheets (1) .columns (1) .find (ff, lookat: = xlwhole) IF not c is nothing then C.offset (0, 2) = DD.OFFSET (0, 2) C.offset (0, 3) = DD.OFFSET (0, 3) C.offset (0, 5) = DD.OFFSET (0, 4) END IF NEXT End Sub Custom menu Write the code that establishes and deletes the custom menu, writes in Workbook_Open and Workbook_beforeClosed events, respectively. You should use VBA, workbook code, Workbook-open () procedure, write in this process With Activeworkbook . Sheets ("Table 2"). Active End with VBA implementation inserting a row in a locked work table, and automatically copy the function of the specified column in the above line Option expedition Public const strpass = "123" 123 is a password Insert a line in the SUB line () Activeesheet.unprotect password: = strpassselection.copy Selection.Insert Shift: = XLDOWN Selection.Pastespecial Paste: = xlformats, Operation: = xlnone, skipblanks: =_ False, Transpose: = FALSE Application.cutcopyMode = false Activesheet.Protect Password: = strpass End Sub How to make the appearance when the XLS file does not appear: "XXX.xls file has been modified, is it possible to modify?" ? Manually save work can be performed before the worksheet is closed THISWORKBOOK.SAVE How to achieve dynamic time display? Sub mytime Range ("a1") = now () Application.ontime Now TimeValue ("00:00:01"), "MyTime" End Sub Use VBA to determine if the specified EXCEL file is opened? For Each W in Workbooks IF W.NAME <> xxx kil .......... END IF Next w How do VBA calls Excel's own functions? such as VLOOKUP? Application.WorksheetFunction.f (x) f (x) is the workgroup you want to use However, when using the internal function, the unit will be wrong, what should I do? Remove the cells you want to generate into a VBA recognized format (type). As "F7: F12" in Excel should be changed to "RANGE (" F7: F12 ")" and so on. How to close, save and exit Excel in VBA? Workbooks ("Your Workbook" .save. The following table illustrates references to some rows and columns using the ROWS and Column attributes. Reference meaning ROWS (1) first line All rows on the ROWS worksheet Column (1) first column Column ("a") first column All columns on the columns worksheet To process several rows or columns at the same time, create an object variable and use the Union method to combine multiple calls to the ROWS attribute or columns attribute. The following example set the first line, the third row, and the fifth line of the first line, the third row, and the fifth line in the active workbook to bold. Sub severalrows () Worksheets ("sheet1"). Activate DIM Myunion As Range Set myunion = union (rows (1), ROWS (3), ROWS (5)) Myunion.font.bold = true End Sub If you just say that only a few storage degene, use a simple method Range ("a1"). Formula = Application.evaluate ("= [Book2.xls] Sheet1! A1") or Range ("a1"). Formula = "= [BOOK2.XLS] Sheet1! A1" How to call the defined name in VBA I am inserting a name at A1: B100 :myrange I would like to select this range with VBA Range ("MyRange"). SELECT How to access the Excel file that has not been opened? Sub alternativeImport () DIM XLAPP as Excel.Application DIM WBSOURCE AS Excel.WorkbookSet XLAPP = New Excel.Application XLAPP.ENABLEVENTS = FALSE Set wbsource = xlapp.workbooks.open ("c: /test/book2.xls") Range ("A1: A10"). Value = WBSource.Sheets ("Sheet1"). Range ("A1: A10"). Value WBSource.Close False XLapp.quit End Sub How to make VBAPRJECT projects can not be viewed? (No password) Open Excel.xls with software tools such as WinHex, etc., in the end of the file, find ID = "{0000000000000}" (with engineering lock password), Or ID = "{xxxxxxx-xxxxxxxxxxxxxx}" (when there is no engineering lock password), modify any 1 bit, save, to achieve purposes. When viewing the project, "Project cannot be viewed" Tips. Note: Before modifying, be sure to back up the original file to prevent it How to use VBA to control the format of the report (left margin, paper size, print a few pages, etc.) Print a few pages: activeWindow.selectedsheets.printout from: = x, to: = Y Activeesheet.pagesetup.leftmargin = left margin Activeesheet.pagesetup..papersize = paper size How to automatically eliminate the virtual line box generated after using COPY copy? Application.cutcopyMode = false Replacing the EXCEL 97 menu bar is easy, just create a new menu bar, you will delete the EXCEL 97 menu bar. When you need to restore the menu bar of Excel 97, you can use it as long as you delete the newly created menu bar. There are only two command buttons for the custom menu of the system, one for returning to the system, and the other is used to exit the system (exitsys). Below is a macro in the module or an event control program. Sub ZapMenu () ON Error ResMe next Commandbars ("Insurance Query System"). Delete End Sub This is a macro used to delete a custom vegetable barbar. Statement on Error Resume Next ensures that it can be removed correctly whether it exists in the custom menu bar. SUB EXITSYS () ZapMenu ActiveWorkbook.close SaveChanges: = false End Sub This is the macro used to exit the system. It deletes a custom menu and shut down an active workbook (without prompting save modification). Sub returnMain () Worksheets ("Insurance Query System") .Select End Sub This macro is used to return to the main screen. It activates the "Insurance Query System" worksheet. SUB setMenu () Dim mybar as commistbar DIM MyButton As Commandbarbutton ZapMenu Set mybar = commandbars.add (name: = "Insurance Query System", _ Position: = msobartop, _ MenuBar: = true) Set mybutton = mybar.controls.add (msoControlbutton) MyButton.style = msobuttoncaption MyButton.caption = "Exit [& E]" MyButton.onAction = "EXITSYS" Set mybutton = mybar.controls.add (msoControlButton) MyButton.Style = msobuttoncaption MYBUTTON.CAPTION = "Return [& r]" MyButton.onAction = "ReturnMain" MyButton.visible = false MyBar.Protection = MSOBARNOMOVE MSOBARNOCUSTOMIZE myBar.visible = TRUE End Sub This macro contains five parts. The first part defines a pair of variables. The second part first runs the ZapMenu macro to ensure that the insurance query system menu bar does not exist and then creates it. The value of the parameter menubar is set to True, make sure this newly created command bar is a menu bar. The third part and the fourth section add two command buttons to the menu bar. And set the initial state of the returnMain command button to invisible. The last part protects this newly created menu bar, and the user cannot move or customize the new menu bar. Work form SUB SUM () 'indicates the total, the first A1: E20 is equal to the same cell of all tables Attribute sum.vb_procdata.vb_invoke_func = "z / n14" DIM X as worksheet For y = 1 to 20 For z = 1 to 5 For Each X in Worksheets Shname = x.name Activesheet.cells (Y, Z) .Value = activesheet.cells (y, z) .Value Worksheets (shName) .Cells (Y, Z) NEXT Next z Next y End Sub
