[Dba-office] Excet Table with VBA

[Salakhetdinov Shamil]

 Susan --

Here is how you can specify a variable set of values to be assigned for a newly added row's cells:

Public Sub aTest()
testAddXlTableRow2 "v1", "v2", "v3"
End Sub
Public Function testAddXlTableRow2(ParamArray values() As Variant) As listRow
' Key column index is assumed to be 1
Dim keyColumnIndex As Integer
keyColumnIndex = 1
Dim lst As ListObject
Set lst = ThisWorkbook.Worksheets("DataValidation").ListObjects("Table1")
Dim rowIndex As Integer
For rowIndex = 1 To lst.ListRows.Count
If lst.ListRows(rowIndex).Range(keyColumnIndex) = values(0) Then
lst.ListRows(rowIndex).Range.Select
MsgBox "Duplicate value found at row # = " + CStr(rowIndex)
Set testAddXlTableRow2 = Nothing
Exit Function
End If
Next rowIndex
Dim listRow As listRow
Dim valueIndex As Integer
Set listRow = lst.ListRows.Add(AlwaysInsert:=True)
For valueIndex = 1 To UBound(values) + 1
listRow.Range.Cells(valueIndex) = values(valueIndex - 1)
Next valueIndex
Set testAddXlTableRow2 = listRow
End Function
-- Shamil


>Friday, December 18, 2015 9:37 AM -05:00 from Susan Harkins <ssharkins at gmail.com>:
>
>Thanks Shamil -- it's similar to what I ended up with last night. Let's compare! I think the only real difference is selecting the newly inserted row. I knew going in that Tables are internally, a list object, but I had anticipated more Table properties and methods, but nope... :) There really should be an Add method that lets you specify (optional) new values for the new rows! I expected that, but didn't find it. 
>
>I find Excel's object model confusing and I struggle to find the right data types and objects in VBA. 
>
>Thank you for helping! -- Susan H. 
>
>Private Sub CommandButton1_Click()
>    'Enter value into ListPerson column in Table1.
>    
>    Dim str As String
>    Dim dbl As Double
>    Dim rng As Range
>    Dim oNewRow As ListRow
>    
>    str = TextBox1.Value
>    
>    Set rng = ThisWorkbook.Worksheets("DataValidation").Range("Table1[ListPeople]")
>    
>    'Handle Match error when match not found.
>    
>    If Not IsError(Application.Match(str, rng, 0)) Then
>        dbl = Application.Match(str, rng, 0)
>    Else
>        dbl = 0
>    End If
>    
>    'If match is found, display duplicate error.
>    'If match not found, add new record to Table1.
>    If dbl <> 0 Then
>        MsgBox "You can't enter duplicate values.", vbOKOnly, "Error"
>        Exit Sub
>    Else
>       'Select Table1.
>        rng.Select
>        Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
>        oNewRow.Range.Cells().Value = str
>        Set oNewRow = Nothing
>    End If
>    
>End Sub
>
><<< skipped >>>