VBA

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Share tech news, updates, or what's on your mind.

Sign up to Post

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
0
 
LVL 1

Author Comment

by:Shai Alon
Comment Utility
You're right, and I'm sorry.
I was frustrated from that post that it bothered me that the answer seemed simple.
I've modified the post to be neutral and answer the question only.

Regards,
Shai
0
Announcing the Most Valuable Experts of 2016
LVL 6
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
2
Warning
This article describes a serious pitfall that can happen when deleting shapes using VBA.
3
Windows Phone Theme Colours
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
3
 

Expert Comment

by:John Clark
Comment Utility
Gustav,

I adapted my application to this and I could not believe how efficient and easy this was to do. I see myself deleting a HUGE amount of redundant code and using Class Module in the future. They have always intimidated me and quite frankly, I did not see the use of them until I needed something similar to what you did here. The link you provided to John Colby's Blog was spot-on and VERY informative.

A question on this method however, the particular functionality I am trying to achieve applies to Text and Combo boxes. Reading JC's Blog led me to believe that I would need another Class to handle each type of Object.

Did I comprehend that correctly or can I include more than one Object in this Class?

Great stuff!

John
0
 
LVL 51

Author Comment

by:Gustav Brock
Comment Utility
Yes, you implement many classes, perhaps one for each type of control.

Or you can modify this check:

    If Control.ControlType = acTextBox Then

to check for other control types as well. And then either apply the same or different methods.

Or do both. Or add more conditions; for example only apply a method to a control with a control source.

/gustav
0
Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
0
Decision Making
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
25
 

Expert Comment

by:Aamir Hussain
Comment Utility
A simple and excellent solution of the complicated question. Well done Mr.Shums :-)
1
 

Expert Comment

by:Brendan Wilson
Comment Utility
This is an excellent article. I appreciate you taking the time to write this.
0
Security
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
2
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
1
 
LVL 48

Expert Comment

by:Dale Fye
Comment Utility
Jim,

Great article.  I use a similar process in a procedure I use for reading from or writing to Excel.  The problem is that occassionally, the user might want to read from or write to a workbook which is already open, and if the user has more than one instances of Excel open it is vertually impossible to select the correct version.  With a method very similar to this, I am able to identify all available instances of Excel and allow the user to select the appropriate instance based on the workbook(s) opened in those instances.

Dale
0
Screenshot of Automated Link Checker
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlighted yellow and are converted into a hyperlink.
1
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
1
On Demand Webinar - Networking for the Cloud Era
LVL 9
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
2
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
If you could add a paragraph painting a picture of what you just said then we'll be good.
0
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Nicely written.  I'm currently in an Oracle shop that has a wompload of .xml, .sql, and Java files that are used to build a monster data warehouse where using a tool like this is very important for supporting code.  

Voted Yes.
0
Eggs in one array
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes a way to elegantly solve the problem.
6
 
LVL 1

Expert Comment

by:sdeller
Comment Utility
The suggestion using UBound is incorrect.  With the code like this:
  CustData(UBound(CustData)).strCustName = ...
  CustData(UBound(CustData)).dblSales = ...
the code will create a new entry with the first assignment, causing UBound to increment. So the second line will refer to yet another new entry.  You need to assign both values with one assignment.
0
 
LVL 48

Author Comment

by:Martin Liss
Comment Utility
I'm sorry but that is not correct. In VB6, once an array is created the only ways to increase the upper bound of the array is through Dim, Redim and Redim Preserve. If you place this bit of code in a new project you'll see that I'm correct.

Option Explicit

Private Type CustomerData
    strCustName As String
    dblSales As Double
End Type
Private Sub Form_Load()
Dim CustData() As CustomerData
Dim lngIndex As Long

ReDim CustData(2)

CustData(0).dblSales = 0
CustData(0).strCustName = "Customer 0"

CustData(1).dblSales = 100
CustData(1).strCustName = "Customer 1"

MsgBox "The upper bound of CustData is " & UBound(CustData)

CustData(UBound(CustData)).dblSales = 200
CustData(UBound(CustData)).strCustName = "Customer 2"

MsgBox "The upper bound of CustData is still " & UBound(CustData)

For lngIndex = 0 To UBound(CustData)
    Debug.Print CustData(lngIndex).strCustName & " has sales of " & CustData(lngIndex).dblSales
