Retrieving ListBox.column values and store into objects

Question

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
 'Skip header
 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

Answer 1

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

• Mast: Yay for for loops. Thé solution for this case.
• Jabberbyter: Your assumption was correct. I did some preliminary test and it worked perfectly. CallByName is a lifesaver! Thanks
• Mathieu Guindon: @Jabberbyter "CallByName is a lifesaver" ...indeed!