Author Archives: richardmoyer

7 Reasons Why You Would Use Access Database VBA In Your Application

(While this articles talks about MS-Access VBA, the same points also apply to Excel VBA) –rjm

Welcome to the world of Microsoft Access Database VBA programming which VBA stands for Visual Basic for Applications (the application being of course a Microsoft Access) and is the technology; languages used to program and automate your database application

It’s not exclusive to Access but it can and is available with other Microsoft Office applications too including Microsoft Excel, Microsoft Word, Microsoft Outlook to name a few!

Access VBA has the power to communicate with other applications beyond the Microsoft Office product range and can talk to other Window applications and across other platforms too.

So, learning the principles of VBA using Access as the tool will stand you in good stead for the other applications should you wish to program and code with them in the future.

The only difference between other applications when wanting to use VBA will simply be learning to load and work with different libraries and calling classes.

I have listed 7 reasons why you would use Microsoft Access VBA programming in your database and are as follows:

1) Manage smaller re-usable procedures (globally) keeping code easy to edit and maintain emulating the business workflows.
2) Allows you to create your own user defined functions which return values as if they were pre-build in Microsoft Access.
3) Hold variables in memory (locally and globally) which speeds up your routines and passes values between different processes with ease.
4) Easily attach code to an event i.e. a button on a form or automatically trigger itself via an event.
5) Manage more powerful procedures that Microsoft Access macros lack or is limited by.
6) Communicate with other applications and platforms increasing the seamless power of Access VBA.
7) Gracefully handle errors (when they happen) and will happen from time to time allowing you flow and manage unexpected exceptions.
You do not need to be an Access programmer to learn VBA (but it helps if you have some knowledge). The more important factor in order to successfully program with Access is to be knowledgeable within the framework of Microsoft Access generally (namely tables, queries, forms, reports and macros) and know its powerful features to identify where VBA should take over and improve the functionality.

Author Unknown
Edited by Rich Moyer

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Excel VBA Training Beyond The Macro Recorder

For anything beyond simple tasks, Excel VBA training is required.  Excel VBA training involves a much deeper understanding of the Excel Object Model than a user ever sees when simply recording macros.

For most users, the first exposure to Excel Visual Basic for Applications (VBA) involves recording a macro. Just like a voice recorder, when the Excel macro recorder is started, as the user carries out a series of steps (for example opening a workbook going to a particular worksheet, copying some data, and so on) Excel faithfully records each step by generating the necessary Visual Basic code. Each time the user plays back the macro, these same steps are performed exactly as they were recorded.

When Recorded Macros Are Appropriate

Recording macros is ideal for really simple tasks such as producing an extremely strait-forward report. However, there are distinct restrictions on this approach. Because Excel plays back the steps just as they were originally performed, recorded macros are often painfully slow.

Recorded macros will only run properly under the conditions in which they were originally recorded. For example, if a particular worksheet needs to be active at a certain point and it is not active when the macro is played back, an error will occur.  This means that recorded macros are only of use to the person who records them. It is unlikely that they could be distributed to work colleagues.

Formal Excel VBA Training

One of the first things we do when we run an Excel VBA training course at our London training centre is to attempt to wean users off the recorder. We provide them with a good grasp of the Excel object model, a way of programmatically representing all of the elements that make up the Excel environment such as cells, workbook and worksheets. True, there is a bit of a steep learning curve for those users who have done little or no programming. This is one reason why we run a five day intensive Excel VBA training course for new users. We find that it gives everyone a chance to gain confidence and familiarity with this challenging environment.

After weaning users off the macro recorder, we explain to them the syntax and structure of VBA. They learn how to use variables to store both data and references to Excel objects, how to code logical and iterative structures and how to allow the person running a macro to choose between different options.

Despite its initial complexity, attending an Excel VBA training course is a good investment. It can greatly enhance the productivity of an Excel user. Monthly operations and reports which used to take hours can suddenly be streamlined and accomplished with astounding rapidity.

Getting trained on Excel VBA is the only real way of losing one’s initial dependency on the macro recorder. However, the recorder will always have its uses. For example, when one is working with an Excel object or procedure which is programmatically complex, recording a few steps then examining code generated is an excellent way to learn new syntax.

By: Andrew Whiteman

Article Directory: http://www.articledashboard.com

The writer of this article is a developer and trainer with Macresource Computer Solutions, an independent computer training company offering Microsoft Excel VBA training courses in London and throughout the UK.


Excel VBA Made Easy And Visual Basic 6 Made Easy are E-books written to help you learn Excel VBA And Visual Basic 6 easily. The e-books are popular among the students of both online tutorials.

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Excel VBA Programs and IT Requirements

I am a big believer in understanding the impact of actions and decisions as related to corporate IT requirements for any software programming or automation, including Excel VBA Programming. The article entitled, “How the Capabilities of an Excel Programmer Can Help Your Business” at ExcelVBAWizard opens with the exclamation:

