Home  >  Article  >  Software Tutorial  >  Excel macro code is not difficult, experts please raise your hands

Excel macro code is not difficult, experts please raise your hands

WBOY
WBOYforward
2024-01-17 10:06:21699browse

Excel macro code is not difficult, experts please raise your hands

It’s not difficult for experts to program excel macro code

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

Please help with an EXCEL macro code, thank you!

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

How to use macros on a table in excel

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!

Statement:
This article is reproduced at:docexcel.net. If there is any infringement, please contact admin@php.cn delete