Next
End Sub

Open in new window

0
Oh no
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
68
 

Expert Comment

by:M Melson
Comment Utility
Very nice article. Thank you for taking the time writing and sharing it.  I can use part with variable names to indicate the type of variable. Good advice.
0
 
LVL 48

Author Comment

by:Martin Liss
Comment Utility
Thanks.
0
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
5
 
LVL 51

Author Comment

by:Gustav Brock
Comment Utility
My apologies. The reference to Excel is a left-over from my article about the Windows Phone colour palette.

I did have an Excel version in mind, I recall now, but I found that (user)forms in Excel is quite a different animal than forms in Access.
Somehow a userform in Excel could be tweaked for the purpose, I guess, but I have never designed a form in Excel, so it wouldn't be a 15 minute task for me, thus I had to postpone the task and haven't yet found the time.

I'll put it on my to-do list but can't tell when done. If I succeed, I'll drop a message here, of course.

/gustav
0
 

Expert Comment

by:Sam Isaacson
Comment Utility
Cool - thanks.
0
There is often more than one way to accomplish a particular coding task and one of the criteria that can be used to determine which method to use is to determine which one is the fastest.  But how do you do that? You could try using displays of Visual Basic's Now() or Time() functions, and doing that will get you some results but they won't be very accurate since the accuracy of those functions is only about 1 second and that is huge compared to the time it takes to run a line, or even in most cases, thousands of lines of code. To improve on that you'll need to use an API. Some of the API possibilities are GetTickCount, TimeGetTime and QueryPerformanceCounter. Here's a chart of the commonly used methods and their accuracy.

Accuracy
As you can see, GetTickCount and TimeGetTime are a hundred times more accurate than the VB functions and 10 ms is likely fine enough for any code that you want to time. QueryPerformanceCounter is much more accurate than that in that it can measure actual CPU cycles but most programmers will never need to do that.

Here's a VB6 example that uses GetTickCount. In this case a comparison is done between 'Test1' where the Array function to fill an array, and 'Test2' where a more manual approach is used.  

Each test uses a loop that repeats an action the same, large, number of times. The use of the large number of loops magnifies the results of the test and that is necessary because an individual line of code can be almost too fast…
12
 
LVL 56

Expert Comment

by:Bill Prew
Comment Utility
How about using Timer() in VBS?

~bp
0
 
LVL 48

Author Comment

by:Martin Liss
Comment Utility
I can't say one way or the other for VBA since my main experience is with VB6 but Microsoft says the resolution of the Timer function is just 1 second.
0
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.
 

What are they?

An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains a list of valid elements. Here’s an example:

Private Enum MyEnum ‘  May also be Public
    FirstElement
    SecondElement
    ThirdElement
    FourthElement
    ‘etc.
End Enum      

You would place that code in the Declarations section (in other words at the top) of any type of form or module. Each value in the list is defined internally by VB as a Long Integer and by default the first element will be given a value of 0, the second a value of 1, and so on.

You might be saying to yourself that you could do that with constants like this

Private Const FirstElement = 0
Private Const SecondElement = 1
Private Const ThirdElement = 2
Private Const FourthElement = 3

and you'd be right, but there are several advantages to Enums that will become apparent as you read on. Two that I’ll mention now however are: they are faster and take up less memory than strings and Intellisense will be available when you use them as shown in this picture. If you’ve forgotten how to invoke Intellisense, you press Ctrl+Spacebar, and in this case it was done after the equal sign was entered.

Using Intellisense to show Enum values
You may not have realized it but you’ve probably run into Enums which are built into Visual Basic.

The built-in days of the week Enum
10
 

Expert Comment

by:isnoend2001
Comment Utility
Thanks i have some code to use this
0
 

Expert Comment