If you’re an Excel super-user who has finally outgrown a few of those Excel built-in worksheet abilities, and you’re wishing that they could accomplish even more, consider hiring someone with the capabilities of an Excel programmer to take your spreadsheets to the next level! Just tell a programmer what worksheet functions and results that you want, and you’ll be able to sit back and relax as they do the rest of the work! They will take it from there. They will also create an Excel add-in that you can install in Excel, and that you can distribute to others if you want. You can even sell it!

Don’t get me wrong, I LOVE Excel, and use it almost daily. But I also believe that you must use the right tool for the job, and in a corporate setting, it may be not only inappropriate to use Excel as the platform to support your business process, but it may be costly to the business, and may even be illegal.

Customer Requirements, Business Requirements, IT Requirements

As with any project, one of the more difficult tasks lies with you, the Business Unit client, in providing the details of what you want to accomplish from a business process perspective. Your customer has requirements for the information they need from you and the business process used to support them. For example, your customer know the information he needs as “Invoice” You, as a business unit, must translate the customer requirements into terms that you use within your business process, for example, External Customer Billing Document.

Those business process requirements are in terms that the people associated with your business function know and use daily, but are not in a language that an IT programmer would understand. The business process requirements must then be translated into IT requirements, meaning an IT professional who knows both the business lingo and IT semantics must then convert those business requirements into IT requirements. The IT programmer then translates those IT requirements into Specifications, and Code.

Business People Define Business Requirements

The mistake is frequently made when the business representative says, “I want you to use function XYZ to do ABC”, when in fact the business person needs to tell the IT consultant, “I need to have a program that solves this business problem, shortens the time it takes, and eliminates the possibility of errors”.  IT can then properly design the program so that it meets IT requirements, is portable and upgradeable, and is included in any infrastructure upgrade plans.

The IT Consultant Translates Business Requirements to IT Requirements and Specifications

It is the IT consultant who is responsible for writing the IT requirements and specifications so that the IT analyst/designer/programmer (this can be one person, or a whole team for a larger project) understands what the system or program is to accomplish. IT needs to establish what capabilities can be automated, on what platform, within what budget constraints, and include all the security, supportability, documentation, training, and data integrity aspects of the project.

Real Life in IT

Of course, you can just tell the programmer to do what you want, but the likelihood of having that program meet the IT requirements is small. I have seen this thousands of times:

  • The business user dictates what they want to a free-lance programmer who then creates a one-off program we will call Program Alpha that can be used by one workgroup.
  • Another workgroup sees the program, but needs slight changes to be able to use it for their specific business function, so another version of Program Alpha now becomes Program Beta, and so on.
  • Now, the environment is flooded with many different versions of Program Alpha, and by the way, the original programmer was dismissed after implementing the first project.
  • All of these programs were based on a version of baseline software we will call Infrastructure 1.0. Now, IT has to make changes to the systems, desktop images, network, or other something-or-other in the nebulous “IT Cloud” that was defined in the IT Strategy, and called Infrastructure 1.1.
  • The problem is that Program Alpha and all its offcast versions are not compatible with the IT requirements of Infrastructure 1.1, and do not work. IT does not know about this Infrastructure 1.0 dependency, because IT was not engaged in the requirements and specifications of the original Project Alpha, likewise, IT could not include Project Alpha iterations in the application and system tests conducted for Infrastructure 1.1.

Calls to 911

So, the day that Infrastructure 1.1 goes live, the IT  Help Desk gets flooded with many irate business people saying that their application, Project Alpha, etc. does not work. The Help Desk has no record of a Project Alpha in production, has no documentation on the product, has no programming resources that know about the requirements and development of Project Alpha, and has no budget to get these issues resolved. It is very likely that no backups were taken of Project Alpha, and the SQL code to query the corporate systems is no longer compatible with the new database version included in Infrastructure 1.1 IT requirements.

Finger Pointing, Mudslinging, Escalation

This business unit is obviously affected by the ability for Project Alpha to work in the new Infrastructure 1.1 environment. This now becomes an emergency for everyone involved:

  • The Business Unit  can no longer do their jobs
  • The Business Unit Management  must escalate this problem to IT Management
  • IT Management now forces a mad scramble in IT to find information about Project Alpha. No budget or resources were allocated to this project, nobody in IT knows anything about Project Alpha, and there is no documentation, backup, or database recovery specified.
  • All users must sustain a system outage because Infrastructure 1.1 must be rolled back to Infrastructure 1.0 configuration.
  • The Customers (for whom the Business Unit is using this data) are left without the necessary data to run THEIR business.
  • Regulatory  The use of mission critical data in spreadsheets must be controlled. Your company may be in violation of SEC and other regulatory statutes for not complying with the established IT security, data integrity, backup, and recovery criteria.

