Archives

First learn stand, then learn fly. Nature rule, Daniel-san, not mine. (VBA)

Miyagi

Typically, “Hello World” is the first bit of programming we all do. It is the simplest, and most basic way to interact with the computer, essentially Marco Polo for adults.

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

For the first VBA snippet I’m going to post, we’ll do something slightly more involved. Let’s suppose that your superior wants you to verify that the “Ref Name” text box on the Sales Order (SO) screen is filled out. They intend to use it to document a project name for each sales order and they don’t want the users to have the option of leaving it blank.

As I mentioned in my last post, I approach this with small steps and achieve success on each step before continuing. So, begin by opening the SO screen, right click on the mnemonic, choose components, and BEFORESAVE to get to the VBA environment.

The first small step is essentially the same as “Hello World.”

Public Function BEFORESAVE() As Boolean
 If Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value) = "" Then
    MsgBox ("Please specify a project name.")
 End If
 BEFORESAVE = True
End Function

You may ask, “How do I know the name of the text box?” The simplest way is to click on it and hit Ctrl-Shift-R. This will copy the “path” of the control to the clipboard. Incidentally, you have to add the Trim function because the field can contain blank spaces and that isn’t the same thing as “” to a computer. This code simply notifies the user that they have made a mistake, but doesn’t prevent them from doing so. To prevent them from continuing we have to stop the BEFORESAVE event. Also, it would be nice that when we did so, the original screen would come back up with the text box focused so the user could immediately fix their mistake.

Public Function BEFORESAVE() As Boolean
 If Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value) = "" Then
    MsgBox ("Please specify a project name.")
    BEFORESAVE = False
    ThisForm.m2mpageframe1.page1.txtfordername.SetFocus
    Exit Function
 End If
 BEFORESAVE = True
End Function

That bit of code will force the user to enter information into the text box or it will not let them save. Yes, I know this code isn’t exactly Halo, but this is our first project after all.

What if management only wants numbers entered into this field? Well, you could use the following.

Public Function BEFORESAVE() As Boolean
 If Not IsNumeric(Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value)) Then
    MsgBox ("The Ref Name field can only contain numbers.")
    BEFORESAVE = False
    ThisForm.m2mpageframe1.page1.txtfordername.SetFocus
    Exit Function
 End If
 BEFORESAVE = True
End Function

As you can see, the code is not difficult to read and understand. In a previous post, I mentioned that one of the negatives of using VBA in Made2Manage is that it doesn’t handle error checking very well. That isn’t entirely accurate. Most of the code I’ve seen written by others does not include error handling code. If you want your code to show any errors it encounters, modify your code as below.

Public Function BEFORESAVE() As Boolean
On Error GoTo errCode
 If Not IsNumeric(Trim(ThisForm.m2mpageframe1.page1.txtfordername.Value)) Then
    MsgBox ("The Ref Name field can only contain numbers.")
    BEFORESAVE = False
    ThisForm.m2mpageframe1.page1.txtfordername.SetFocus
    Exit Function
 End If
 BEFORESAVE = True
errCode:
    MsgBox "ERROR: " & Err.Number & " " & Err.Description
End Function

As I mentioned above, I'll be including VBA, SQL, and VFP code which you are free to use and modify. Feel free to comment if there are questions or suggestions for improvement.

VBA Tips and Why Dalton Should be a Beta Tester

Now that I’m sure you’re all fired up about learning VBA the next question has to be….

How do I learn VBA?

I’m glad you asked. Have you taken class “M2M Foundations and VBAe” or watched the pre-recorded version? If not, stop reading now and go do that first. After you’ve done that the best way to learn to code is to code, so start a simple customization project. As your skills increase, take on projects that get progressively more complex.

The Process

If your project is something that will actually be used in production, the first thing you need to do is thoroughly interview the people requesting the customization. Ask probing questions about the project and be as specific as possible. The more thorough you are during this phase, the fewer revisions you’ll have to do later.

