Excel VBA实例

一直知道excel里面也可以做窗体,前段时间有个要在excel里生成随机数的劳务,于是研究了一下,记录一下

 

首先我用的是excel2007版,这个版本跟2003有些差异,开发工具并没有在正常视图里

 

 调出excel2007窗体工具的方法是

 

点击界面左上方的office按钮,然后选择excel选项

在弹出的窗口中选择常用

钩选在功能区显示开发工具选项卡

然后点击确定按钮后

菜单区域就会出现开发工具一栏

 

下面正式开始

1.新建一个excel文件

2.选择开发工具

3.选择visual basic视图或按alt+F11

4.在vbaproject上点右键,选择插入-用户窗体

5.点击菜单栏上的工具箱,把窗体工具箱显示出来,插入自己需要的控件

6.选择视图-代码窗口

7.写相应代码处理

Dim randomnumDim randomlenDim dataPrivate Sub CommandButton1_Click() Sheet1.Columns("B:B").Select Application.Selection.Delete For q = 1 To TextBox1.Value GetOnerRow (q) Next qEnd SubPrivate Sub CommandButton2_Click() Sheet1.Range("B1: B" + TextBox1.Value).Select Application.Selection.Copy End SubPrivate Sub Label2_Click()End SubPrivate Sub Label3_Click()End SubPrivate Sub TextBox1_Change()End SubPrivate Sub UserForm_Click()End SubPrivate Sub UserForm_Initialize() ComboBox1.RowSource = Sheet1.Range("A2: A4").Address ComboBox2.RowSource = Sheet1.Range("A5:A6").Address TextBox1.Value = 10End SubPublic Function GetRandNum(i, j) As String randomnum = Application.WorksheetFunction.RandBetween(i, j)End FunctionPublic Function GetRandNum5_8() randomlen = Application.WorksheetFunction.RandBetween(5, 8)End FunctionPublic Sub GetOnerRow(q) data = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z") flag = True m = "" If (ComboBox1.Value = "单独数字0-9") Then flag = True If (ComboBox2.Value = "8位") Then For p = 1 To 8 Call GetRandNum(0, 9) m = m + data(randomnum) Next p End If If (ComboBox2.Value = "5-8位随机") Then Call GetRandNum5_8 o = randomlen For p = 1 To o Call GetRandNum(0, 9) m = m + data(randomnum) Next p End If End If If (ComboBox1.Value = "单独字母a-z") Then flag = False If (ComboBox2.Value = "8位") Then For p = 1 To 8 Call GetRandNum(10, 35) m = m + data(randomnum) Next p End If If (ComboBox2.Value = "5-8位随机") Then Call GetRandNum5_8 o = randomlen For p = 1 To o Call GetRandNum(10, 35) m = m + data(randomnum) Next p End If End If If (ComboBox1.Value = "数字+字母") Then If (ComboBox2.Value = "8位") Then For p = 1 To 8 Call GetRandNum(0, 35) m = m + data(randomnum) If randomnum > 9 Then flag = False End If Next p End If If (ComboBox2.Value = "5-8位随机") Then Call GetRandNum5_8 o = randomlen For p = 1 To o Call GetRandNum(0, 35) m = m + data(randomnum) If randomnum > 9 Then flag = False End If Next p End If End If If (flag) Then m = "'" + m End If Sheet1.Cells(q, 2) = mEnd SubPrivate Sub UserForm_Terminate() ThisWorkbook.Save Application.ScreenUpdating = False Application.DisplayAlerts = False '时关闭保存提示 If Workbooks.Count > 1 Then '‘检查究竟开了几个EXCEL文件 ' ThisWorkbook.Close '‘如果大于等于两个文件，就用CLOSE关闭当前EXCEL，另一个不管他，保持打开 Else 'Application.Quit '‘如果只有一个打开的文件，用QUIT关闭，包括EXCEL进程 End IfEnd Sub

8.最终效果