While this represents the worst case scenario, before putting mission critical data into a self-maintained system of any kind, you need to understand all of those “overhead” things and IT Requirements that make IT development so expensive and time consuming. You need to ask yourself the questions:

  • Can I live without this function?
  • What workarounds must I implement to do the same thing?
  • Can my customers live without the data provided by this function?
  • What is the cost to the business to lose these customers?
  • Will I be able to find a job elsewhere because I lost these customers?

Rich Moyer is Managing Partner (retired) of Spaho Consulting

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Create An Excel VBA UserForm

Create An Excel VBA UserForm

OK, so let’s begin. We will create an Excel VBA UserForm, an Excel Trial Dialog  with information icon and timer enabled OK button, and then add in the VBA Code.

  • First Open Excel and press ALT+F11 to enter the VBA Editor.
  • Go to the File Menu and choose Insert–>UserForm.  A window will open with a blank palette for the Excel VBA Userform
  • Name the new UserForm MsgBoxCountdown and set its Height property to 132 and its Width property to 242.
  • Add one Label to the Excel VBA UserForm and name it lbTrialMsg
  • Set its Caption property to This Message only appears in the Trial Version of XXXX (without the quotes) – this will be our Trial or Nag Message to the user. We will prefix this Caption with a passed in bespoke Message.
  • Add one further Label to the Excel VBA UserForm and name it lbCountDown
  • Set its Caption property to This Trial Dialog can be closed in (without quotes) – this will be our Countdown Message to the user.
  • Now add an Image Control and load in an image resembling a Question Mark – you can easily create or adopt an image for this purpose.
  • Finally add a Command Button and name it btnOK, set its Caption to OK – this will be the button that will be disabled until the timer interval has elapsed and the user is allowed to press it.

Tip: Why not add a Frame Control just over half the Height and the full Width of the Excel VBA UserForm to hold the lbCountDown Label and btnOK Button and then set the BackColor of the UserForm itself to &H80000005& – this divides the UserForm giving it a really pleasing aesthetic quality

You should now have a nice looking Excel VBA UserForm and be in a position to add in the VBA Code.

Double-click your OK Button to enter the VBA Code Editor for the UserForm Module. Amend the Subroutine generated to the following Code – you can paste over it if preferred:

'=================================
' btnOK_Click, closes the UserForm
'=================================
Private Sub btnOK_Click()
Unload Me
End Sub

Now add the following Code at the top of the Module – these are the Windows API’S we will be using to re-style the Dialog Window and the Interval Variable that we have set to 5 (seconds) before we enable the OK Button:

Option Explicit Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long Const WS_SYSMENU = &H80000 Const GWL_STYLE = (−16)
'=============================================================== ' Interval, set this to the time before the OK Button is enabled
'===============================================================
Private Const Interval = 5

OK, next we will add the UserForm QueryClose Event Handler. This can be used to simply trap a Close attempt on the Red Cross if you don’t want to remove it when we get to the UserForm Activate Event Handler in a minute. So add the following Code:

'================================================================'
UserForm_QueryClose, workaround if you don't want to hide the Red Close Cross
'================================================================
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error GoTo QueryCloseErrorHandler
Application.EnableCancelKey = xlErrorHandler
If CloseMode = 0 Then Cancel = True
MsgBox "Oops, the X in this Dialog has been disabled, please use the OK Button on the form", vbCritical, "Kiosk 4.1"
End If
Exit Sub
QueryCloseErrorHandler:
Resume Next
End Sub

Nearly there, now we need to add in the UserForm Activate Event Handler Code. This is the work-horse of the VBA Code and I will go through it in a minute. Anyhow, add in the following Code (you can please yourself whether you add in the comments):

'================================================================
' UserForm_Activate, strange little error handling routine, it goes...
' − style the userform to remove the red cross ' − add a restart point for hack attacks
' − set up an error handler & tell Excel to use it ' − disable the OK Button
' − start the countdown
' − on hack (CTR+Break) goto restart − that will begin the whole process over
' − if time up enable OK Button, OK will Unload the UserForm
' − in the interim DoEvents will allow you to still move the Dialog
'================================================================
Private Sub UserForm_Activate()
On Error Resume Next
Dim hwnd, lStyle As Long
hwnd = FindWindow("ThunderDFrame", Me.Caption)
lStyle = GetWindowLong(hwnd, GWL_STYLE)
SetWindowLong hwnd, GWL_STYLE, lStyle And Not WS_SYSMENU
Me.lbTrialMsg.Caption = Me.Tag & Me.lbTrialMsg.Caption

restart: err.Clear
On Error GoTo TrialErrorHandler
Application.EnableCancelKey = xlErrorHandler
Me.btnOK.Enabled = False Dim t As Single
t = Timer
Do DoEvents
If err.Number = 18 Then GoTo restart
If Round(t + Interval − Timer, 0) > 0 Then
If err.Number = 18 Then GoTo restart    Me.lbCountDown.Caption = "This Trial Dialog can be closed in " & Round(t + Interval − Timer, 0)
Else
If err.Number = 18 Then GoTo restart
Me.lbCountDown.Caption = ""
End If
Loop
While t + Interval > Timer
Me.btnOK.Enabled = True
Exit Sub
TrialErrorHandler: Resume Next
End Sub

