Home > Article > Software Tutorial > Excel macro code is not difficult, experts please raise your hands
Private Sub test()
Dim result As String 'A random number containing ten numbers from 0 to 9
Dim randomValue As Integer
Dim randomData(10) As Integer
Dim flag As Boolean
For i = 0 To 9
flag = True
While flag = True
Randomize
randomValue = Int((9 - 0 1) * Rnd 0)
If i = 0 Or search(randomValue, randomData, i) = False Then
result = result & CStr(randomValue)
randomData(i) = randomValue
flag = False
End If
Wend
Next
End Sub
Private Function search(ByVal key As Integer, ByRef data() As Integer, ByVal length As Integer) As Boolean
If length = 0 Then
search = True
Exit Function
End If
search = False
For i = 0 To length - 1
If data(i) = key Then
search = True
Exit Function
End If
Next
End Function
After writing it, this is to generate random numbers. You put it in the event of clicking the button
Using a dictionary object will be much faster. The code is as follows: (If you think it is not fast enough, read all column A of sheet3 into an array to reduce the number of frequent readings of the worksheet and speed up a lot)
Sub pick duplicate()
Dim Sht2Dic, CongFuArr()
Dim N As Long
Dim Rng2 As Range, Rng3 As Range
Set Sht2Dic = CreateObject("Scripting.Dictionary")
For Each Rng2 In Sheet2.Range("A1:A" & Sheet2.UsedRange.Rows.Count)
Sht2Dic(Rng2.Value) = Sht2Dic(Rng2.Value) 1
Next
For Each Rng3 In Sheet3.Range("A1:A" & Sheet3.UsedRange.Rows.Count)
If Sht2Dic.exists(Rng3.Value) Then
N = N 1
ReDim Preserve CongFuArr(1 To N)
CongFuArr(N) = Rng3.Value
End If
Next
Sheet1.Columns("") = ""
Sheet1.Range("A1").Resize(N, 1) = WorksheetFunction.Transpose(CongFuArr)
End Sub
Click Development Tools--Record Macro, set a meaningful macro name, and set the shortcut keys for using the macro. If necessary, you can enter a detailed description of the macro and click OK
From now on, every step of your operation must meet your needs, because excel has started recording your operation steps, and now you find that the position of recording macros on the toolbar has changed to stop recording.
Then according to the example, we select all tables, set the font to italics, set the font size to size 16, and set the background color to turquoise, as shown in the picture, each step is set correctly.
After the steps are completed, click to stop recording, so that the macro for setting the table format in batches is recorded. When we click on the macro, we can find that the macro we just recorded has been saved.
When we need to set the same format for other tables, we only need to call this macro, as shown in the figure, the format before calling the macro.
Select the table you want to format, then click Macro, the macro list box will pop up, select the macro you recorded, click Run, and you will find that the format of the table becomes the format we want.
The above is the detailed content of Excel macro code is not difficult, experts please raise your hands. For more information, please follow other related articles on the PHP Chinese website!