+ Reply to Thread
Results 1 to 15 of 15

Need advice about certification

  1. #1
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Need advice about certification

    Hi,

    I currently do a lot of coding for my company. Here is a sample:

    Please Login or Register  to view this content.
    All I have learned about coding has been from other users and reading books like John Walkenbach's Power Programming. What I really want to do is learn it from a best practices perspective, or understand it at that level. My company pays for tuition reimbursement, especially if there is a certification involved. I was wondering if anybody could tell me, what my best bet would be given my current knowledge of programming.

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Need advice about certification

    Hi,

    please find some comments in the code, please note that the code is untested and may be improvable

    Basically: Avoid selecting cells/ranges, declare all your variables, qualify sheets/cells

    Please Login or Register  to view this content.
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Need advice about certification

    I really do appreciate your help. How did you learn all of these things? If you noticed, I asked where I could learn the best coding methodology because my company offers to pay my tuition. I will create a fully notated VBA for this project later today for your consideration.

  4. #4
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Need advice about certification

    Hi,

    About 3 years ago I had a 2-Day beginners course for Excel VBA paid by my company. After that it was learning by failing, a lot of google search and the MS documentation reading, but I think the most important part is practice, especially at the beginning you can forget things fast when you don't use them.

    Knowing languages like Java or C makes VBA very easy, you just have to get used to the object model and functions.

    If you want to get more information I'd suggest to start reading here: http://www.excelforum.com/excel-prog...materials.html

  5. #5
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Need advice about certification

    Hi I attached my workbook with my code. If you could look at it, and give me your feedback. Am I doing this too hard.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Need advice about certification

    I could not include the data because my company does not allow that.

  7. #7
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Need advice about certification

    It's nearly 1700 lines of code without test data... If there is no problem with the code I'd suggest to leave it like it is..unless you have a lot of spare time

    As I said before try to avoid selecting cells/ranges but qualify them with the appropriate workbook and worksheet.

    The subs you are using are based on the cell that is currently selected, instead use the cell as an argument into the sub: http://www.functionx.com/vbaexcel/functions/Lesson3.htm

    If you want to improve your programming see the link in post #4 to improve programs in future.

  8. #8
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Need advice about certification

    Resb_Workingfile.xlsmRESB_Data.xlsx

    Hi,

    I know you said that I should limit selecting sheets. Here is two attachments with dummy data that represents a typical macro I would write for. If you would like to look at it (This time there is less than 100 lines) and let me know how I should do it so I do not select sheets so much.

    Thanks,

    Gregg

  9. #9
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Need advice about certification

    Hi,

    please check the code and comments in here: Resb_Workingfile_EDIT.xlsm

  10. #10
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Need advice about certification

    tehneXus

    Thank you for your help!! I really do appreciate you walking me through!

  11. #11
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Need advice about certification

    I hate to be a bother, but there is one more question. When you imported the data the new way, it did not keep my Reservation and LIs with leading zeroes, because it lost the ranges as text. Is there a way to change how the vntData variant to carry over the text fields as texts?

  12. #12
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Need advice about certification

    Oh, sorry I didn't pay attention to that
    Replace
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Need advice about certification

    Please Login or Register  to view this content.
    Hi again,

    This code removed my leading zeroes from my concatenate range. How do I fix that? I tried a couple of things but they did not work.

  14. #14
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Need advice about certification

    like the one before, replace the line with:
    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    09-11-2013
    Location
    Huntsville, AL
    MS-Off Ver
    Excel 2007
    Posts
    180

    Re: Need advice about certification

    thanks again!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Certification
    By lavi in forum Excel General
    Replies: 1
    Last Post: 05-06-2010, 09:44 AM
  2. Excel Certification
    By minkus in forum Excel General
    Replies: 1
    Last Post: 03-04-2010, 01:02 PM
  3. Certification spreadsheet
    By bwj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2008, 05:23 PM
  4. Does getting Certification ?
    By four66 in forum Excel General
    Replies: 1
    Last Post: 05-01-2007, 02:11 PM
  5. VBA programming certification
    By Frans Verhaar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-11-2006, 01:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1