by:Esot
Comment Utility
Good clarification, thanks
0
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't interested in and/or not all of the procedures that led up to arriving at the break point. Adding Debug.Print statements or MsgBox statements in procedures is another way but adding them manually can be very tedious. The add-in I've attached resolves those problems by automatically adding  statements in all selected procedures that contains "Debug.Print" or whatever else you want along with the name of the procedure. 
When the add-in is loaded this toolbar will be added to the Visual Basic IDE in VB6 or the VBE (Visual Basic Environment) in Excel.
Insert-Procedure-Names-Toolbar.jpgThe button on the right in the toolbar is the one that you use to set the prefix value and when it is clicked this window is displayed.
Choose-Code.jpgThe default for the prefix string is "Debug.Print" but you can choose "Const Proc_Name = " or set the prefix to anything else you want including "Msgbox" by choosing 'Other' and entering the value. Note that the 'Const PROC_NAME' prefix can be very valuable if you want to be able to include procedure names in your error routines.
And this shows the VB6 version of the window that appears if you click the first button in the toolbar. You use it to select the procedures to be modified. (The VBA …
3
 
LVL 48

Author Comment

by:Martin Liss
Comment Utility
Updated 9/3/2014
Version 3.0 - Many bug fixes and performance-improving changes including:
New toolbar and selection icons
Corrected a problem where the WARNING line could be added more than once to the same procedure
Corrected a problem where objects added after the 'Select Procedures' was shown for the first time weren't being included
Previous selections are now remembered each time 'Select Procedures' is shown
Added a second button to the addin toolbar as a replacement for the InputBox that used to be used, so that the user isn't repeatedly asked what marker he wants to use
0
 
LVL 48

Author Comment

by:Martin Liss
Comment Utility
Updated 9/20/2014
Version 3.0.1

Corrected bug where commented out procedures were being considered as active code.
0
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that follows allows you to easily do just that.

A few things to note about the code.

o Microsoft Word must be installed on your PC and on any of your users' PCs in order for this code to work
o No Reference to Word is required in your application
o The code as shown will check spelling but you can also have it check grammar by changing the objDoc.CheckSpelling line  to objDoc.CheckGrammar
o You can replace the textbox with a RichTextbox if you like and the code will work with it as well
o In Excel you will need to remove the App.OleRequestPendingTimeout = 999999 line
o The code expects an ActiveX textbox named "Text1" and an ActiveX command button named "SpellCheck"

 
Private Sub SpellCheck_Click()

    Dim objWord As Object
    Dim objDoc  As Object
    Dim strResult As String
    Const QUOTE = """"
    
    On Error GoTo ErrorRoutine

    App.OleRequestPendingTimeout = 999999
    Set objWord = GetObject("Word.Application")
    If TypeName(objWord) <> "Nothing" Then
        ' Word is already open
        Set objWord = GetObject(, "Word.Application")
    Else
        ' Create an instance of Word
        Set objWord = CreateObject("Word.Application")
    End If

    Select Case objWord.version
 

Open in new window

4

This is an Add-On procedure to be used in conjunction with the code provided in Reducing EE Email Clutter using Outlook, because it uses the "tagging system" of that article.


Purpose

Imagine the following situation: You have read the prior article, and implemented the code provided, and your EE Email is organized in a better way now.  Maybe you even used conditional colouring to emphasize specific topics.
But you have still 500 notification mails for the last 4 days in your inbox informing you about new questions you have set a filter for, or neglected questions of areas you are a Designated Expert in.  You pick one or another of those questions, maybe posted the prior day already, to provide suggestions.  You go into the questions, and find them answered and closed already.

Well, that can't happen anymore, if you apply this code on all or some of your questions! It will go thru the (EE assigned) inbox completely (if nothing is selected), or only thru the selected posts, and look whether the question is already closed or deleted. If so, it is removed from the inbox, and you can be sure about each remaining question being worth to have a look into it.

For Page Editors: The code also checks for deleted articles, and removes the notif then. It is more difficult to extend to check for the other states, like Editor or Author Review, or (Re-)Published, since that is not reflected in the printer-friendly version.


Inside the code
1
 
LVL 70

Author Comment

by:Qlemo
Comment Utility
With EE v.10 this Add-On stopped working (as expected). Since I will (probably) have to switch to read XML streams instead of the printer-friendly page, getting it working again might last a while.
0
Free Tool: ZipGrep
LVL 9
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Being an active EE Expert means to get a lot of (E)EMail, as you certainly know. If you are using Outlook, I'll show you how to minimize your inbox contents without losing anything – even improve the experience by changing the Subject line to facilitate sorting and by adding tags for quick recognition.

The "Framework" that I present below has been tested with Outlook 2003, 2007 and 2010. Since VBA Macros are used, you might have to allow macro execution when starting Outlook.
We utilize so-called User Properties, which have some particularities, but are great for holding additional user-definied content.

I have not tested the code provided if it works flawlessly with HTML mails, only with plain text.
Since the mail content and subject can change at any time, it might not work anymore after new releases were published.

 

1. Basics – Different types of email you will receive, and what to do with them


There are many different types of emails you can get, distinguished by the text of the Subject line. Getting a newer email might render formerly received ones void, or newer ones are not interesting because you already have been informed. To build logic around that can get quite complex. I have tried to make the rules as easy to understand and implement as possible. Now, let's start with the enumeration of possible headers and actions:

Expert Alert: …
A new question your filter has trapped. This is usually the first mail you get,
8
 
LVL 70

Author Comment

by:Qlemo
Comment Utility
I have posted an Add-On for bulk-removal of collected mails, checking if the questions are deleted or closed meanwhile.
0
 
LVL 70

Author Comment

by:Qlemo
Comment Utility
EE v.10 has changed the notification alerts sent, and the subjects are more informative now. I'm currently reworking the code - there aren't that much changes necessary, but I have to wait until I got most of the different types of notifications. Most probably I will write a short follow-up article, instead of editing this one.
0

0. Preface

This Article is a replacement of http:/A_1788-Getting-your-EE-Ranking-statistics-in-Excel.html. Changes in the way Experts Exchange delivers point statistics, implemented in July 2010, made this complete overhaul necessary, and I decided to spawn a new Article for the sake of clarity and reference.

I'm trying to (re-)cover all areas of the predecessor first, for all those of you who did not use the Excel sheet before. So that sections can be read diagonally for the "experienced users" (I tried to keep it as close as possible to the predecessor to make that easy).

Then I'll show the differences, and how to incorporate the valuable historical data obtained previously and no longer available yet.

Since VBA is required, the method described will not work for Office 2008 for Mac, which has no VBA. The only way to have a similar result is to create the required Web Queries yourself, and replace some of the code with Excel formulas - doable, but very time consuming to set up.

1. Target

For all Experts out there who keep a record of the points they get, and do that manually, here is a way how to obtain the data automatically - as far as possible.
Because of the nature of the new ranking statistic pages, you need to get them in time. You cannot go back one week or month or year anymore, as it was possible before the major change in July 2010. Hence it…
17
 
LVL 63

Expert Comment

by:SysExpert
Comment Utility
I am interested. Also need statistics of experts ranked by number of certificates they have.
Any help appreciated.  I am in the process of looking for a new position and this may help me stand out from the crowd.

Thanks !
0
 
LVL 70

Author Comment

by:Qlemo
Comment Utility
The Web Query URL is
  http://www.experts-exchange.com/memberSearchResults.jsp?sort=110_-1&searchDisplayTypeID=1&ListNamesort=-1&ListNameorderBy=110&ListNamelistView=1&epp=200
to get a list of 200 Experts, descending on #certifications.

You can't put that into Excel easily, or if you do, data starts at row 124 (header in row 123). That is because the results are no web tables.
What do you want to do with the list?
0
The code described here does no longer work. Please see replacement Article: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3887-Getting-your-EE-Ranking-statistics-in-Excel-The-Next-Generation.html .
 

1. Target

For all Experts out there who keep a record of the points they get, and do that manually, here is a way how to obtain the data automatically ...

... as far as possible. Zone points are retrievable at any time, but overall points and associated data is not. Experts Exchange did abolish the retrieval of overall statistics of past days, weeks, months and years, but only recent data is available for each category. Since the method shown here reads the Web pages, you can't get more data than if you look yourself.

The framework I provide here as an Excel Worksheet will allow for filling in as much data as possible. To keep track of overall points, which are not the total of all zone points as you certainly know, you have still to query and enter it manually for past periods. There is no magic wand used, and hence nothing you can't do on your own. And you need only basic Excel skills to use this spreadsheet (though understanding will need some more skills, but it is not really difficult).

Important:
You need to have the "Remember Me"
18
 
LVL 70

Author Comment

by:Qlemo
Comment Utility
0
 
LVL 92

Administrative Comment

by:Patrick Matthews
Comment Utility
Qlemo,

It is my privilege to let you know that this article has been selected for the Editors Choice designation, EE's highest accolade for articles.  Great work!

matthewspatrick
Page Editor
0

VBA

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.