VBA for Excel FAQ:
1,
Q: I have a small program that needs some modifications to the existing Excel format. Mainly to add some ranks, combine some cells, set the format of the cell, and so on.
When the program is running, only the Excel table changes in front of you, dazzling.
I want the program to be completed in silent mode, after completing UPDATA Windows?
Or others can make it not dazzling
A:
Before running
Application.screenupdating = false
After running
Application.screenupdating = true
2,
Q:
I got an Excel's cell address: use Cells references: cells (5, 2)
Then it's R
1C
1 Address Because the corresponding "B5"
Is there any convenient function to help me convert to another after providing one of the addresses? ?
A:
Expression.Address (Rewabsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
3,
Q:
I want to shield the warning of the system and not let it pop up.
A:
Application.displayAlerts = false
4,
Q:
In Excel, you want to get the number of rows actually used, that is, the number of rows of content, not a blank part
A:
Dim usrow as integer
DIM USCOL AS INTEGER
USROW = Activeesheet.USEDRANGE.ROWS.COUNT
USCOL = Activeesheet.USEDRANGE.COLUMNS.COUNT
USROW is the number of rows, USCOL is the number of columns.
5,
Q: How to remove the formula of the cell, keep its data?
A:
Cells (N, M) .Value = Cells (n, m) .value
6,
Q: How can I change to set the cell floor color?
A:
Range object. Interior. Colorindex = 13 'color index
7.
Q:
Need to put the value of 8:00:00 8. 8 I have no corresponding function.
A:
DIM X as integer
x = Hour (CDATE ("8:00:00"))
8,
Q:
How to get the current executive of the current XLS document? ? ?
A:
ActiveWorkbook.path 'The path to the current document
ActiveWorkbook.FullName 'with the current document name of the path
ActiveWorkbook.name 'current document name
9,
Q:
How to pop up the custom menu when you click on a cell in Excel?
A:
Private Sub Workbook_sheetSelectionChange (Byval SH As Object, ByVal Target as Excel.Range)
If Activecell.Row = 3 and Activecells.column = 3 THEN
Userform1.show
END IF
End Sub
10,
Q:
The following is a piece of macro, the function is like this: In the Sheet1 table, when the mouse is selected, the first column of the line is assigned to the second line of the Sheet2 table. The purpose is to achieve such a code in a record macro? Please advise ~!
Row = Activecell.Row
Sheets ("Sheet2"). Cells (4, 2) .value = Cells (Row, 1) .value
A:
Row = Activecell.Row
Sheets ("Sheet2"). Cells (4, 2) .value = Sheets ("Sheet1"). Cells (Row, 1) .value
or
Sheets ("Sheet2"). Cells (4, 2) .value = Activecell.Value
Can.
