带你入门VBA系列之：不懂的代码快点贴过来

jick

我用ACCESS编的用户密码登陆代码,红色的是出错的,请老师指点指点::::::::::::!!!!!!!!!!!!!

Private Sub 命令1_Click()
    Dim cond As String
    Dim ps As String
    If IsNull(Forms![password]![用户名]) Or IsNull(Forms![password]![口  令]) Then
        MsgBox "必须输入用户名/口令", vbOKOnly, "信息提示"
        Exit Sub
    End If
    cond = "uname='" + Forms![password]![用户名] + " ' "
    ps = DLookup("upass", "password", cond)
    If (ps <> Forms![password]![口  令]) Then
        MsgBox "不存在该用户", vbOKOnly, "信息提示"
    Else
        MsgBox "欢迎使用本系统", vbOKOnly, "信息提示"
    End If
       
End Sub

baiyunljy

请问，老师，我的这个怎么总是提示.ComboBox1，方法和数据成员未找到，代码如下：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Dim 姓名
Set 姓名 = Sheets(1).Range("c2:d13")
If Target.Column = 8 Or Target.Column = 9 Then
Sheet1.ComboBox1.Visible = True
Sheet1.ComboBox1.Left = Target.Left + 30
Sheet1.ComboBox1.Top = Target.Top
Sheet1.ComboBox1.ListFillRange = 姓名
Sheet1.ComboBox1.ComboBox1.ListIndex = 30
Sheet1.ComboBox1.LinkedCell = Target.Address
Sheet1.ComboBox1.ColumnWidths = "30;30;30" '分别设置三列之宽度
Else: Sheet1.ComboBox1.Visible = False
End If
End Sub

baiyunljy

帮帮我吧，我找不到原因，好心人，帮帮忙啊

gzxw

谢谢　Long_III

我采用你所说的第二种方式，可以改为函数使用，如下，

Function jc(r1 As String)
    Dim i%, iMax%, k%, m
    Dim arr1, arr2, arr() As String
    arr1 = Array("", "吖", "八", "嚓", "咑", "鵽", "发", "猤", "铪", "夻", "咔", "垃", "嘸", "旀", "噢", "妑", "七", "囕", "仨", "他", "屲", "夕", "丫", "帀")
    arr2 = Array("", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z")
    For k = 1 To Len(r1)
      m = Mid(r1, k, 1)
      ReDim Preserve arr(1 To k) '定义动态数组大小
      arr(k) = arr2(Application.WorksheetFunction.Match(m, arr1, 1) - 1) '用match函数的近似查找
    Next
   jc = Join(arr, "")
End Function

但第一种改为函数时，却不行，不知道什么地方不对，请老师看看！

Function jc(r1 As Range)
    Dim i%, iMax%, k%
    Dim arr()
    Dim arr1, arr2, arr3
    Dim m As String
    arr1 = Array("", "吖", "八", "嚓", "咑", "鵽", "发", "猤", "铪", "夻", "咔", "垃", "嘸", "旀", "噢", "妑", "七", "囕", "仨", "他", "屲", "夕", "丫", "帀")
    arr2 = Array("", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z")
    iMax = UBound(arr1)
    ReDim arr(0 To iMax, 0 To 1) '重新定义一个二维数组
    For i = 0 To iMax
        arr(i, 0) = arr1(i)
        arr(i, 1) = arr2(i)
    Next
    For k = 1 To Len(r1)
        m = Mid(r1, k, 1)
        ReDim Preserve arr(1 To k) '定义动态数组大小
        arr(k) = Application.WorksheetFunction.VLookup(m, arr, 2)
    Next k
    jc = Join(arr(), "")
End Function

gzxw

另外，再请教一个问题，在一个数组去查找一个值，如果没有，其返回值怎样表示，麻烦用语句写明白一点。

bychd

count = [A65536].End(xlUp).Row + 1   请问这个里面的[A65536]是什么对象？

是range的另外一个表示方法，也可以表示为range("a65536"),cells(65536,1) [Long_III]

[此贴子已经被Long_III于2007-1-30 17:58:02编辑过]

Long_III

QUOTE:

以下是引用baiyunljy在2007-1-26 19:15:58的发言：

请问，老师，我的这个怎么总是提示.ComboBox1，方法和数据成员未找到，代码如下：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Dim 姓名
Set 姓名 = Sheets(1).Range("c2:d13")
If Target.Column = 8 Or Target.Column = 9 Then
Sheet1.ComboBox1.Visible = True
Sheet1.ComboBox1.Left = Target.Left + 30
Sheet1.ComboBox1.Top = Target.Top
Sheet1.ComboBox1.ListFillRange = 姓名
Sheet1.ComboBox1.ComboBox1.ListIndex = 30
Sheet1.ComboBox1.LinkedCell = Target.Address
Sheet1.ComboBox1.ColumnWidths = "30;30;30" '分别设置三列之宽度
Else: Sheet1.ComboBox1.Visible = False
End If
End Sub

是不是多了一个啊？

Long_III

QUOTE:

以下是引用gzxw在2007-1-27 19:09:39的发言：

另外，再请教一个问题，在一个数组去查找一个值，如果没有，其返回值怎样表示，麻烦用语句写明白一点。

上面的代码可以改为，你仔细看看，你把两个数组arr混在一起了

Function jc(r1 As Range)
    Dim i%, iMax%, k%
    Dim arr()
    Dim arr1, arr2, arr3()
    Dim m As String
    arr1 = Array("", "吖", "八", "嚓", "咑", "鵽", "发", "猤", "铪", "夻", "咔", "垃", "嘸", "旀", "噢", "妑", "七", "囕", "仨", "他", "屲", "夕", "丫", "帀")
    arr2 = Array("", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z")
    iMax = UBound(arr1)
    ReDim arr(0 To iMax, 0 To 1)    '重新定义一个二维数组
    For i = 0 To iMax
        arr(i, 0) = arr1(i)
        arr(i, 1) = arr2(i)
    Next
    ReDim arr3(1 To Len(r1))    '没必要定义为动态数组
    For k = 1 To Len(r1)
        m = Mid(r1, k, 1)
        arr3(k) = Application.WorksheetFunction.VLookup(m, arr, 2)
    Next k
    jc = Join(arr3, "")
End Function

Long_III

QUOTE:

以下是引用gzxw在2007-1-27 19:09:39的发言：

另外，再请教一个问题，在一个数组去查找一个值，如果没有，其返回值怎样表示，麻烦用语句写明白一点。

单独就这样的查找问题，一维数组用match，，二维或多维的时候，最好在数组里用循环了

关于1维的，示例如下

Sub hjs()
    Debug.Print Application.Match("我", Array("", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z"), 0)
    Debug.Print Application.Match("B", Array("", "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z"), 0)
End Sub

结果如下

Error 2042
 3

tobaccohy

Sub FindMe()
    Dim intS As Integer
    Dim rngC As Range
    Dim strToFind As String, FirstAddress As String
    Dim wSht As Worksheet
    Application.ScreenUpdating = False
    intS = 1
    Set wSht = Worksheets("搜索结果")
    strToFind = "您好" '指定搜索的值
    With ActiveSheet.Range("A1:C20") '可根据实际工作表改变范围.
        Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
        If Not rngC Is Nothing Then
            FirstAddress = rngC.Address
                Do
                    rngC.EntireRow.Copy wSht.Cells(intS, 1)
                    intS = intS + 1
                    Set rngC = .FindNext(rngC)
                Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
        End If
    End With
    Application.ScreenUpdating = True
End Sub

兰版请帮忙加下注释