Excel VBA-array and other knowledge

Source: http://blog.csdn.net/alexbnlee/article/details/6917800

Array: defines an array:
Dim arr (30 To 50) As Single defines an array, from 30 To 50, As a floating point type ~
Arr (30) = 34.3

A random number is generated, but the random number must be different (10 different random numbers are generated between 1 and 20)

 

Sub sdlkfjl () For I = 1 To 10 Cells (1, I) = Int (1 + Rnd () * 19) 'Give a random number For j = 1 To I-1 Do While Cells (1, j) = Cells (1, I) 'To determine whether the number is the same as the previous one, knowing that Cells (1, I) = Int (1 + Rnd () * 19) are not the same, re-generate the random number Loop Next End Sub

→ On Error Resume Next indicates that when a running Error occurs, the control forwards the control to the statement after the wrong statement occurs, and continues running here.

→ Duplicate values can be removed from Collection objects.
A Collection object consists of two parts: the value part and the Key part. The Key part is used to differentiate duplicate values. If it is the same Key, then, only
A value is used as the Collection value, so when writing, if you want to remove the duplicate value of the element, you can directly use the element value as the Key value, but the Key must be of the primary type, so you can use
CStr function for conversion ~
In addition, because duplicate Key values exist, you must use the On Error Resume Next statement to bypass the Error ~

Private Sub UserForm_Initialize () Dim r As Integer Dim I As Integer Dim MyCol As New Collection Dim arr () As Variant On Error Resume Next With Sheet1 r =. cells (. rows. count, 1 ). end (xlUp ). row For I = 1 To r If Trim (. cells (I, 1) <> "Then MyCol. add Item: = Cells (I, 1), key: = CStr (. cells (I, 1) End If Next End With ReDim arr (1 To MyCol. count) 'maximum value has changed because repeated values will become non-repeated ~ For I = 1 To MyCol. Count arr (I) = MyCol (I) Next ListBox1.List = arr End Sub

 

→When combox1 changes, the following events are triggered:

    Private Sub ComboBox1_Change()          Dim MyAddress As String          Dim rng As Range          ComboBox2.Clear          With Sheet1.Range("A:A")              Set rng = .Find(What:=ComboBox1.Text)              If Not rng Is Nothing Then                  MyAddress = rng.Address                  Do                      ComboBox2.AddItem rng.Offset(, 1)                      Set rng = .FindNext(rng)                  Loop While Not rng Is Nothing And rng.Address <> MyAddress              End If          End With          ComboBox2.ListIndex = 0          Set rng = Nothing      End Sub  

 

Where

rng.Address <> MyAddress  

It is very important because the FindNext function will be retrieved up. If this restriction is not found, it will keep repeating ~
→ VbCrlf and Chr (13) Both indicate line breaks.
→ Press Ctrl + J to prompt for methods and Properties
→ Space + lower ring line can divide a row into multiple rows to write Space + Underscore
→ The Array function returns a Variant containing an Array.

    Dim A As Variant      A = Array(10,20,30)      B = A(2)  

 

→ The Split function returns a one-dimensional array with a small mark starting from scratch. It contains a specified number of substrings.

    Sub SplitExample()          Dim Str() As String          Str = Split("a,b,c,d,e", ",")          For i = 0 To UBound(Str)              s = s & Str(i) & vbCrLf          Next          MsgBox s      End Sub  

 

Reference: http://club.excelhome.net/forum.php? Mod = viewthread & tid = 196095.
Simple replication of cells, saving cell values as Arrays

    Private Sub CommandButton1_Click()          arr = Sheet2.Range("a1:e83")          Range("a1:e83") = arr      End Sub  

 

→ Example of the Split Function & Join function, which extracts the name and gender from a sentence

Sub SplitExample () Dim Str () As String Cells (2, 1) = "all" Cells (2, 2) = "name" Cells (2, 3) = "gender" Str = Split (Cells (1, 1), ",") For I = 0 To UBound (Str) Cells (I + 3, 1) = Str (I) If Right (Str (I), 3) = "(female)" Then Cells (I + 3, 2) = Left (Str (I ), inStr (Str (I), "(")-1) Cells (I + 3, 3) = "female" Else Cells (I + 3, 2) = Str (I) cells (I + 3, 3) = "male" End If Next MsgBox Join (Str, ",") End Sub

 

The Join function is the reverse process of the Split function ~

→ Dynamic Array
ReDim Preserve array name (UBound (array name) + n) After expanding the array boundary, the original value is retained; otherwise, it is automatically deleted.

→ Array Replication
You can use "=" to directly copy the array, but make sure that the array on the left of "=" is a dynamic array.