The first part of the Code tells Excel that if it hits an error to skip over regardless of what error has occurred. Not normally good practice but we do not want the Excel Debug box to ever be made available to a user.

We then style the Dialog Window to remove the red cross Close Button using the Windows API calls. NB: For Office 2000 and later we use the class name ThunderDFrame (for Office 97, it isThunderXFrame). The lbTrialMsg Caption is then set to include the UserForm Tag message that we set before we show the UserForm plus the message we set earlier. In other words we can call this UserForm from anywhere in our Application passing in a Key Relevant Message that is prefixed onto our pre-set one.

We then add a Goto point called restart: This will be where we jump to when a user presses the CTRL+Break Keystroke combination. This also sets up Excel to use another Goto point for our Error Handling and then tells Excel that we wish to only use that point for all errors whatever they may be.

Next we disable the OK Button. We set the variable ‘t’ to the current Timer time and begin looping until our interval has expired – the interval variable was set to 5 (seconds) earlier. In the interim we use DoEvents to allow the Dialog to be moved around and the lbCountDown Caption to be updated with our Countdown Message. When the interval expires we then clear the lbCountDown Caption.

At the same time we still trap the CTRL+Break Keystroke combination and then enable the OK Button as the flow moves out of the Do loop. We then exit the Subroutine. Used in conjunction with some VBA Module protection you have a useful little Countdown Message / Nag Dialog.

If you don’t want to re-style the Dialog Window, simple remove the API’S and allow the UserForm QueryClose Event Handler to trap the Red Close Cross.

OK, finally, double-click the ThisWorkbook Module and enter the following:

Option Explicit
'================================================================
' DemonstrateMsgBoxCountdown, run this to see the Countdown Dialog '================================================================
Public Sub DemonstrateMsgBoxCountdown()
MsgBoxCountdown.Tag = "(YOU CLICKED A FEATURE):"
MsgBoxCountdown.Show
End Sub

OK, now choose Debug–>Compile VBAProject to compile your Code and check for any errors. Click anywhere in the Code you entered above and press F5 to Run the Sub/UserForm.

You should see your UserForm appear. Try pressing Ctrl+Break and watch the Timer interval increase back up to 5 seconds.

That’s it.  I hope you liked this article on Creating an Excel VBA UserForm.

Mark Kubiszyn

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Create an Excel VBA Add-In

Why Would I Want to Create an Excel VBA Add-in?

If you have created VBA code and want the sub procedures and functions to be available in all existing and future workbooks you create, then the best way to access your VBA code is to create an Excel VBA Add-in. An add-in for all intents and purposes, is a regular Excel Workbook with VBA which has a xlam (or .xla in Excel 2003 or earlier) file extension.

Once saved and installed as an Add-in, the workbook is automatically opened and hidden so the VBA procedures and functions are accessible to all other workbooks. The Add-in is hidden in a special way so that it can only be seen from the Project Explorer in the Visual Basic Editor (VBE) and can not be unhidden from within Excel.

Add-ins are an ideal way of distributing VBA code. Excel comes with Add-ins that are not installed by default like Analysis ToolPak, Solver etc.

Lets assume that you have created an Excel workbook and have created a VBA module with numerous User Defined Functions (UDF) and Sub Procedures. This workbook must now be saved as an Excel VBA Add-in.

From within Excel, select Save As from the Office button. Save the file with a descriptive name and as an Add-in file type which will have a .xlam file extension for 2007 and .xla type for 2003 and earlier. The file location will change to a dedicated Add-in folder.

You can save the Excel VBA Add-in here or choose a different location.

Close all open workbooks and re-start Excel.

To make this function available to all workbooks, the Excel VBA Add-in must be installed.

In Excel 2007 select the Office button and select the Excel Options button.

From the Option categories down the left, select Add-ins.

In the drop down box at the bottom of the window select Manage: Excel Add-ins, then click Go. The Excel VBA Add-ins dialog box is shown with a list of all available Add-ins. If you saved your Excel VBA Add-in in the dedicated folder as above, the Add-in should appear in the list. If you saved your Excel VBA Add-in elsewhere, click the Browse button and navigate and select the Add-in file, making sure the Add-in is ticked in the Available Add-in list.

Once the Excel VBA Add-in is installed, its functions and procedures are available to be executed in the normal way. To make changes to the Excel VBA Add-in in the future, you can access the VBA code from the Project Explorer in the Visual basic Editor where it appears as a project. Don’t forget to save the changes.

It is advisable to password protect the VBA code so it cannot be viewed or modified by unauthorised users. To apply a password to the Excel VBA Add-in, right click the Add-in located in the Project Explorer of the VB Editor and select VBA Project Properties. On the Protection tab, tick the box to ‘Lock project for viewing‘ and enter & confirm your password. Once saved, the protection will take effect once Excel is restarted.

If your Excel VBA Add-in contains procedures or macros, these can be added on to the Quick Access Toolbar. Click the down-arrow to the right of the Quick Access Toolbar and select ‘More Commands‘. In the ‘Choose commands from‘ box select ‘Macros‘. From the list of available procedures, select the procedure to add to the toolbar and click ‘Add‘. To change the appearance of the button, click the ‘Modify‘ button. In the displayed dialog box, select an alternative icon and modify the procedure’s display name.

Author Unknown.

Edited by Rich Moyer

Rich Moyer is Principal Consultant (retired) of Spaho Consulting

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Cannot Compile Excel VBA Project

Cannot Compile Excel VBA Project

by Addison Philip in Computers / Data Recovery    (submitted 2009-08-17)

Microsoft Excel allows you to extend its functionality beyond its defaults by the use of programming language Microsoft Visual Basic. The VBA code in this environment is written, changed, compiled and viewed in the VBA editor. The VBA editor organizes and shows all the VBA modules in VBA projects. Actually, each time when you create a new workbook, a VBA project gets automatically created and attached with it.  Occasionally, you cannot compile the VBA code.

At times, you find difficulties while compiling a VBA project associated with a workbook. The odds are that the workbook is corrupted. Thus, to restore the corrupted workbook, you will need to use your current backup. Alternatively, you can also opt for scanning the damaged workbook by using an Excel Recovery application .

For an instance, after you edit an existing VBA project, when you try to compile it, you get the similar error :

“AppName: excel.exe AppVer: 9.0.0.2719 ModName: vbe6.dll

ModVer: 6.3.91.8 Offset: 00083676″

This behavior occurs irrespective of changes made to the project.

Cause

There can be several causes that might prevent a VBA project from being compiled. Some of them are listed below:

  • Bugs in code
  • Code size limitation issues
  • Use of default names for macros
  • One or more modules are corrupted
  • Current workbook is corrupted

Solution

You need to try following suggestions to solve the issue described above:

  • Try running the code without compiling by using Debug modes in the VBA Editor. This will help you to detect the code bugs, if any
  • You should try deleting one or more functions.
  • If the error is from naming conventions issues for modules, you need to copy the code into modules and name them differently.
  • Try creating a new VBA project and copy the modules and functions one at a time. This will help you to detect the corrupted modules. Delete the corrected modules, if any.
  • You should attempt to import the code to a new workbook to compile it. If it gets compiled and runs successfully, the current workbook is corrupted. Use backup to restore the file.

If you lose data while no clean backup is available, scan the damaged workbook by using an Excel Repair tool. Excel file repair software can repair the damaged workbook by using powerful algorithms while providing graphically rich user interface. Stellar Phoenix Excel Recovery is a comprehensive tool to repair damaged Microsoft Excel files. It supports Excel repair for Excel 2010, 2007, 2003, 2002, and 2000. It can restore all file objects, such as hyperlinks, formulas, charts, pivot tables, macros etc. This non-destructive software is compatible with Windows 7, Vista, XP, 2003, 2000, and NT.

About the Author

Addison Philip works as a freelancer and researcher for  xls repair &  Excel file repair software. Use and distribution of this article is subject to our Publisher Guidelines whereby the original author’s information and copyright must be included.

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Record a Macro and Test in Excel 2007

If you don’t know anything about creating macros with Excel Visual Basic for Applications (VBA), where should you start? In this article you’ll take these first steps in using Excel VBA: Record a Macro.

  • Understand what Excel can do without macros
  • Think of a simple, repetitive, Excel task that you have to repeat every day
  • Use the Macro Recorder to automate that simple routine

What Can Excel Do Without Recording a Macro?

Excel can do all kinds of amazing things, without macros. Get to know Excel’s powerful built-in features, such as:

  • Conditional Formatting
  • Data Validation
  • Pivot Tables
  • AutoFilters and Advanced Filters

If you use those built-in features, you might not need to record a macro. For example, instead of checking each cell in a column, and manually coloring the cell green if it’s over 50, use conditional formatting to highlight the cells automatically.

Identify an Excel Task to Automate

If you use Excel every day, you probably have a few tasks that you repeat daily, weekly or monthly. To get started with Excel VBA, you could focus on one of those tasks, and try to automate it: Record a Macro.  In this example, you have a list of stationery orders, for binders, pens, and other items, in a workbook named Orders.xlsx. You can create a small sample Orders file, with columns for Sales Date, Item Sold, Quantity, Unit Price and Total Cost. Every day, in your imaginary job, you open that Orders file and filter the list of orders, to find all the orders for binders. Then you copy the orders, and paste them into a new workbook. Here’s a summary of the steps that you might follow every morning:

  • Open the orders file
  • Filter the list for binder orders
  • Copy the binder orders
  • Create a new workbook
  • Paste the binder orders into the new workbook.

Instead of doing that task manually every day, you could automate it, by using Record a Macro to create the Excel VBA code, which is also known as a macro.

Get Ready to Record a Macro

Now that you’ve decided to automate this task, you’ll use Excel’s Macro Recorder tool to create the Excel VBA code. Before you start recording, you’ll get everything into position. For example:

  • Do you want the macro to open a specific workbook, or will that workbook already be open?
  • Should you select a cell or worksheet before the macro runs, or will selecting the cell be part of the macro?

In this example, you want the macro to open the Orders workbook for you, and then filter and copy the data. So, the Orders workbook should be closed when you start recording. You don’t need to select a specific cell or worksheet before recording; any cell selection will be done during the macro recording.

Start Recording

Once everything is in position, you can get ready to start recording.

  •  Open a new blank workbook, which is where you’ll store the macro.
  • At the bottom left of the Excel window, click the Record Macro button.
  • In the Record Macro dialog box, type a one word name for the macro, CopyDailyRecords
  • From the ‘Store Macro In’ drop down, select This Workbook as the workbook where you’d like to store the VBA code. Later, you can open this workbook every morning, to run the macro.
  • In the Description box, you can type a brief note about what the macro will do. This is optional, and you can leave the Description box empty, if you prefer.
  • Click OK, to start recording.

Perform the Macro Steps

While the Macro Recorder is on, you’ll perform the steps that you want to automate. In this example, these are the steps that you should do now:

  •  Open the orders file – Orders.xlsx
  • On the Data sheet in the Orders file, use an AutoFilter to view the binder orders
  • Copy the filtered binder orders, including the heading row.
  • Create a new blank workbook
  • Paste the binder orders into the new workbook, in cell A1 on Sheet1.

If you make a mistake – no problem! Just stop the recording (see the instructions below), close the files without saving, and start over again.

Stop the Recording

Once you finished all the steps, follow these steps to turn off the Macro Recorder and save the macro file.

  • Click the Stop Recording button at the bottom left of the Excel window.
  • Close the workbook where you pasted the binder orders, without saving the changes.
  • Close the Orders.xlsx workbook, without saving the changes
  • Save the workbook where you stored the macro: Name: BinderCode.xlsm; File Type: Excel Macro-Enabled Workbook *.xlsm
  • Close the BinderCode.xlsm file.

Get Ready to Test the Macro

To prepare to test the macro, make sure that the Orders.xlsx workbook is closed.

 Add the Developer Tab

To run the macro, you’ll use the Developer tab on the Excel Ribbon. You can add the Developer tab to the Excel Ribbon, if it’s not there already:

  •  Click the Microsoft Office Button, and then click Excel Options.
  • Click the Popular category, and add a check mark to Show Developer tab in the Ribbon
  • Click OK, to close the Excel Options window.

Macro Security Settings

If you haven’t run macros before, you might need to change your macro security level. (You may have to clear this with your IT department.)

  •  On the Ribbon, click the Developer tab, and in the Code group, click Macro Security.
  • In the Macro Settings category, under Macro Settings, click Disable all macros with notification
  • Click OK.

If you changed the setting, close the workbook, and then reopen it.

 Test the Macro

Now that the Developer tab is visible, you can get ready to test the macro. First you’ll open the file where the recorded macro is stored, and enable macros, by following these steps:  Open the file (BinderCode.xlsm) where you stored the macro that you recorded.

  • If a security warning appears at the top of the worksheet, click the Options button.
    • Click Enable This Content, to allow the workbook’s macros to run, and click OK.
  • Run the Recorded Macro

Now, follow these steps to run the macro, to see if it works the way you want.

  • On the Ribbon, click the Developer tab, and in the Code group, click Macros.
  • In the Macro dialog box, click the macro that you want to run – CopyDailyRecords.
  • Click Run.

If you see an error message, click the End button, and try recording the macro again. If everything went as expected, great! You can close all 3 workbooks used by the macro, without saving the changes.

Try Recording Another Macro

Now that you know the steps to record a macro and testing a macro in Excel 2007, find another task that you would like to automate, and record the steps, to create another macro. You can save hours every week, by automating your Excel tasks, whenever possible.

Author Unknown.

Edited by Rich Moyer

Rich Moyer is Principal Consultant (retired) of Spaho Consulting

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Comparison of Access Visual Basic and Excel VBA Macros

 A Comparison of the MS Office Programming Languages

There is Visual Basic and Visual Basic Express, which are full Integrated Development Environments,  but within each of the respective Microsoft Office applications, there are  Visual Basic Applications (VBA) languages.  This tutorial looks at two particular Office programs: a comparison of Access Visual Basic and Excel VBA Macros

Similarities between Access Visual Basic and Excel VBA

In theory it should be easy to switch from one MS Office VBA programming language to another, since all the underlying principles are the same. It doesn’t matter whether you’re writing macros within Access Visual Basic and Excel VBA, Outlook, PowerPoint, Visio, or Word, you’re still using:

  • objects, collections, methods and properties
  •  variables (the DIM statement)
  •  IF conditions and loops
  •  message boxes and input boxes
  •  subroutines and functions

In fact, the only thing which is different is the object and collections defined within the application. So how hard can it be to switch from (say) Excel VBA to Access VBA? The answer, sadly, is… fairly hard. Here’s why!

Differences between Access Visual Basic and Excel VBA

As we explored the VBA languages associated with MS-Access and Excel, our Comparison of Access Visual Basic and Excel VBA Macros revealed that the main differences between the two programming languages are that Access supports two macro languages, doesn’t support recording, has two different ways to create Visual Basic macros, hides a lot of its functionality in the DoCmd object and has two separate object models. If that didn’t make much sense, worry not – the rest of this article will explain each of these points in turn.

Access has Two Macro Languages

This is a red herring. Microsoft Access has two separate languages for writing macros: one called macros, the other called either modules or Visual Basic. If you have any knowledge of VBA, you should ignore the first; it is provided for people who have no programming experience, and doesn’t support proper looping, error-handling and many other structures. So although the Access database window contains a MACROS tab, you should ignore this completely!

Access doesn’t Support Recording

Want to know how to colour a cell red with pink spots in Excel? If you can’t guess the macro command (unlikely), you can just record a macro and have a look at the resulting code. This is a vital aide-memoire even when you’re a VBA guru. Access, on the other hand, doesn’t support recording – not even in the latest version at the time of writing, Access 2010. This is, to say the least, a shame (interestingly, while Word supports VBA recording, PowerPoint doesn’t any more: Microsoft removed the facility from version 2007 onwards). This means that you’re often forced to resort to Google, Microsoft help or phoning a friend to find out how to do something in Access VBA.

Two Different Ways to Write Macros in Access

Want to create a VBA macro in Access? To do this, you need to go to the VBA code editor. Strangely, you can do this in two different ways: either press ALT + F11 as normal, or click on the Modules tab in the database window and choose to create a new module. Why are there two different ways to do the same thing? Historical reasons, we think.

  • Access Uses the DoCmd Object for Many Commands
  •  Access VBA is complicated (or simplified?) by the fact that about half the commands begin with DoCmd. For example:
  •  DoCmd.OpenForm – to open a form
  •  DoCmd.Maximize – to maximise a window

Features like this make Excel a far more logical programming language than Access.

Access has Two Separate Object Models

Excel is pretty self-contained (although if you’re creating your own dialog boxes, you’ll be using a separate applications called Microsoft Forms). Access, however, splits into two almost equal parts:

  • tables and queries are part of the Access database engine
  • forms, reports, macros and modules are part of the Microsoft Access application

Although you’ll probably only hit this complication when you get into advanced programming in Access, it’s another Access feature to muddy the VBA water. In summary, in our comparison of Access Visual Basic and Excel VBA Macros, we’d say that Excel macros are far more straightforward than Access ones. Added to this is the fact that it’s much quicker to learn Excel than it is to learn Access, and you get two unequal learning curves!

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

What Is Microsoft Excel Part One

Microsoft Excel is a powerful spreadsheet application, or workbook. An Excel workbook is a computer file that allows a user to enter a series of numbers, along with other data. The purpose of the Excel application is to allow users to collect like data in a specific location for current and/or future use.

There are a number of spreadsheet applications, one being Microsoft Excel. Lotus 123 was one of the dominant spreadsheets back into the 80s, before Excel w was released. Excel came out in 1984-1985. It was actually written for the Apple Macintosh. There are other spreadsheet programs out there, but Excel and Lotus are the ones most widely used.

Most spreadsheets strive for compatibility with Excel or minimally, portability to Excel Virtually every business in the United States, if not the world uses Microsoft Excel. Either for the IBM compatible PCs or for the Apple computers.

Spreadsheets are so commonplace that my daughter learned how to use it in 5th grade. This means that Microsoft Excel will be used for years to come, if not decades, as long as Microsoft continues to makes enhancements to the application, staying ahead of the competition.

Do you use Excel? If so, when were you introduced to it? Do you use the PC version of Excel or the Apple version?

Do you also use Macros (VBA) with Excel?

Macros, made up of VBA (Visual Basic for Applications) are the backbone of Microsoft Excel. It allows users to automate portions of their work. For example if you usually copy and paste data from one sheet to another, numerous times, you can create a macro to do it for you, thus saving you time in the future. You can record the Excel macro or you can type the Excel VBA that makes the macro.

Recording Excel macros is the easiest way to start automating your tasks. You can open the VBA editor, and you can see the code VBA behind the macro. You can change one or more lines of code, thus altering your macro. The more times you do this the more you learn. And before you know it, you are an expert. It just takes time and effort.

There are so many good books centered around the use of Excel VBA that anyone can learn it. Google is also a great source. Just type in Excel VBA Help and you will find endless sources and resources to learn, most of them for free.

You can also hire an Excel VBA expert to help you with your files. While this costs money it does allow you to get results much much faster, often within hours or the same day. If not sure how to find an Excel expert, it is easy, just type Excel experts into Google and numerous pages will show up. Look at the first 1 to 5 Google listings and see if any of those are Excel consulting firms. If so, take a look at their sites. Now please note, you need more than an Excel expert, you need an Excel expert that knows Excel VBA.

If you use the Apple version of Excel you want to note that only a few versions of Excel for the Mac have VBA in them. Excel 2008 for example does not have macros, but Excel 2011 does. So if you want to automate Excel on your iMac or Mac Book Pro, make sure to get the 2011 version. Otherwise you cannot do any automation at all. If you run Parallels on your Mac you can run Windows, and then you can run the Windows version of Microsoft Excel. All except the free version support Excel VBA.

So there you have it, you now know what Microsoft Excel is. If you have questions regarding this article, or if you would just like more information, check out Excel Business Support for Excel VBA Help. A great site with paid as well as free resources on Microsoft Excel.

Author Unknown.

Edited by Rich Moyer

Rich Moyer is Principal Consultant (retired) of Spaho Consulting

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Creating Excel VBA Macros

How to Create Excel VBA Macros.

Have you used a voice recording?  Creating Excel VBA Macros can be that easy.  The main benefit of a voice recording device is that everything you spoke once can be repeated over and over again without you physically speak again. Your voice is on the tape. You can even be heard without you being there. The Excel Macro Recorder feature is similar. The difference is that you store Excel actions with Excel VBA Macros instead of your voice. These actions can be repeated again and again without your physical intervention for each step in Excel. For example: Select a given data region, insert a table, and create a Pivot Table

  • Select a heading of a given table and apply a particular formatting
  • Delete all the empty sheets
  • Delete all sheets except the current one
  • Arrange cells in a particular layout
  • And more…

An audio tape is a set of recorded sounds. A Macro is a set of recorded instructions.

What Kind of Instructions Can Be in an Excel Macro?

Actions like: changing the color of a cell, renaming a sheet, creating a table, sorting, filtering, etc. On a voice recorder, to reproduce your voice you hit the Play button on the recording device; on the other hand, to reproduce a set of actions on Excel you run a Macro. Your voice is recorded on a magnetic tape or digital memory, while your Excel actions are recorded in a text file, specifically in an Excel VBA Macros Sub procedure inside a text window called a “Module”. There is a hierarchy within Excel:

  • Workbook – the Excel file that contains Excel formulas, data, and charts
    • Sheet – a tab, or section of the Excel Workbook
      • Range – a single cell or group of cells
  • Form – the graphical screen display for Excel instructions and data
  • Module – an Excel container that holds Excel VBA instructions
    • Subprocedure or Sub – a section of Excel VBA code dedicated to perform certain actions.  Contains, data, instructions, calls to functions.  Does not return data or status to the calling program.
    • Function – a series of VBA lines of code that perform a particular task, and return data or status codes to the calling routine.
    • Object – The target of instructions in the Excel Libraries.  A container for Properties, Methods, and Events
      • Method – An activity that Excel is being told to do.  Examples: insert a cell, delete a worksheet
      • Property – an attribute of the object.  Data or parameters passed to or from an Excel SUB or Function that give direction to how that code runs.  Examples: color, name, size, location.
      • Event – A trigger that directs when the object is to run.  Example, a keypress, or mouse click are events.

How do you Record Instructions in a Macro?

If you are using Excel 2007 or 2010

  • Go to: View>Record Macro>Macro name:>Ok
  • Or click the status bar Record icon.
  • The Excel 2007/2010 status bar also indicates that Excel is in recording mode…

On Excel 2003

  • Go to: Tools>Macro>Record New Macro
  • And specify the Macro configuration (name, description, etc.)…
  • After pressing OK, the status bar indicates that Excel is in recording mode…

While this mode is activated, EVERYTHING you do on Excel will be recorded. This is not as good as it might appear; the same way your voice recording device registers your hesitations and background noise, the Excel recorder captures all your mistakes and incidental actions.

Editing Recorded Macros

This creates the need to edit recorded actions in Macros. Keep in mind that while Excel is in recording mode, its behavior is normal, with one exception: in the background, Excel creates a Module (Module1, Module2, Module3, ModuleN) and a Sub procedure with the name you specify on the “Record Macro” dialog. By default: Macro1, Macro2, Macro3, MacroN.

The Excel VBA Editor is opened with Alt+F11.  Locate the workbook in the Explorer panel on the left.  Within that Workbook you will have Sheets, Modules, and Forms.  Your recorded macros will be within one of the modules in the Modules organization. Make the necessary changes to the macro in the VBA Editor.  You can run the subroutine from there, or simply make and save your changes, then run the macro from within the spreadsheet window.

Author unknown:

Edited by Rich Moyer

Rich Moyer is Principal Consultant(retired) of Spaho Consulting

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.