Ad
Ad
Ad
Tag

VBA

Browsing

If you use Excel to retailer information, from time to time you need to have to lookup for info speedily and competently. For instance if you choose a get in touch with from an critical buyer you may well need to accessibility products or pricing facts.

A handful of lines of VBA code can produce an input box which you can use to look for for information in a worksheet. A normal situation could go a thing like this:

  • Respond to an incoming simply call and a buyer requests cost details
  • Open up the “Costs” Excel file
  • An enter box requests the look for textual content
  • The VBA code highlights any products discovered matching the query

Creating The VBA Lookup Code

We want the input box to activate as before long as the file opens so we spot the code in the workbook portion of the VBA code window, not as a general module.

  personal sub workbook_open up () 

'the rest of the code goes listed here
conclusion sub

The to start with thing we&#39ll do is generate an inputbox exactly where a consumer can enter the lookup expression. You can customize the enter box with prompts and a title, but we&#39ll adhere to a common format:

  txt = InputBox ("Research") 

Following, we&#39ll activate the worksheet which retains the searchable knowledge, apparent any earlier look for results and determine the array to lookup.

  Sheets ("research") 
Cells.ClearFormats
Array ("a1"). CurrentRegion.Pick out

The code now has a search string to search for, and a details variety to search by. We&#39ll search for decrease circumstance versions of the data and the textual content so that a user doesn&#39t have to stress about matching circumstance.

  For Every single c In Collection 

If we obtain a match we want to be ready to spotlight the entry in this state of affairs we&#39ll spotlight the cells from the to start with column to the very last column of details instead than the full row.

The benefit in working with the instr operate is that a partial match can be found, preserving the consumer treasured time. Generally, when looking for “widget” encounter might inform the user the greatest approach is to simply form “widg” in the search box.

  If InStr (LCase (c.Textual content), LCase (txt)) Then 

very first = Variety (c.Deal with) .Conclusion (xlToLeft) .Tackle

previous = Array (c.Handle) .Finish (xlToRight) .Handle

Array (1st & ":" & past) .Interior.ThemeColor = xlThemeColorDark2
Stop If

Upcoming

Range ("A1")

The code can be edited to research a precise column or worksheet and it would be feasible to look for all the sheets in a workbook with a tiny further coding.

Summary

This code is an case in point of how some innovation can considerably increase business enterprise processes, in this circumstance discovering information and facts rapidly and competently. And though Excel has a lot of built-in capabilities to attain the similar end result, a very little understanding of VBA blended with your very own company knowledge can carry dramatic office enhancements.



Supply by Andy L Gibson

An Excel “if” statement enables the worksheet to modify the worth of a mobile primarily based on a further mobile. For instance, if you experienced these exam effects, the worksheet would put a “go” or “fall short” in the mobile made up of the components.


55, = if (a1> = 50, "Move", "Fail")
45, = if (a2 <50, "Fail", "Pass")

becomes:

55, Pass
45, Fail

The formulas become challenging when more parameters are included and this article will show you how to use VBA to write the “if” statement.

Handling Complex If Formulas

A problem can occur when the statement contains multiple values ​​with AND OR parameters if you were trying to grade the scores above rather than a simple “pass” or “fail” the formula becomes more complicated.

Here&#39s the formula to grade the scores into “Fail”, “Pass” or “A” where a score of 70 or greater was an “A”.


= IF (AND (A1 = 0, A1 <50), "FAIL", if (and (a1> = 50, a1 <70) , "A",)))

For an explanation of how to write “if” formulas containing multiple “AND” values, you can Google: “Excel if and statements”.

You can see that complex Excel formulas are not easy to follow and do not use a logical syntax. That&#39s because they need to be written on a single line. Fortunately, you can use VBA to write the formula in plain English and then use a few lines of code to create the if statement.

First, let&#39s write the formula in plain English into 3 separate cells, with the result in the next cell.


if a1> and a1 <50, "Fail"
if a1> = 50 and a1 <70, "Pass"
if a1> = 70 and a1 <= 100, "A"

We now use VBA code to loop through the cells and parse the values ​​into the text string we need for the “if” formula. We want each of the statements to be converted into a standard syntax, for example, line 1 should become:


if (and (a1> , a1 <50), "Fail", next statement )

First, we&#39ll select the range. Note that we use the chr (34) character to use as quotation marks.


Range ("a1")

Set rng = Range ("a1:" & ActiveCell.End (xlDown) .Address)

quot = chr (34)

myStr = "="

Next, we&#39ll loop through each cell and convert the plain English code into the Excel formula we need.

For i = 1 To rng.Rows.Count

myStr = myStr & "if (and (" & Replace (rng.Rows (i), "and", ",")

myStr = myStr & ")," & quot rng.Rows (i) .Offset (0, 1) & quot , "

Next

For i = 1 To rng.Rows.Count

myStr = myStr & ")"

Next