Next, break your customization up into small, basic steps and build on little successes. Do not begin a VBA project for Made2Manage by writing 20-50 lines of code. Write just enough code to prove the basic concept. Test it to make sure it works. After that piece works, add the next layer of complexity on top of it. If you code this way, you will only need to debug one concept or procedure at a time. Continue this basic process until your code does what you think it should.

At this point, I will often show the prototype to one of the users to make sure I understood their needs and that this basic design will fulfill them. Make any necessary changes to the basic product before continuing.

Testing

Road House

“Never underestimate your opponent. Expect the unexpected.” – Road House

I cannot emphasize this enough, test everything you write. Test it thoroughly yourself, have others test it in a practice environment, and monitor it for quite some time in production. I subscribe to the philosophy that programmers are typically the worst software testers. Programmers do not think like regular users and are likely to miss a whole slew of “creative” ways users will respond to our programs. Therefore, when you are testing your own work, you have to be paranoid and envision your user as someone who deliberately tries to break your program. For example, lets say you create a text box which the user will use to enter only numeric data. You must address the possibility that the user will leave it blank or accidentally enter letters and incorrect characters in it.

That’s my basic process for creating VBA customizations. In a previous article, I listed some of the advantages of using VBA, so lets discuss some of the disadvantages.

Disadvantages of using VBA

First, you can’t accomplish everything in the VBA environment. Made2Manage’s own development team has indicated this to me several times over the years. Some of the customizations they do require modifications to the source code, which you and I can’t access.

Second, implementation of the VBA can be very inconsistent. Those who are already accomplished programmers with Visual Basic with other programs will realize that some Visual Basic commands simply will not work in the M2M VBA environment. Also, some of the events like BEFORESAVE behave inconsistently on different Made2Manage forms. I’ll point out specific examples in a future post.

Third, VBA must be installed on every workstation that needs to run the customizations. While the new VBA code can be retrieved from the server each time a user logs in, to install the product you must literally touch each machine. In addition, you may need to update the VBA program file each time you migrate your M2M Version. This can be very time consuming if you support 50 or more users.

Fourth, VBA for Made2Manage tends to hide mistakes in your code. The program will attempt to go past code with syntax and other errors in an attempt to hide your mistakes from the user. This may sound like a great feature, but let me assure you that it is in fact one of VBA’s biggest problems. Since VBA masks many errors, they can be exceedingly difficult to find and correct.

Finally, you have to be very careful when performing customizations as you could possibly damage your data. Obviously, Made2Manage does not support customizations that they haven’t written, and it will cost you dearly if you need to hire them to fix your database. Until you are very confident of your skills in VBA and SQL, do not write directly to your database. Restrict yourself to screen and pre-save data validation and leave database access to Made2Manage itself.

In my next article I will demonstrate a relatively simple VBA customization and how I step through it.

Code you write is twice as sweet as code purchased.

Color Of Money

“Money won is twice as sweet as money earned.” – Paul Newman

At this point you may be asking yourself, “David, why should I bother learning all of this stuff? I don’t have the slightest idea how to write VBA (Visual Basic for Applications) code.”

Why Learn VBA?

There are many reasons to learn VBA.

First, if you are a geek like me, you actually enjoy learning and using code. I first learned the term Homo Logicus while reading Jeff Atwood’s excellent blog Coding Horror. I’ll paraphrase and describe it as someone with an insatiable need to understand how things work. Sometimes I can’t believe people pay me to work on puzzles all day long.

Second, VBA is ubiquitous and applies to many functions of an administrator’s job. Microsoft Office is a completely different world when you incorporate VBA programming. You’d be amazed at what you can do with it in Excel.

Third, unlike many other add on products for Made2Manage, VBA is absolutely free. You can install it from your Made2Manage install CD and assuming you’re on support you can download the latest version from M2M Expert .

Finally, let’s assume that you’re not one of the tribe of Homo Logicus and you don’t find coding fun. You should still learn to do your own customizations or hire someone on staff to do so. The reason is accountability. Many years ago, before I had VBA experience, my employer required a customization. It should have been fairly simple, but rather than working through the problem myself, I deferred to an “expert.” I want to make it clear that this person was not a Made2Manage employee, but they were a certified solutions provider. Essentially, the customization was supposed to update all line items of the sales order with the correct ship to address and ship date at a single click. Anyway, when we received our VBA customization, it worked but it took 30 seconds for a process that I felt should have taken less than 5. I reviewed the code and found it riddled with problems. After being shown his mistakes, the contractor fixed them and sent us another bill for the corrections. Even after these corrections we were left with code we couldn’t use and were given a bill twice the size of the original quote.

