下列操作基于Office2007版本。
目录
VBA开发模式打开
语法操作
常量定义方法
Public Const PI As Double = 3.1415926535
字符和数值,Val函数,以及Format通用格式化
'String->数值
Val(localSht.Cells(row, 6))
'数值->String (Format函数可以格式化)
Dim maxCode As Integer
Format(maxCode, "00000")
'字符串拼接
localSht.Cells(row, 7) = strType & strKind & localSht.Cells(row, 6)
判断条件
If (H < high And W < width) Then
XX = S
Exit Function
ElseIf (H < high And W >= width) Then
angle1 = WorksheetFunction.Acos((L / 2 + width / 2) / r1)
s1 = angle1 * r1 ^ 2 - r1 * Sin(angle1) * (L / 2 + width / 2)
angle2 = WorksheetFunction.Acos((L / 2 + width / 2) / r2)
s2 = angle2 * r2 ^ 2 - r2 * Sin(angle2) * (L / 2 + width / 2)
XX = S - s1 - s2
Else
XX = WorksheetFunction.Min(high, H) * WorksheetFunction.Min(width, W)
End If
'循环获取行数
Do
row = row + 1
strTimeInfo = localSht.Cells(row, 3)
If localSht.Cells(row, 4) = strType And localSht.Cells(row, 5) = strKind Then
If Val(localSht.Cells(row, 6)) > maxCode Then
maxCode = Val(localSht.Cells(row, 6))
End If
End If
Debug.Print row & "max=" & maxCode
Loop Until strTimeInfo = ""
Select Case stComboText
Case "选A"
GetKindABCD = "A"
Case "选B"
GetKindABCD = "B"
Case "选C"
GetKindABCD = "C"
Case "选Z"
GetKindABCD = "Z"
End Select
循环操作
'循环获取行数
Do
row = row + 1
strTimeInfo = localSht.Cells(row, 3)
Debug.Print row
Loop Until strTimeInfo = ""
函数定义,参数传入及返回值。GetKindABCD为函数名,返回值直接赋予GetKindABCD
Function GetKindABCD(stComboText As String) As String
Select Case stComboText
Case "选A"
GetKindABCD = "A"
Case "选B"
GetKindABCD = "B"
Case "选C"
GetKindABCD = "C"
Case "选Z"
GetKindABCD = "Z"
End Select
End Function
子过程定义,中途退出方法Exit Sub。
Private Sub ComboBox_KINDABCD_Change()
Dim strType As String
Dim strKind As String
If FormCodeAssign.ComboBox_TYPEAC.Text = "" Or FormCodeAssign.ComboBox_KINDABCD.Text = "" Then
Exit Sub
End If
strType = GetTypeAC(FormCodeAssign.ComboBox_TYPEAC.Text)
strKind = GetKindABCD(FormCodeAssign.ComboBox_KINDABCD.Text)
FormCodeAssign.TextBox_NEWCODE = strType & strKind & GetNewItemCodeNumber(strType, strKind)
End Sub
FORM中常用的控件操作
ComboBox
添加选项,其中fmStyleDropDownList设定风格为下拉式不可输入编辑,设定初始选择项目可以调用 ListIndex方式,从0开始计数。(FormCodeAssign为 Form表单名称)
FormCodeAssign.ComboBox_KINDABCD.AddItem ("A")
FormCodeAssign.ComboBox_KINDABCD.AddItem ("B")
FormCodeAssign.ComboBox_KINDABCD.AddItem ("C")
FormCodeAssign.ComboBox_KINDABCD.AddItem ("Z")
FormCodeAssign.ComboBox_KINDABCD.Style = fmStyleDropDownList
FormCodeAssign.ComboBox_KINDABCD.ListIndex = 0
获取当前选择项的文本,
FormCodeAssign.ComboBox_KINDABCD.Text
当前表单显示或不显示,调用Show和Hide方法
FormCodeAssign.Caption = "申请"
FormCodeAssign.Show
FormCodeAssign.Hide
对象操作【Book,Sheet,Cells】
获取其中的Sheet对象
Set localSht = Sheets("管理名字")
strTimeInfo = localSht.Cells(row, 3)
调试方法
可以进入调试模式,通过单步调试或者断点。也可以调用Debug.Print。
Dim row As Integer
Debug.Print "FormCodeAssign show"
Debug.Print row
或者消息框
MsgBox "申请成功!! " & localSht.Cells(row, 7)
其他补充
'跳转到最新行或指定行
ActiveWindow.ScrollRow = row
ActiveWindow.ScrollColumn = 1
vba中的“ByRef参数类型不符”问题:
一般情况下,是函数参数和传参的类型不匹配造成的,还有可能是函数内部调用的 函数有不妥的地方反馈造成的。
比如,求反余弦函数Acos,直接调用Acos(值)是有问题的,应该使用WorksheetFunction.Acos(值),还有最小值,最大值
WorksheetFunction.Min(x1,x2) 等