The code above produces the following string which can now be inserted into the appropriate cells to create the correct result for the formula.


= IF (AND (A1> , A1 <50), "FAIL", IF (AND (A1> = 50, A1 <75) , "A",)))

The formula can be manually entered in the cells, or using VBA like the code below.


Range ("d2")
ActiveCell.Formula = myStr
Set rng = ActiveCell.CurrentRegion.Columns (1)
Selection.AutoFill Destination: = Range ("d2: d" & rng.Rows.Count)

Once you&#39re able to write out what you need in plain English, the VBA code takes care of the rest, without you needing to understand the complex formula.

Summary

This code snippet is a good example of a procedure you can use without needing to understand how it works. Excel formulas can be challenging, but the VBA procedure can be edited to work with the current project you are working on.



Source by Andy L Gibson

Workbook BeforeSave Party

This event will run in advance of a workbook will save.

Necessary Parameters

SaveAsUI – The SaveAsUI parameter will decide if the Help you save As Dialog will exhibit. To display screen the Help you save As Dialog established the SaveAsUI benefit to Genuine. If you do not want the Help save As Dialog to display set the SaveAsUI price to Bogus.

Terminate – The terminate parameter is first established to Wrong when the function begins. If the terminate parameter is set to price of Correct then the workbook will not conserve.

Workbook BeforeXmlExport Party

This function will run right before Excel will save or exports XML knowledge from this unique workbook.

Demanded Parameters

Map – The map parameter returns the map that is remaining saved or exported as an XMLMap object. This parameter can be used to manipulate the XML map object and its attributes.

URL – The URL parameter returns area of wherever the XML map will be saved as a string.

Terminate – The cancel parameter is authentic set to Phony when the party begins. If the cancel parameter is set to value of Accurate then the workbook will not conserve or export the XML map.

Workbook BeforeXmlImport Occasion

This celebration will operate just before Excel imports XML knowledge into or refreshes XML info within this specific workbook.

Necessary Parameters

Map – The map parameter returns the map that is getting imported or refreshed as an XMLMap item. This parameter can be made use of to manipulate the XML map object and its qualities.

URL – The URL parameter returns deal with route of the XML map is saved as a string. Assigning a benefit to this parameter will inform Excel that exactly where the XML file that has XML data is positioned.

IsRefresh – The isRefresh parameter will return a value of Real if the XML map is becoming refreshed and a value of Fake if the XML map is becoming imported.

Terminate – The cancel parameter is primary established to Phony when the party begins. If the cancel parameter is established to benefit of Real then the workbook will not import or refresh the XML map.

Workbook Deactivate Celebration

This event will operate just about every time a person selects a distinct workbook.

Workbook NewChart Function

This event will operate every single time a new chart is established in the distinct workbook.

Required Parameters

Ch – The ch parameter returns the newly designed chart as an chart item to allow buyers to manipulate the events, procedures and qualities.

Workbook NewSheet Occasion

This celebration will run each time a new workbook is produced in the particular workbook.

Expected Parameters

Sh – The sh parameter returns the freshly established worksheet as a worksheet object to allow for customers to manipulate the situations, procedures and qualities.



Source by Matthew S Demaine


VBA: Visual Basic for Application is portable language that can manipulate Microsoft office Application like-Word, Access, Power Point, and outlook. Why I am writing on VBA???
If you do a job that does not need much human decisive brain but repetitive in nature, A VBA macro automation can help you to reduce time and human effort. Increase your productivity too. When I said a VBA Macro …definitely the question come as what is a macro??
 
Macro:A macro is a piece of code which has a capability to automate MS office suite. Initially a programming language which was having capabilities to execute a sequence of command in MS office Suite is called Macro language.
 
How it is developed?
Please do not confuse with VB(Visual Basic ) with VBA(Visual Basic Application ) to start with. Way back in 1960 when BASIC((Beginner’s All-purpose Symbolic Instruction Code) was born. It became hugely popular among the software industry. After a great success, people wanted a user friendly  version of it. The first customized version of the BASIC was quick BASIC released by Microsoft in 1985. After understanding the need of the customer and support Microsoft office package in 1995 Microsoft released the first version of VBA.
 
What kind of task can be automated through VBA??
·         Recurring Tasks
·         Repetitive Tasks
·         Automatic update or refresh
·         If you need your own function that will support your activities
·         Greater look and feel
·         Control lot more applications through one point
 
Common Problems of VBA:
·         Most of the times Macros are very week
·         Macro created on one version may not work in other version of MS packages
·         Sometime it is windows dependent. I mean Macro created in operating system may not work in other operating
·         It can be concern as security threat.
 
How to access Macro:
·         Open any MS office application(word,Excel etc)
·         Go to view tab
·         Click on Macro drop down
·         Click on view macro to see if any macro is already there
·         Or click on record macro to create a new one
 
 
Well what you need
You don’t need to have anything else other than a MS office package. It is inbuilt inside of a MS office.