# Retrieving ListBox.column values and store into objects

I know there must be a better way to perform the above sub than using multiple If statements. I was thinking of storing the obj.PropertName into a collection and run it through a for loop.

'Create instance
Dim wellObj As CWell
Set wellObj = New CWell


. . .

Private Sub Well_List_Click()

'Must check if null..
If Not (Well_List.Column(1, row) = "") Then
wellObj.wellName = Well_List.Column(1, row)
End If

If Not (Well_List.Column(2, row) = "") Then
wellObj.wellActive = Well_List.Column(2, row)
End If

If Not (Well_List.Column(3, row) = "") Then
wellObj.wellDiameter = Well_List.Column(3, row)
End If

If Not (Well_List.Column(4, row) = "") Then
wellObj.wellCasing = Well_List.Column(4, row)
End If

If Not (Well_List.Column(5, row) = "") Then
wellObj.screenedInterval = Well_List.Column(5, row)
End If

If Not (Well_List.Column(6, row) = "") Then
wellObj.wellSock = Well_List.Column(6, row)
End If

If Not (Well_List.Column(7, row) = "") Then
wellObj.wellDepth = Well_List.Column(7, row)
End If

End Sub


Refactored:

NB: wellObj has all properties type String

Private Sub Well_List_Click()

Dim properties(1 To 7) As String
Dim value As String
Dim row As Integer

row = Well_List.ListIndex + 1

'Must check if null..
properties(1) = "WellName"
properties(2) = "WellActive"
properties(3) = "WellDiameter"
properties(4) = "wellCasing"
properties(5) = "ScreenedInternal"
properties(6) = "WellSock"
properties(7) = "WellDepth"

For i = 1 To 7
value = Well_List.Column(i, row)
If value <> vbNullString Then
CallByName wellObj, properties(i), VbLet, value
End If
Next

End Sub


Start with proper indentation. There shouldn't be anything (other than subroutine/line labels) at the same level of indentation as a Private Sub statement, other than the End Sub.

Code blocks are more readable and code as a whole is easier to follow when code blocks are indented. General rule of thumb, if there's an End X for a statement, you're in a code block. Give that Tab key some lovin'!

Private Sub DoSomething()
|...If Not FooBar Then
|...|...DoSomethingElse
|...End If
End Sub DoSomething


Using a For..Next loop here is tricky, because you're accessing a different property every time. You're going to need to access the properties by name, and to do that you need them in an indexed structure, like an array:

Dim properties(1 To 7) As String
properties(1) = "wellName"
properties(2) = "wellActive"
properties(3) = "wellDiameter"
properties(4) = "wellCasing"
properties(5) = "screenedInternal"
properties(6) = "wellSock"
properties(7) = "wellDepth"


Now you can go from 1 to 7 and, assuming CWell is a class module (I'm not all that familiar with Access, I hope my assumption is correct)...

For i = 1 To 7
value = Well_List.Column(i, row)
If value <> vbNullString Then SetPropertyValue wellObj, properties(i), value
Next


Notice I'm using vbNullString instead of an empty string literal "". vbNullString is a null string pointer, which isn't allocated a memory address. "" has its own memory spot. No biggie, but not needed either.

So, how would that SetPropertyValue method be implemented? The VBA standard library has this little wonder in its VBA.Interaction module, called CallByName:

Private Sub SetPropertyValue(ByRef instance, ByVal member, ByVal value)
CallByName instance, member, vbLet, value
End Sub


This is assuming CWell exposes a Property Let accessor/mutator. If wellName and friends are public fields, you need to properly encapsulate them and expose them via properties.

That said, public members should be PascalCase, so "wellName" would be "WellName".

Now, I really introduced that SetPropertyValue method just to make an abstraction level for the sake of this answer. The one-liner could just as well be inlined:

Dim properties(1 To 7) As String
properties(1) = "wellName"
properties(2) = "wellActive"
properties(3) = "wellDiameter"
properties(4) = "wellCasing"
properties(5) = "screenedInternal"
properties(6) = "wellSock"
properties(7) = "wellDepth"

For i = 1 To 7
value = Well_List.Column(i, row)
If value <> vbNullString Then
CallByName wellObj, properties(i), vbLet, value
End If
Next

• Yay for for loops. Thé solution for this case. – Mast Jun 22 '15 at 16:12
• Your assumption was correct. I did some preliminary test and it worked perfectly. CallByName is a lifesaver! Thanks – Jabberbyter Jun 22 '15 at 17:30
• @Jabberbyter "CallByName is a lifesaver" ...indeed! – Mathieu Guindon Jun 22 '15 at 17:39