1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: VBA effeciency question

Discussion in 'Business Applications' started by theseus75, Oct 1, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. theseus75

    theseus75 Thread Starter

    Joined:
    Jan 7, 2005
    Messages:
    275
    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
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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?
     
  3. theseus75

    theseus75 Thread Starter

    Joined:
    Jan 7, 2005
    Messages:
    275
    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.
     
  4. theseus75

    theseus75 Thread Starter

    Joined:
    Jan 7, 2005
    Messages:
    275
    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.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/755101

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice