Author Archives: richardmoyer

Utilizing Excel Object Methods In VBA Programming

When writing code in Microsoft Excel Visual Basic for Applications (VBA), you need to programmatically manipulate the objects inherent in Excel, such as worksheets, charts and pivot tables. In VBA, each of these objects has properties and methods which can be used to manipulate the object and make it do what you need it to. Properties are the attributes or characteristics that the object possesses, whereas Excel Object Methods are actions associated with the object. For example, a workbook has a “name” property and an “open” method.

Excel Object Methods

Excel Object Methods are a little more sophisticated than properties in that they normally, though not always, require expansion in order to get a particular object to perform a particular action. This expansion is provided through the arguments which the programmer has to supply when using the method. Thus, in order to exit Excel altogether, we would use the “quit” method of the “Application” object, thus: “Application.Quit” without supplying any arguments.

Parameters

If, by contrast, we want to open a workbook, we need to supply at least one parameter: a string specifying the workbook to be opened. As you write your code, Excel will furnish a useful prompt: having entered the method, if you type an opening parenthesis, a “QuickInfo” tool tip will appear with a list of the parameters required by the method. This facility is much the same as the display of parameters when you enter a function in Excel.

Arguments

Any arguments which are displayed in square brackets are optional while those not in square brackets are obligatory. Thus, for example, when using the “Open” method of the “Workbook” object, the “Filename” parameter is obligatory while the “ReadOnly” parameter is optional. The “QuickInfo” tool tip supplies a list of every parameter, separated by commas, which the method can accept in the order that they must be supplied. If you do not wish to supply a given (optional) parameter, then you must still insert a comma to mark the position of the omitted argument.

Thus, for example, if you wanted to use the “Open” method of the “Workbook” object and supply the “Filename” and the “ReadOnly” parameters, you would type “Workbooks.Open(“c:\reports\main.xlsx”,, True)”. Since the “ReadOnly” parameter is the third, a comma is inserted to mark the position of the missing second parameter. VBA offers a very useful alternative method of entering arguments. You can enter the name of each parameter followed by “:=”. Using this technique, the order of parameters becomes unimportant and no reference has to be made to omitted parameters. Thus, in the example above, we could type “Workbooks.Open(FilePath:=”c:\reports\main.xlsx”, ReadOnly:=True)”.

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

Using Excel VBA and Google Maps to Find the Distances Between Places

Project: Find the Distances Between Places with Excel VBA and Google Maps

My friend wanted to find out the distances between towns in South Africa. He asked me to give him a hand, as I used to do a lot of data processing in my first banking job. My buddy was going to use this to calculate transport costs, but the function I wrote using Excel VBA and Google Maps could be used for any number of applications.

Preparation

There are many ways to skin a cat. This was an unpaid job, with no glory component, so I just did it in what I thought would be the quickest way possible.

Method

I knew Google Maps could calculate distances and directions. So I fired it up and looked for the distance between two towns. I then looked at the source HTML to work out how to extract the distance data. Trying different towns, it soon became apparent that I needed to specify the state and country, as well as the town name. As town names are not unique. I then opened up Excel, made a sheet to calculate the query URLs, and then wrote a function using Excel VBA and Google Maps to piece all the parts together.

Results

The function seems to work most of the time. Sometimes it does not return anything, but that is because Google Maps does not return a distance, and that is because the place names are not specific enough.

Disclaimer

Use the getDistance function at your own risk. It might not work for you, but I won’t be supporting it. The Code:

Function getDistance(urlData As String)
Dim sHtml As String
Dim iStart As Integer
Dim iEnd As Integer
Dim lRow As Long
Dim searchStart As String
searchStart = "distance:"""
Dim browser As InternetExplorer

Set browser = CreateObject(“InternetExplorer.Application”) With Sheets(“Soya”)

lRow = 2
While Not IsEmpty(.Cells(lRow, 6))
urlData =.Cells(lRow, 6)
browser.Navigate (urlData)
browser.Visible = True
While browser.ReadyState <> READYSTATE_COMPLETE
Debug.Print Now, "waiting"
DoEvents
Wend
sHtml = browser.Document.DocumentElement.innerhtml
Debug.Print sHtml i0 = InStr(1, sHtml, "distance:""")
If i0 > 0 Then i1 = InStr(i0 + Len(searchStart), sHtml, """")
If i1 > 0 Then getDistance = Mid(sHtml, i0 + Len(searchStart), i1 − i0 − Len(searchStart))
Else getDistance = "Not Found"
End If
Else getDistance = "not found"
End If

.Cells(lRow, 8) = getDistance
Debug.Print Now, getDistance
lRow = lRow + 1
Wend
End
With browser.Quit
End Function

Learn More!

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

How the Capabilities of an Excel Programmer Can Help Your Business

The Various Capabilities of an Excel Programmer, And How They Can Help Your Business

by Roy Millor

in Technology    (submitted 2012-06-30)

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! Worksheet Functions What if you, the client, was looking for a small-change to one of Excel’s built-in worksheet functions, and do not want to have to pay for the rewriting? That’s not a problem either! A good Excel programmer can write a function with only the new or changed functionality, and then call the built-in function from the new custom function. That way, you’ll only have to pay for the new or changed functionality which will save you quite a bit of money!

VBA Programming

If you’re going to be doing your own VBA programming, but nevertheless want some functions written that you can call from your code, you can just tell a programmer what parameters that you want to pass to the function, and what result that you’d like for the function to return! You can leave the rest to the programmer.  Afterwards, those functions can easily be copied and pasted in your VBA project. Alternatively, the programmer could put the functions on a DLL that you can call from VBA. If you’re already finished creating a macro that provides some performance gain, but you’d like more from it, an Excel programmer can make your macro more versatile, error-free, and convenient. New, better features can also be added Programmers can also help your macro run many times faster! Don’t be surprised if your macro’s speed is boosted by a factor of one hundred. Do you need faster, more efficient calculations in your spreadsheets? Perhaps you have workbooks that take hours, maybe even days, to calculate. If that’s the case, let a programmer demonstrate how much faster they calculate (once modified)! You only need to pay if you approve of the programmer’s work. If improvements are not fast enough to impress you, you aren’t obligated to buy at all. Programmers can automate nearly any task you think about whether hiring a programmer is right for you!

About the Author Looking for a Excel programmer

Try Excel Expert now 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.

Microsoft Excel Experts

How To Find The Right Microsoft Excel Expert That Will Fit Your Goals and Budget  

by Roy Millor in Technology    (submitted 2012-06-30)

Are you searching for Microsoft Excel experts who can help fulfill your needs? If so, consider trying the Directory of Excel Experts!   The Directory of Excel Experts lets you quickly find the right Microsoft Excel experts who can meet your unique business criteria. Whether you need someone nearby in your city, or you’re looking for someone who specializes in a certain task, Directory of Excel Experts can help you out in any country, state, city, or zip code. You can also search for them to be within a set number of miles from your place of business!

Sort by Programs

You can also look for different Microsoft Excel experts depending on their qualifications, skills, and readiness to complete a particular task. You can see what programs they are experts in, like VB.net, Word, SQL Server, Access, etc. You can even combine the searches (for example, finding a VBA specialist that is also an Access programmer) and look for one within 10 miles of you! The Directory of Excel Experts lets its members look for almost any combination of qualifiers imaginable.

Search Results

In the results, you can set it up so you can see the Microsoft Excel experts’ office hours, contact information, what applications they are working in, what their URL is, and much more. Over time, the possibilities will dramatically increase,  as y more qualified Excel experts register with the service.

Toll Free

The Directory of Excel Experts is a free service! You’ll find that the Directory of Excel Experts is available for free, with no strings attached. That’s all – they don’t require pay for their directory and search services. The Directory doesn’t manage the project at all – they stay 100% out of all of that! Everything – the pricing, deals, etc. – is between you and the Microsoft Excel experts, which simplifies things quite a bit. You can contact one expert, or you can contact a dozen – it’s all up to you.If you really like the work done by a particular resource, you can post your review of the Microsoft Excel experts and your interaction with them. As you go along, you’ll find that the specific areas of their experience greatly vary, as does the frequency of their Excel use. Many Microsoft Excel experts are currently becoming professional programmers, developers, and consultants, taking their talents to do freelance work.  Make sure not to forget that thousands of these folks are entry level Excel hobbyists and may be some power users, but not officially qualified folks. Looking for a Microsoft Excel Expert try Directory of Excel Experts now. 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.

Creating Excel Macros

Macros are basically a set of functions and commands that help Excel users in performing the same task again and again easily. Creating Excel macros for the execution of repetitious tasks will help in any business organization. Macros not only save time, but it also improve consistent data maintenance and reduce errors.

Spreadsheets in every organization contain lots of raw data.  This data is analyzed with the use of various logical calculations called formulas. When recording a macro, each formula is then substituted with VBA (Visual Basic for Applications) program code.

Most users can start creating Excel macros simply  by using the Macro Recorder in Excel.  Many simple macros can be created without the intervention of an Excel VBA programmer.

Here are few tips that will help you learn how to record Excel macros.

1. Macros need to be recorded before they are run.

Your Excel options must be changed to grant permission for macros to be executed.  To allow the recording of macros, you need to enable the macro option from the tool menu.

2. Look for the Record New Macro option in the menu.

As soon as you select the option, a dialog box opens, where you need to type a name for the macro you are recording.

3. Just after typing the name click the OK button and the recording will start automatically.

4. The Excel user then needs to execute each of the functions that you need the macros to perform.

The commands and functions that you input while recording are all recorded, even the mistakes. Macros are also capable of performing multiple tasks. \

5. Once the functions are recorded, you need to click the Stop Recording button that is present in the small window opened showing that recording is active.

Once the recording is stopped the macros are saved in the VBA Editor.

6. To run the macros you can run them from the Macros dialog, or you can create a button to be placed on the toolbar.

To create a button, you need to select Customize Button from the tool menu and in the command tab click Macros. Once the dialog box appears, choose the name of your macro and press the OK button. Now the button will remain on the tool bar, just one click away to perform that task.

7. By creating a shortcut on the keyboard, you can run the macro by pressing the required keys on the keyboard.

To create a shortcut you can select Option from the macro menu. Just after selecting, a window will open, where you are prompted to press the desired shortcut key. You can select a key combination that is convenient to you, but remember that some shortcut keys are already assigned to important functions.  For example, you may not want to reassign a shortcut such as Ctrl+P, which inherently brings up the Print menu.  There are also predefined shortcuts for pasting (Ctrl+c) and cutting (Ctrl+x). After selecting the key combination you want, press the OK button.

Macros are easy to use and can be effectively used for formatting cells for text, putting data in certain cells by matching column and rows and for adding formulas while creating spreadsheet.

Once the macros are recorded, Excel VBA programmers can make changes as required, depending on the needs of the organization.

by Schew Maker

in Computers / Databases    (submitted 2011-04-15) 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.

Excel VBA Workbook Events – Macros

These sub procedures that deal with certain Excel VBA Workbook Events need to be stored in the code window for the workbook object.   These short VBA snippets are installed in the workbook itself, in the code window of the ThisWorkbook object.

  • snippetsOpen with Excel – open the target spreadsheet into which these macros are to be installed.
  • Open the Excel VBA Editor with Alt+F11
  • Right Click ThisWorkbook
  • Select View Code.  A new code window  will appear

What are Excel VBA Workbook Events?

Whether you know it or not, every workbook triggers events for even the most routine tasks such as opening, closing, saving, or exiting.   By preempting (or redefining) the default function, you can change the default behavior of Excel to do different tasks.

Prevents a Save As

There are situations that you do not want the user to create a copy of the workbook you have provided.   All changes made to  the spreadsheet must be saved back to the  original location.  This is done by redefining the Excel VBA Workbook Event for SaveAs.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then MsgBox "You cannot perform a SaveAs on this file. Save the file using the existing save settings." Cancel = True
End If
End Sub

Clean Up the Workspace

This macro arranges the open workbooks in tiled format  when a workbook is selected.  This helps the user to clean up and organize the workspace whenever they open a new workbook.  The VBA Workbook Event preempted is Workbook_Activate().

Private Sub Workbook_Activate()
MsgBox "Open workbooks will now be tiled"
Application.Windows.Arrange
xlArrangeStyleTiled
End Sub

Organize the Order of Worksheets

When a user opens a new worksheet, the last worksheet opened becomes last worksheet in the workbook.  The Excel VBA Workbook event preempted is Workbook_NewSheet()

Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox “The new sheet will appear as the last sheet in this workbook."
Sh.Move After:=Sheets(Sheets.Count)
End Sub

Display Sheet and Range Info in Status Bar

The Status Bar display is optional, but I find it particularly useful: it can contain lots of information, and provide some shortcuts.  I keep it turned on all the time.  This Excel VBA Workbook Event Macro displays the sheet name and address of the selected range in the status bar.  This comes in handy when working with multiple workbooks and worksheets, and when scrolling so the active cell is off the screen.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Excel.Range)
Application.StatusBar = "You are in " & Sh.Name & " and have clicked in the cell range " & Target.Address
End Sub

Code By: Chester Tugwell Article Directory: http://www.articledashboard.com

Chester Tugwell is a freelance Microsoft Office trainer and owner of Blue Pecan Computer Training based in Sussex, UK. He provides a comprehensive set of Excel training courses as well as other Microsoft Office training options. More free computer training materials are available via the Blue Pecan website.

Narrative and explanation of the subroutines are 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.

Using Message Boxes and Input Boxes in Excel VBA

If you want to make your macros interactive then one way of doing this is to introduce message boxes and input boxes. These allow you to display messages that require a response or ask the user to enter values before the macro can proceed any further.

Simple greeting using a message box

Create an empty workbook and the switch to the Visual Basic Editor (VBE) by clicking
Tools > Macro > Visual Basic Editor
or by using the shortcut key ALT F11.

If you are using Excel 2007 click on the Developer ribbon and then click on the Visual Basic Editor button.

Once in the VBE environment you will need to create a module to hold your function. Click Insert > Module In the code window enter the following procedure

Sub Greeting()
MsgBox “Welcome to my spreadsheet”
End Sub

Press F5 to run the macro

Passing a variable to a message box

In the same procedure as above we will create an input box variable called MyName and declare the variable as a string. We will then concatenate the message “Welcome to my spreadsheet” with the value in the MyName variable. This joins the message in the message box with a value the user enters in an input box.

Sub Greeting()
Dim MyName As String
MyName = InputBox("What's your name?")
MsgBox "Hi There " & MyName & "."
End Sub

Press F5 to run the macro

Adding more information to a message box

Now we are going to use Date, Time and CurDir functions to add more information to our message box. Adjust your code as shown below.

Sub Greeting()
Dim MyName As String
MyName = InputBox("What's your name?")
MsgBox "Hi There " & MyName & ". Today's date is " & Date & " and the time is " & Time & ". This file is saved in " & CurDir & "."
End Sub

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

MASTER CORE EXCEL 2010 TOOLS

MASTER CORE EXCEL 2010 TOOLS FORMULAS AND FUNCTIONS FOR BUILDING POWERFUL, RELIABLE SPREADSHEETS!

Excel expert Paul McFedries, author of the MrExcel Library, shows how to use Excel 2010’s core features to solve problems and get the answers you need! Using real-world examples, McFedries helps you get the absolute most out of breakthrough Excel 2010 improvement from Sparklines to the brand-new version of Solver. Along the way, you’ll discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation.Becoming an Excel expert has never been easier! You will find crystal-clear instructions, insider insights, even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more.

    • Create more powerful formulas
  • Use conditional formatting to instantly reveal anomalies, problems, or opportunities
  • Analyze your data with standard tables and PivotTables
  • Use complex criteria to filter data in lists
  • Understand correlations between data
  • Perform sophisticated what-if analyses
  • Use regression to track trends and make forecasts
  • Build loan, investment, and discount formulas
  • Troubleshoot problems with formulas, ranges, and functions

About MrExcel Library:

Every book in the MrExcel Library pinpoints a specific set of crucial Excel skills, and presents focused tasks and examples for performing them rapidly and effectively. Selectedby Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will:

    • Dramatically increase your productivity—saving you 50 hours a year, or more
  • Present proven, creative strategies for solving real-world problems
  • Show you how to get great results, no matter how much data you have
  • Help you avoid critical mistakes that even experienced users make

[wpramazon asin=”078974306X”]

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