Microsoft Excel Tips - Excel.Tips.Net Optimize Slow VBA Code. Speed Up Efficient VBA Code/Macros Back to: Excel VBA . Got any Excel/VBA Questions? Excel Help Speed up code and stop screen flickering: Sub NoScreenRePainting() Application.ScreenUpdating=False 'Your code here. Preventing calculation while executing code: Sub NoCalculations() Application.Calculation = xlCalculationManual 'Your code here. Speeding up code if you have Worksheet or Workbook Events. Sub StopAllEvents() Application.EnableEvents = False 'Your code here. Use the With Statement when working with Objects. Sub WithARange() With Range("A1") .Value = 100 .Font.Bold = True .Interior.ColorIndex = 6 .Copy Destination:=Range("B1") End With End Sub Use VbNullString instead of = "" When needing to default a String variable back to it's default of "" use: Sub EmptyText() Dim strWords As String strWords = "Cats" MsgBox strWords strWords = vbNullString MsgBox strWords End Sub Inserting a Relative formula into a range of cells: Faster than AutoFill or Copy. Avoid the use of Copy and Paste whenever Possible: Here's a better way!
Find a set of amounts that match a target value There are instances when one wants to figure out which elements of a set of numbers (amounts) total to a target value. One instance where this may be necessary is for a company that receives a check for outstanding accounts receivable (A/R) bills but doesn't have matching documentation indicating what bills are being paid. Excel template using Solver VBA code to find multiple matching combinations References For a shareware product that addresses the same issue, please visit TM Match Target. Recent Comments Excel template using Solver The Excel template uses Solver to provide one possible combination of numbers that total to the target amount. The Excel template VBA code to find multiple matching combinations There have been several requests for solutions that provide a list of all possible matches. Also, the code may take a fair amount of time to run depending on the size of the list of numbers being searched. How to use the code The second cell contains the target value. The code References
Cookies and Excel - Excel Liberation Option Explicit Private pHtmlHandle As Long Private pHtmlName As String Private pCookieName As String Private pDays As Long ' everything to do with creating cookies in excel Const cFailedtoGetHandle = -1 Public Property Get htmlName() As String htmlName = pHtmlName End Property Public Property Get cookieName() As String cookieName = pCookieName Public Function getCookie() As String ' with an initialized cookie, start up a browser and get it createCookieJar "get" getCookie = doCookie End Function Public Function putCookie(content As String) As String createCookieJar "put", content putCookie = doCookie Private Function doCookie() As String Dim cb As cBrowser Set cb = New cBrowser ' browse to it With cb .init .Navigate pHtmlName doCookie = .ElementText(pCookieName) .kill End With Set cb = Nothing Public Sub init(Optional hn As String = "cookiecjobject.html", _ Optional cn As String = "cookiecjobject", _ Optional ds As Long = 365) pHtmlName = hn pCookieName = cn pDays = ds End Sub ' create the script to get or put content
Msdn forums Ask a question Quick access No announcements Forums [ view all ] Selected forums Clear Filter : All threads All threads Answered Unanswered Proposed answers General discussion No replies Helpful Has code All languages Sort : Most recent post Most recent post Most recent thread Total votes Total replies Excel Pages Excel Pages This page describes the various pages on the web site. This is the Page Index to the Excel Web Source site. The icon indicates that the content is related to Excel itself (typically the user interface) or to formulas. indicates that the content is related to VBA. indicates that the content is related to Visual Basic 6. indicates that the content is related to Visual Basic NET. indicates that the page has been converted from HTML to ASP.NET 2.0 indicates a download on the page. About This Site This page tells a bit about the site, along with acknowledgements, and miscellaneous information. Activating Excel From Other Programs If you are doing cross-application program (such as automating Excel from a Word document or template), you may find it useful for the user's convenience to activate Excel and set keyboard focus to the active sheet. Adding The COM Add-Ins Menu THis page desribes how to add the COM Add-Ins menu item to the Tools menu. Adding Menu Items To VBE Alert Message Component
Prevent Users Ctrl Break (ing) Your VBA Code During Execution | Excel & VBA – da Tab Is On Anytime I write VBA code that runs for more than a split second, one of my worries remains that someone will ctrl + break it. You see, I am a very strong supporter of P.E.T.A. (People for Ethical Treatment of Algorithms) and believe that any code, no matter how long it takes (or in my case how badly written it is), must be allowed the dignity to complete. And for those who believe in killing poor little VBA code(s) with a ctrl + break, I just got a neat little trick up my sleeve. Here’s how it goes: Take for example some VBA code that runs for a few seconds. So the trick to prevent VBA code execution by pressing ctrl + break is to insert this magic statement in the VBA code: The statement instructs Excel to not show the “Code execution has been interrupted” message and provides a way for the developer to tap into the ctrl + break action by the user. Here is a code that is supposed to run for 5 seconds. Go ahead – take control
Excel: Find closest value | Get Digital Help Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with a criterion Find closest value Question: How to find closest number in a list? Answer: Array formula in cell C2: How to create an array formula Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.Press and hold Ctrl + Shift.Press Enter onceRelease all keys Explaining array formula Step 1 - Return absolute values of numbers in an array, numbers without its sign ABS(number) returns the absolute value of a number, a number without its sign. becomes and returns Step 2 - Return the smallest number MIN(number1,[number2]) returns the smallest number in a set of values. returns 1. Step 3 - Return the relative position of an item in an array MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value returns 12. and returns 42. EDIT: Added excel workbook to this blog post Download excel example file
Custom Number Formats (Multiply & Divide by any Power of 10) Posted on January 31st, 2012 in Excel Howtos , Huis , Posts by Hui - 35 comments In the past here at Chandoo.org and at many many other sites, people have asked the question “How can I display a number Multiplied or Divided by 10, 100, 1000, 1000000 etc, but still have the cell maintain the original number for use in subsequent calculations“. Typically the answer has been limited to “It can’t be done” or “It can only be done in multiples of 1000”. Well thanks to a tip I picked up from Kyle who responded to a post here at Chandoo.org they are all wrong. It is possible to Multiply or Divide any cell contents by any power of 10 using Custom Number Formats ! That is: How does this work: When using custom number format we have two possibilities to modify the display number Use a Comma to divide by 1000; orUse a % to Multiply by 100 So using a combination of these any power of 10 can be obtained. So using the correct combination of , and % can result in any power of 10 multiplier we require. Downloads
Microsoft Access help, tutorials, examples Where to ask for help For a list of places where you can post questions, see Forums on our Links page. Brief tutorials and help for novices and database developers. This is literally an FAQ. Re newer versions, see Converting to Access 2007 or 2010.Examples have not been tested in versions beyond Access 2010.Articles marked MS are quoted by Microsoft (on the Office website.) Allen is no longer accepting database clients. Search this site If you don't know how to write code, these may help. Table Design Forms Reports Expressions Solutions for developers. Combo Boxes Queries Relational Design Coding examples for VBA (Visual Basic for Applications.) Solutions (VBA) Error Handling in VBA Access 95 and laterELookup() - an extended replacement for DLookup() Access 95 and laterECount() - an extended DCount(). Functions (VBA) Pitfalls and Traps Examples by Library See also the Index of VBA Functions - list of the code from all pages on this site. Specific solutions and downloadable tools. Applications Utilities
Using Microsoft Excel Worksheet Functions in Visual Basic [Excel 2003 VBA Language Reference] The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location. Note Some worksheet functions aren't useful in Visual Basic. For example, the Concatenate function isn't needed because in Visual Basic you can use the & operator to join multiple text values. Calling a Worksheet Function from Visual Basic In Visual Basic, the Microsoft Excel worksheet functions are available through the WorksheetFunction object. The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. Sub UseFunction() Dim myRange As Range Set myRange = Worksheets("Sheet1").Range("A1:C10") answer = Application.WorksheetFunction.Min(myRange) MsgBox answer End Sub If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. Note Visual Basic functions don't use the WorksheetFunction qualifier. Example
VBA Select Case – Using VBA Select Case Statement in Excel | Excel & VBA – da Tab Is On The VBA Select Case Statement is an important construct in the VBA language. The Select Case statement in VBA allows the program to execute one set of statements out of many based on what a given expressions evaluates to. In today’s post we will look at some basic and intermediate ways in which it can be used when writing VBA code. Basic Form of the VBA Select Case Statement Here are the various parts of the Select Case Statement in VBA: Expression To Test: The test expression is the condition based on which we would like to execute one of the many available code segments. Various Case Statements: The various expressions to which the above test expression is matched. Case Else: If none of the cases specified match the specified expression, the code mentioned in case else statement would get executed. The basic form of VBA procedure with a select case statement is shown above. VBA Select Case Statement to Check Condition For Numbers Here is a working example of the select case statement.
Match two criteria and return multiple rows in excel | Get Digital Help Question: I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different dates. If I work with vlookup or Index-match I got only the first price for a certain securities. So I am not able to find the price of a securities that match both the name of the securities and the date. Could you advice if there is any way to overcome? Answer: Here are two possible solutions: Create an excel 2007 tableUse an array formula The easiest is the excel 2007 table. Excel 2007 table Select the range Click "Insert" tabClick "Table" Click OK Click "black triangle" on Security header.Select one or more securitiesClick "black triangle" on Date header.Select one or more dates Array formula Array formula in F9: =INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3:$B$19)*COUNTIF($G$4, $C$3:$C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER. How to create an array formula