teach vba for excel

When you copy Sheet1 to Sheet3, you implement the following method:Worksheets ("Sheet1"). Copy after:=worksheets ("Sheet3") The syntax for using Worksheet.copy is as follows:An expression. Copy ( before, after)Before: inserting before a sheetAfter: inserting after a sheetNote: The above two parameters cannot be specified at the same time, and when none of the two parameters are specified, the sheet will be copied to a new workbook.Resources:Https://msdn.microsoft.com/zh-cn/

Range (Rows (3), rows (5)). Insert Shift:=xldown1) Insert a row at the current cell ; You can add a loop statement to insert multiple rowsRange ("A10"). SelectSelection.EntireRow.Insert, Copyorigin:=xlformatfromleftorabove2) Insert the same number of rows at the current selection row as the number of rows to select, and change the line number to insert in different places. Rows ("10:11"). Select Selection.insert Shift:=xldown, Copyorigin:=xlformatfromleftorabove3) Change range to Sheet1.c

Write the Excel VBA tool to connect and manipulate the MySQL database.System environment:Os:win7 64-bit English versionOffice 2010 32-bit English version1, VBA before the preparation of the connection to MySQLTools--->references. ----> ReferencesTick Microsoft ActiveX Data Objects 2.8 Librarys and Microsoft ActiveX Data Objects Recordset 2.8 Librarys2. Install My

Write to open and close multiple workbooks with Excel VBA, and the appropriate code is as follows: 1, open the specified workbook Dim wb As Workbook Set wb = "File path and filename" Workbooks.Open filename:= WB 2. Close all workbooks and save Workbooks.close 3, open a number of work-thin program code: Sub Openworkbooks () On Error Resume Next Dim Selectfiles as Variant ' Show open File dialog bo

Typical Excel VBA code application (EXCEL program) Application. enableevents = true/false' enable/disable all events Application. displayalerts = true/false' display/Close warning box prompt box Application. screenupdating = true/false' display/Close screen refresh Application. statusbar = "" 'displays text in the address bar, and uses the caption attribute i

Yesterday, colleagues had a need to know the month of the first sale of each item, and the month of the last sale. I want to use what Excel function to solve, but found a half-day did not find the right, and finally through the VBA to solve it.How to use:Excel Tools-Macro-visual Basic Editor right-click in the left column,Insert-ModuleThen enter:1 FunctionLast0 (ByValInt_row as Integer) as Integer2Last0 = -

Problem: As shown, group by Lon,lat, then transpose.Sub admin () Dim conn, xRs, xFd Set conn = CreateObject ("ADODB". Connection ") Conn. Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ "Extended properties= ' Excel 8.0;hdr=yes;imex=1 ';" _ " Data source= " thisworkbook.fullname Set xRs = CreateObject (" ADODB. RecordSet ") sSQL =" Transform Sum ([tas_t]) Select [lon], [lat] from [sheet1$a:d] Group by [LON], [

How to make an Excel file that is limited to open on a computer and other computers cannot open the Excel file. This has to be done with the help of VBA code. Just add the following code to the event that the workbook opens. Private Sub Workbook_Open () application.screenupdating = False On Error GoTo 100 Workbooks.Open Thisworkbook.path "/validation. XLS

In Excel, you create the hyperlink code in bulk (connect to sheet in the current document), and in column B in Sheet1, you create a series of hyperlinks that are the other sheet in this document, such as creating a macro under Sheet1 code as follows.SUB Macro 1 ()Dim Temp, Temp2Dim I, Jj = 1For i = 5 to 74temp = "' G" J "'! A1 "Temp2 = "G" JRange ("B" i). SelectActiveSheet.Hyperlinks.Add anchor:=selection, address:= "", Subaddress:=temp, texttodis

Today, I used the Excel VBA editor to modifyCodeWhen it is found that the input space will be automatically returned, and the input and quotation marks will also be automatically escaped, almost unable to write code normally. Follow these steps to solve the problem: 1. Find the developer menu in Excel. If the developer menu is not displayed, click File-options-

("Excel.Application") Dim Wkbook as Object ' represents ExcelWorkbook (that is, Excel workbook file. xls. xlsx) Dim Wksheet as Object ' represents Excel's work page ExcelApp.Application.EnableEvents = False ' suppress macros and other prompts to run Excelapp.applicati On. DisplayAlerts = False ExcelApp.Application.CutCopyMode = False Dim diclist, FileList, Cundic, I, FileName (), FilePath () Dim Excelpath as String set diclist = CreateObject ("Scrip

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 DimCurrentmaxdrawba

VBA Get EXCEL Number of rows and columns in a tableBeginners Excel Macro Children's shoes, always want to know the table contains data in the number of rows and columns, especially when the number of rows and the number of columns is uncertain. This avoids a lot of errors and can improve efficiency. But every time you use the Internet to find, always give a lot o

1 Option Explicit2 OptionBase13 4 SubFillsheet ()5 DimI as Long6 DimJ as Long7 DimCol as Long8 DimRow as Long9 DimArr () as LongTenrow = Application.inputbox (prompt:="Input row:", type:=2) OneCol = Application.inputbox (prompt:="input column:", type:=2) A ReDimarr (Row, col) - fori =1 toRow - forj =1 toCol theArr (i, j) = (I * j)Mod - - Next - Next - + 'need to select -Worksheets ("Sheet3"). Activate + DimRng asVariant A SetR

Distance education is nothing new, but the term is abused by the market too much.This gives us a question mark on how the course is going and how it works. I am very happy to introduce you to the learning blog of our Excel VBA students.In the future, the blog list of all oiio students will be sorted in this post.They have their understanding, inspiration, and feelings about the course. I believe that after

In work, Excel + VBA is often used for some data operations. When reading thousands of rows of data, a progress display is required. Although VBA has a progress bar with an active control, it cannot be used properly. So I made a self-made one and displayed it in the status bar. Code: 'Custom progress bar. Function getprogress (curvalue, maxvalue) dim I as si

Using the following VBA code, you can count a character or a number, or even a string, within a range of data regions, several times, or several, in Excel. The code below is the VBA macro code. Set Myb = CreateObject ("Scripting.Dictionary"): Myb ("number") = "Times" Set rng = Application.inputbox ("Select the area to be counted:", type:=8) ActiveSheet.Cells.

Workbooks. opentext (filename, origin, startrow, ype, textqualifier, delimiter, tab, semicolon, comma, space, other, otherchar, fieldinfo, textvisuallayout, delimiter, thousandsseparator, delimiter, local) For more information about the meanings of the preceding parameters, see the help of VBA. In actual programming, it is generally not necessary to process these complex parameters. You can use a recording macro to obtain the

1, VBA traversal to add a reference, runtime2. You cannot open a separate application because the newly opened application is not available when you get to the Picture property of shape. Prompts for automation errors.3. Positioning shape, the width and height of shape is required because it is based on the location of the center point. CellsSRC (visSectionObject, Visrowxformout, Visxformpinx). FormulaUSet the format of the property so there is no good

Tags: select Name records width INF from HTTP POST VATThe first step is to download the Postres driver on the Internet, after installation,: https://www.devart.com/odbc/postgresql/download.htmlSecond step to create an ODBC data sourceClick "Start-" control Panel-"Management tools-" data source (ODBC)-"User dsn-" Add "Write VBA code after the installation is configured.private Sub CommandButton1_Click () Dim CNN as New ADODB. Connection Dim rs as New A