Solved: VBA effeciency question

[theseus75]

I'm working on a VBA form to populate an Excel workbook, and I have the feeling that my code could be written/operate more efficiently. In fact, it's probably messy enough to make some of you cringe, but I'm a total newb when it comes to VBA.

Too, and perhaps this needs to be its own thread, I would prefer that - if a field were left blank - that it insert nothing into the corresponding workbook cell instead of "0", but the code didn't work when I tried writing the conditional to read:
If frmJanData.txtDeath2 = "" Then
frmJanData.txtDeath2 = ""
End If

And don't let the morose verbiage concern you: I work in healthcare, so it's to populate a workbook with a list of patient IDs for those who've expired. The pt IDs are typically a 9 digit number.

Thanks in advance for any help!


Private Sub cmdSubmit_Click()

Sheets("deaths").Select


Dim txtDeath1 As Long
Dim txtDeath2 As Long
Dim txtDeath3 As Long
Dim txtDeath4 As Long
Dim txtDeath5 As Long
Dim txtDeath6 As Long
Dim txtDeath7 As Long
Dim txtDeath8 As Long
Dim txtDeath9 As Long
Dim txtDeath10 As Long


If frmJanData.txtDeath1 = "" Then
frmJanData.txtDeath1 = 0
End If
If frmJanData.txtDeath2 = "" Then
frmJanData.txtDeath2 = 0
End If
If frmJanData.txtDeath3 = "" Then
frmJanData.txtDeath3 = 0
End If
If frmJanData.txtDeath4 = "" Then
frmJanData.txtDeath4 = 0
End If
If frmJanData.txtDeath5 = "" Then
frmJanData.txtDeath5 = 0
End If
If frmJanData.txtDeath6 = "" Then
frmJanData.txtDeath6 = 0
End If
If frmJanData.txtDeath7 = "" Then
frmJanData.txtDeath7 = 0
End If
If frmJanData.txtDeath8 = "" Then
frmJanData.txtDeath8 = 0
End If
If frmJanData.txtDeath9 = "" Then
frmJanData.txtDeath9 = 0
End If
If frmJanData.txtDeath10 = "" Then
frmJanData.txtDeath10 = 0
End If

txtDeath1 = frmJanData.txtDeath1
txtDeath2 = frmJanData.txtDeath2
txtDeath3 = frmJanData.txtDeath3
txtDeath4 = frmJanData.txtDeath4
txtDeath5 = frmJanData.txtDeath5
txtDeath6 = frmJanData.txtDeath6
txtDeath7 = frmJanData.txtDeath7
txtDeath8 = frmJanData.txtDeath8
txtDeath9 = frmJanData.txtDeath9
txtDeath10 = frmJanData.txtDeath10


Range("b6").Value = txtDeath1
Range("b7").Value = txtDeath2
Range("b8").Value = txtDeath3
Range("b9").Value = txtDeath4
Range("b10").Value = txtDeath5
Range("b11").Value = txtDeath6
Range("b12").Value = txtDeath7
Range("b13").Value = txtDeath8
Range("b14").Value = txtDeath9
Range("b15").Value = txtDeath10



End Sub

 

[OBP]

As none of the Excel gurus have answered your question I will have a go. I would have said that you could cut out the section that uses the "txtDeath1 = frmJanData.txtDeath1" etc statements unless you need to use "txtDeath1" and so on again somewhere else.
I would put the "frmJanData.txtDeath1" directly in to the Ranges instead.

I wouldn't worry too much though unless the code is running particularly slowly, as long as it does what you want.

Have you considred Access for this database?

 

[theseus75]

Yeah, I don't think I needed to use "txtDeath1" anywhere else, and that saves me about 2/3rds of my work. Thanks so much. It wasn't running slow, but I have so much left to do (for other indicators) that the truncated way will save hours.
I thought about doing it in Access, but since this will be something more lay users will fill in, I didn't want Access scaring them off.
I don't suppose you know how to make sure that what they're putting in is a number?

Thanks again for you help.

 

[theseus75]

I should also mention - in case anyone else is checking out this thread for help - that going with the more direct reference "Range("b6").Value = frmJanData.txtDeath1" also allows for empty cells without causing en error (if, say, someone wanted to report fewer than 10 deaths), whereas my above-listed code errored if I didn't have anything in the cell, hence putting in the 0 if nothing else were filled in.

 

[OBP]

theseus, in actual fact Access is a much friendlier program for users than Excel, providing that the Database is well constructed.
It allows you to control what the users can see, enter or select more easily than Excel, it can however be harder on the Creator as it is a more complex package than Excel for database use.
To control if the data is a Number check out the IsNumeric function