Moral of the story, if you’re a Made2Manage Administrator take the online classes, post in the forums, ask for sample code, and view later posts to this blog for help.

Paying for code is not nearly as satisfying as creating it yourself and seeing it work. Also, if you created the code yourself and a problem occurs in testing or even in the production environment, you are in a much better position to fix it if you wrote it yourself.

What if I don’t have the resources to hire a Homo Logicus?

Then if you must customize Made2Manage you will have to contract with someone to do it. I’ll give some pointers as to how to pick someone, how to draft a statement of work document, and proper testing procedures in a future post.

Also, I will provide the code I wrote to accomplish the customization I listed above as well as explanations for how the VBA works in a future post as well.

Are you willing to wear the white belt?

Kano

As I mentioned in my first post, I am an Aikido Instructor and I am fond of sharing notable quotes with my students. One of my favorites is the story attributed to Jigoro Kano , the founder of Judo. As it was told to me, on his death bed Kano, the founder and therefore best Judo practitioner of his time, called together his top students and asked to be buried in his white belt. Initially it was assumed that this was his last act of humility, but later scholars came to see it for what it really was.

Kano was dying and knew that his next adventure was entirely new to him as it is to all of us. He was prepared wearing the white belt of the beginner.

As I related this story to my class, one of the students asked me why I was not living up to this ideal. I was in the same position with the same company for 10 years. I had mastered that position and was stagnating due to lack of challenges. Through the assistance of some Made2Manage friends I located another employer near Dallas with an entirely new set of challenges and I put on my white belt. This blog itself is another opportunity to wear the white belt.

Anyway, my point is that all of us should be willing to risk, to try new things, to tackle new challenges especially if there is a good chance we will fail. If you haven’t tried VBA (Visual Basic for Applications) before and you want to modify the way Made2Manage works, give it a try. If you’ve never created and changed Made2Manage reports because it’s confusing, ask for help and then try.

Are you going to stagnate and do business as usual, or are you willing to wear the white belt?

What can Made2Mentor do for You?

PASS Summit 2012 Smaller 2

Who are you?

I am currently an Independent Consultant, and I specialize in Data Warehousing, Dimensional Modeling, SQL Server Integration Services (SSIS), Azure Data Factory, Business Intelligence Markup Language (Biml), and technical training. I’ve been working with SQL Server for well over 10 years gaining a mastery of the full Microsoft Business Intelligence Stack. This, bolstered by my teaching experience and Bachelors Degree in Education, is why I enjoy sharing my passion for Data Warehousing so much.

I’m highly active in the PASS Community on all levels as I frequently present at SQL Saturdays and SQL Server User Group Meetings. I’m very active as a PASS volunteer as well.

Why do you blog?

As I mentioned earlier, I have a passion for Data Warehousing and Analytics. However, I enjoy connecting with people even more so. I’d like to invite you to read my posts, watch my videos, and learn with me. Note that I said “With” because I love learning and I probably learn more from the blogging process than my readers.

I also use this blog to document the process of my work.  I find that if I don’t write notes about what I’m doing, then I have to re-invent the wheel every time a similar challenge presents itself in the future.

What can I do for you?

I solve problems. If you have some, I offer a wide array consulting services with an emphasis in the following:

  • Data Warehouse Design/Dimensional Modeling
  • SQL Server Integration Services
  • Biml Scripting
  • SQL Server Development
  • SQL Server Administration and Disaster Recovery

If you need someone with experience to help you learn any of the above skills, I’d be glad to help. I also have an extensive professional network which includes the very best practitioners in every field of data science, so I can always find the help you need.

I also enjoy connecting with people and introducing them to the PASS Community.

How can I contact you?

Feel free to contact me through any one of the following: