Excel

Use of symbol # in vba code

2015-04-14T07:59:00.001-07:00

The type-declaration character for Double is the number sign (#). Also called HASH
Other type declaration characters are:

Integer %
Long &
Currency @
Single !
Double #
String $

Don't understand the significance of # here.

It implies that when the expression is evaluated, the number in front of the type declaration character is treated as a specific data type instead of as a Variant.
See this example, which are basically the same.

Sub Sample1()
    Dim a#

    a = 1.2

    Debug.Print a
End Sub

Sub Sample2()
    Dim a As Double

    a = 1.2

    Debug.Print a
End Sub

EDIT
Let me explain it a little more in detail.
Consider this two procedures

Sub Sample1()
    Dim a As Double, b As Integer

    b = 32767
    a = b * 100

    Debug.Print a
End Sub

Sub Sample2()
    Dim a As Double, b As Integer

    b = 32767
    a = b * 100#

    Debug.Print a
End Sub

Question: One of them will fail. Can you guess which one?
Ans: The 1st procedure Sub Sample1() will fail.
Reason:
In Sample2, when you do b * 100# the result of calculation will be of type Double. Since it is within the limits of Double, so the calculation succeeds and the result is assigned to variable a.
Now in Sample1, when you do b * 100 the result of calculation will be of type Integer, since both the operands are of type integer. But the result of calculation exceeds the limits of Integer storage. As a result it will error out.

Dictionary for VBA

2015-04-10T11:20:00.002-07:00

Comparing Dictionaries and Arrays

A dictionary is a general-purpose data structure that looks like a linked list but acts like a "super array." Like VBScript arrays, dictionaries store data and make that data available through one variable. (If you're unfamiliar with VBScript arrays, see my July 1999 column.) However, dictionaries differ from arrays in many ways, including

A dictionary has additional methods to add new items and check for existing items.
You don't need to call ReDim to extend the dictionary's size.
When you delete a particular item from a dictionary, all the subsequent items automatically shift up. For example, if you delete the second item in a three-item dictionary, the original third item automatically shifts up into the second-item slot.
You use keys to identify dictionary items. Keys can be any data subtype, except an array or dictionary.
A dictionary can't be multidimensional. (Although you can't store arrays or dictionaries in a dictionary item, you can store dictionaries in array items.)

The most important reason for using a dictionary instead of an array is that a dictionary is more flexible and is richer in terms of built-in functionality. Dictionaries work better than arrays when you need to access random elements frequently. Dictionaries also work better when you want to locate items by their content rather than their position.

Creating a Dictionary

A dictionary is a COM object (called the Dictionary object) that is available through the Microsoft Scripting Runtime library. The Dictionary object isn't part of the VBScript runtime engine but rather a separate COM object that Microsoft bundles with any installation of VBScript and JScript. As a result, you can use the Dictionary object in any COM-compliant script, including VBScript, JScript, and Perl scripts.
To create a Dictionary object in VBScript code, you use the CreateObject function with the programmatic identifier (ProgID) of Scripting.Dictionary:




Dim dict

Set dict = CreateObject _

("Scripting.Dictionary")

After you've created an instance of the Dictionary object, you're ready to add items to it.

Adding Items

To add items to a dictionary, you use the Add method. You can't insert an item in a specific position in a dictionary; any item you add automatically appears at the bottom of the dictionary.
The Add method has the syntax




dObject.Add key, item

where dObject is the Dictionary object you want to add items to. You must specify two arguments. The first argument, key, specifies the unique key you want to use to identify the item. The second argument, item, is the value you want the item to store. For example, the code




dict.Add "Italy", "Rome"

dict.Add "Germany", "Berlin"

adds two items to the dictionary named dict. The first item's value is Rome. You use the key Italy to access this item. The second item's value is Berlin. You use the key Germany to access this item. In other words, you're adding two key-value pairs—Italy-Rome and Germany-Berlin—to this dictionary.
In a key-value pair, only the key must be unique. A dictionary can have key-value pairs that have the same value. For example, a dictionary might include the items Today's grocery list-Milk and Tomorrow's grocery list-Milk.
If you attempt to add a key-value pair but the key you specify already exists in that dictionary, you receive a runtime error. To avoid this error, you can use the Exists method, which checks whether a key is unique. This method has the syntax




dObject.Exists(key)

where key is the key you want to check. This method returns True if the key exists and False if it doesn't. You can use this method in code similar to that in Listing 1. This code checks the key of a new key-value pair before adding the item to the dictionary. If the key already exists, you receive the message Please specify a new key rather than a runtime error. If the key doesn't exist, the code adds the item's key and value.
As I mentioned earlier, a key can be any data subtype, except an array or dictionary. (For information about the various VBScript subtypes, see my June 1999 column.) For example, you can create a dictionary that uses unique integers rather than strings for keys:




dict.Add 1, "Rome"

dict.Add 2, "Berlin"

Although using integers or other subtypes is permissible, most scriptwriters use strings because strings are the most readable.

Retrieving Items

To retrieve existing items in a dictionary, you use the Item property. This read/write property has the syntax




dObject.Item(key)

where key is the key of the item whose value you want to read. For example, if you want to retrieve and display the value that the Italy item holds, you specify




MsgBox dict.Item("Italy")

However, you can also use the abbreviated syntax




MsgBox dict("Italy")

to display the value Rome because the Item property is the Dictionary object's default property.
If the key is a string, the case in which you specify the key is important. By default, the Dictionary object makes a binary comparison when it tries to find a match between the specified key and the stored keys. Thus, the comparison is case sensitive. So, for example, the code




MsgBox dict("ITALY")

won't display a value because you initially entered the key in title case (i.e., Italy) not upper case (i.e., ITALY). If you try to retrieve a key that doesn't exist, you don't get an error but rather an empty string.
If you want to make a case-insensitive comparison, you can use the Dictionary object's CompareMode property. The code




dict.CompareMode = _

vbTextCompare

forces the object to apply a textual comparison when searching for a match. Therefore, the comparison is case insensitive. You can set the CompareMode property only when the dictionary is empty. Attempting to set this property for a nonempty dictionary results in an error.

Enumerating Items' Keys and Values

VBScript arrays use progressive 0- or 1-based indexes—numbers over which you have no control—to identify items. As a result, you can easily locate an item based on its position in the array, but you can't easily locate an item based on its content. Dictionaries use keys—strings or numbers over which you have complete control—to identify items. As a result, you can easily locate an item based on its content but not so easily locate an item based on its position. No workaround exists to make arrays support customizable keys; however, you can use a workaround to make dictionaries behave like indexed arrays.
To begin, let's walk through a dictionary and enumerate all the items' values and keys. You can use a For Each...Next statement such as




For Each elem In dict

MsgBox elem & " - " _

& dict(elem)

Next

where elem is a variable that represents each item's key (not its value). For each item in the dictionary, you display the key and the value concatenated together with a hyphen in between. To retrieve the key, you specify elem. To retrieve the value, you use the abbreviated Item property; however, instead of hard-coding the key, you specify the elem variable as the argument.
You can rewrite this For Each...Next statement so that it uses indexes, as Listing 2 shows. In Listing 2, you begin by creating an array called arr. Next, you use the Dictionary object's Items method, which fills an array with all the values in a dictionary. The Items method has the syntax




dObject.Items()

Including the parentheses after Items is optional. However, as you'll see shortly, including them is a good habit to get into.
In Listing 2, you're using the Items method to fill arr with the values in dict. After filling the array, you use the For...Next statement to traverse it and display all the values in one message box. Because you might not know how many values are in the array, you use the Count property to read the number of items that the Dictionary object has. By subtracting 1 from that number, you get the array's upper bound.
Because the Items method returns an array filled with all a dictionary's values, you can use this method to access a dictionary's content through indexes. You just need to specify which index you want to access with the syntax




dObject.Items()(i)

where i is a variable index representing the index of the dictionary item you want to access. For example, if you want to access and display the first item, you specify




MsgBox dict.Items()(0)

Thus, you can access an item in a dictionary without knowing the item's key. However, when you use indexes, you must remember to specify the index number in the second set of parentheses. If you use the code




MsgBox dict.Items(0)

you'll receive an error, which is why including the empty parentheses after the Items method is a good habit to get into.
Instead of filling an array with a dictionary's values, you can fill an array with a dictionary's keys. You use the Keys method, which has the syntax




dObject.Keys()

If you want to use an index to access a dictionary item's key, you use the code




dObject.Keys()(i)

where i is a variable index representing the index of the dictionary item for which you want to specify the key.

Deleting Items and Changing Keys

To delete an item, you use the Remove method, which takes the key of the item you want to delete as its sole argument. For example, if you want to remove the key-value pair of Italy-Rome, you specify




dict.Remove ("Italy")

When you remove an item, all the items after the one you removed shift up one position. In addition, the value of the Count property changes.
The Remove method accepts only keys as arguments. If you want to remove the ith item from a Dictionary object, you need to use a workaround that uses the Remove and Keys methods:




dict.Remove dict.Keys()(i)

where i is a variable index representing the index of the dictionary item you want to delete. If you want to delete all the items in a dictionary, you use the RemoveAll method. As the syntax




dObject.RemoveAll

shows, RemoveAll doesn't take any arguments. Using RemoveAll is significantly faster than removing each item individually, especially if the dictionary has a lot of items.
If you want to change a key in a key-value pair, you don't need to delete the item then add a new one. Instead, you can use the Key property to change the key. This property has the syntax




dObject.Key(key) = newkey

where key is the key you want to change and newkey is the key you want to change it to.

You can't change a value in a key-value pair. If you want a different value, you need to delete the item, then add a new one.

Variable Cell Reference

2015-01-22T11:42:00.003-08:00

Question:
If I want to return a value from a specific cell ie B3 you type in =B3, but in my case the row value is variable and I'm using a Vlookup function to determine the row, so I'm using the formula ="B"&A1 where A1 = the value "8", but in the cell just =B* appears, not the contents of cell B8.

In other words I want a formula to display the value of a specific cell, the column is fixed but the row is variable and is determined by another formula.

solution:
=indirect("B"&A1)

Another example:

=SUM(INDIRECT("'MM'!G"&'BE Summary'!$AZ$2+1+A10):INDIRECT("'MM'!H"&'BE Summary'!$AZ$2+1+A10))

In example above, if cell AZ on tab 'BE Summary" = 10 and cell A10 on current sheet is 2 then the formula become =SUM('MM'!G13:'MM'!H13)
note: 10 + 1 + 2 = 13

Remove custom styles in Excel using vba (also bring back default styles if missing)

2015-01-22T08:48:00.001-08:00

Sub TrimStyles()
Dim s As Variant
Dim g As Style
On Error Resume Next
With ActiveWorkbook
For Each g In .Styles
If g.BuiltIn = False Then
s = g.Value
.Styles(s).Delete
End If
Next g
On Error GoTo 0
End With
End Sub

If still cannot delete some custom styles, use steps below
1. open excel file with unzip program such as winzip, winrar.
2. go to folder xl then open styles.xml with a text editor program such as notepad.
3. search for "cellStyles" then delete the unwanted custom styles. (Also search for "cellstyle" if "cellStyles" not found)
4. search customBuiltin="1" for default styles and change "1" to "0" in case you want to delete the custom changes on default styles.

Excel Keyboard shortcut

2015-01-22T08:43:00.002-08:00

Insert a New Row or Column
Method 1:
Select the current row: Shift+SpaceBar
Insert a row (above the currently selected row): Ctrl+"+"
To insert a new column, in Step 1 use Ctrl+SpaceBar to select the current column.
To delete a row (or column) after it is selected, press Ctrl+"-"
Method 2:
Alt-i, r = insert row
Alt-i, c = insert column
If you want to repeat and do more of each, you could just hit F4.

Autofill
Ctrl+R is fill to the right (Ctrl+D is fill down)

Display result of part of the formula
Highlight part of formula, press F9 to display result of that part of the formula

Show formulas instead of calculated values for entire sheet
Ctrl+"`"

Auto Fill VBA

2014-02-27T12:20:00.004-08:00

Public Sub MyAutoFill()

'Declare range Variables
Dim selection1 As Range
Dim selection2 As Range

Set selection1 = Sheet1.Range("A1:A2")
Set selection2 = Sheet1.Range("A1:A20")
'Autofill
selection1.AutoFill Destination:=selection2

End Sub

How to find or replace asterisk(*) and question mark(?) in Excel?

2014-02-03T10:43:00.002-08:00

Asterisk and question mark are excel’s wildcard characters. If you try to search or replace them in excel they won’t show up. Like if you ever try to replace the * in a formula within a cell with Blank, your whole cell will go blank. To overcome this problem, you need to add tilde(~) key before the multiplication sign(*) or question mark (?) sign in find and replace box.

F9 in Excel to Display result of part of the formula

2014-01-31T11:57:00.001-08:00

Highlight part of formula, press F9 to display result of that part of the formula

Excel coverts General or Number format to Date or other format automatically when reopen

2014-01-13T08:30:00.002-08:00

Go to Home tab ==> style section ==> Normal ==> Modify ==> Format ==> Select General

Use of symbol in VBA variables

2013-11-25T08:39:00.003-08:00

The type declaration characters are:

Integer %
Long &
Currency @
Single !
Double #
String $

It implies that when the expression is evaluated, the number in front of the type declaration character is treated as a specific data type instead of as a Variant.
See example below, which are basically the same.

Sub Sample1()
    Dim a#

    a = 1.2

    Debug.Print a
End Sub

Sub Sample2()
    Dim a As Double

    a = 1.2

    Debug.Print a
End Sub

Consider this two procedures below:

Sub Sample1()
    Dim a As Double, b As Integer

    b = 32767
    a = b * 100

    Debug.Print a
End Sub

Sub Sample2()
    Dim a As Double, b As Integer

    b = 32767
    a = b * 100#

    Debug.Print a
End Sub

Esc key or other cancel key event

2013-04-15T11:41:00.001-07:00

Use Application.EnableCancelKey =
Controls how Microsoft Excel handles CTRL+BREAK (or ESC or COMMAND+PERIOD)
user interruptions to the running procedure.

xlDisabled:
Cancel key trapping is completely disabled.

xlInterrupt:
The current procedure is interrupted, and the user can debug or
end the procedure.

xlErrorHandler:
The interrupt is sent to the running procedure as an error,
trappable by an error handler set up with an On Error GoTo statement.
The trappable error code is 18.

example:

On Error GoTo handleCancel 
Application.EnableCancelKey = xlErrorHandler 
MsgBox "This may take a long time: press ESC to cancel" 
For x = 1 To 1000000 ' Do something 1,000,000 times (long!) 
 ' do something here 
Next x 
 
handleCancel: 
If Err = 18 Then 
 MsgBox "You cancelled" 
End If

Remarks
Use this property very carefully. If you use xlDisabled, there's no way
to interrupt a runaway loop or other non self-terminating code.
Likewise, if you use xlErrorHandler but your error handler always
returns using the Resume statement, there's no way to stop runaway code.
The EnableCancelKey property is always reset to xlInterrupt whenever
Microsoft Excel returns to the idle state and there's no code running.
To trap or disable cancellation in your procedure, you must explicitly
change the EnableCancelKey property every time the procedure is called.

VBA send e-mail

2013-03-18T11:37:00.001-07:00

Sub Send_Email_Using_VBA()
Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
Email_Subject = "Trying to send email using VBA"
Email_Send_From = "databison@gmail.com"
Email_Send_To = "databison@gmail.com"
Email_Cc = "databison@gmail.com"
Email_Bcc = "databison@gmail.com"
Email_Body = "Congratulations!!!! You have successfully sent an e-mail using VBA !!!!"
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
.send
End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub

VBA check e-mail input

2013-03-18T11:36:00.002-07:00

Function IsEmailAddress(txt As String) As Boolean
With CreateObject("VBScript.RegExp")
.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
IsEmailAddress = .test(txt)
End With
End Function

How to look up a value in a list and return multiple corresponding values

2013-03-13T21:07:00.001-07:00

Identify the row numbers that contain the name $A$10 in column A (Use Row(1:1) because Data starts in row1)

=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))

Return the corresponding values from column B,

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)

and remove possible error values.

=IF(ISERROR(INDEX($A$1:$B$7,SMALL( IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

Use (CTRL+SHIFT+ENTER) to input formula

VBA Set Dynamic Chart Minimum and Maximum etc

2013-03-12T13:50:00.000-07:00

Option Explicit
Private Sub ChangeAxisScales()
    With ActiveSheet.ChartObjects("Chart 1").Chart
        ' Category (X) Axis, use .Axes(xlCategory, xlPrimary) for primary if there is secondary axis.
        With .Axes(xlCategory)
            .MaximumScale = ActiveSheet.Range("$E$2").Value
            .MinimumScale = ActiveSheet.Range("$E$3").Value
            .MajorUnit = ActiveSheet.Range("$E$4").Value
        End With
        ' Value (Y) Axis
        With .Axes(xlValue)
            .MaximumScale = ActiveSheet.Range("$F$2").Value
            .MinimumScale = ActiveSheet.Range("$F$3").Value
            .MajorUnit = ActiveSheet.Range("$F$4").Value
        End With
    End With

Check if a user form is loaded

2013-02-27T15:45:00.002-08:00

Function IsUserFormLoaded(ByVal UFName As String) As Boolean 
    Dim UForm As Object 
     
    IsUserFormLoaded = False 
    For Each UForm In VBA.UserForms 
        If UForm.Name = UFName Then 
            IsUserFormLoaded = True 
            Exit For 
        End If 
    Next 
End Function 'IsUserFormLoaded

Solver in Excel and VBA

2013-02-26T10:40:00.004-08:00

Quoted from http://peltiertech.com/Excel/SolverVBA.html

Solver is a powerful analysis tool, bundled with Excel and used for optimization and simulation of business and engineering models. It can be even more powerful if used in conjunction with VBA, to automate solving of multiple models which use different input parameters and constraints.
In a simple example, there are two factors in B5 and B6. The product (=B5*B6) is calculated in B8. Solver will be used to find the maximum value of the target cell (the product in B8), subject to the constraint that both factors (B5:B6) shall not exceed a value of 4. Select Solver from the Tools menu, and enter the appropriate conditions and constraints in the Solver Parameters dialog.

Click the Solve button, and another dialog indicates whether a solution is found and offers some options.

If you record a macro while you use Solver, you will get something like the following:

Sub SolverMacro1()
'
' SolverMacro1 Macro
' Macro recorded by Jon Peltier
'
    SolverOk SetCell:="$B$8", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$5:$B$6"
    SolverAdd CellRef:="$B$5:$B$6", Relation:=1, FormulaText:="4"
    SolverOk SetCell:="$B$8", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$5:$B$6"
    SolverSolve
End Sub

SolverAdd adds constraints to the Solver model. SolverOK defines the cell to optimize, how to optimize it, and what cells to change during the Solver optimization. The macro recorder wrote this line twice, so the first occurrence can be removed. To prevent parameters from a different Solver optimization interfering with the macro's optimization, Solver should be reset prior to running, using SolverReset. SolverSolve has an optional UserFinish argument; if UserFinish is False or omitted, the second dialog shown above will ask the user whether to save the optimization, but if UserFinish is True, Solver will end without the dialog. A modified Solver macro is shown below:

Sub SolverMacro2()
'
' SolverMacro2 Macro
' Macro fixed up by Jon Peltier
'
    SolverReset
    SolverAdd CellRef:="$B$5:$B$6", Relation:=1, FormulaText:="4"
    SolverOk SetCell:="$B$8", MaxMinVal:=1, ValueOf:="0", ByChange:="$B$5:$B$6"
    SolverSolve True
End Sub

When you try to run this macro, you get a compile error. The command SolverReset is highlighted, and the following error message appears.

In order to use a macro based on an installed add-in, you must first make sure that the add-in is installed, then you must set a reference to the add-in in the workbook containing the code that calls the add-in's procedures.
To install an add-in, on Excel's Tools menu, choose Add-Ins. If the add-in is shown on the list, check the box in front of its name. If the add-in is not found, click Browse, navigate to the add-in file*, then when it appears on the add-in list, check its checkbox. Solver was already installed, or we would not have been able to record a macro using it.
*Depending on your Office and Windows versions, the default Excel add-ins library is "C:\Program Files\Microsoft Office\OFFICE11\Library" or "C:\Documents and Settings\{username}\Application Data\Microsoft\AddIns". By default in Excel 2003, Solver is located in "C:\Program Files\Microsoft Office\OFFICE11\Library\SOLVER".

To set a reference to an add-in, it must first be installed. Then on the VB Editor's Tools menu, select References. This lists all open workbooks and installed add-ins, as well as a huge list of resources installed on the host computer. Find the add-in in the list, and check the box in front of its name.

With a reference set to Solver, SolverMacro2 will run as expected. In addition, the Solver library will be accessible through the VB Editor's Object Browser (right), and you will have the benefit of Intellisense (below) while editing code that uses members of the Solver library.

Avoiding Solver Reference Problems

The code you write to run Solver will work on your computer, and on any computer with the same versions of Excel and Solver. In fact, it should work on any computer that has later versions of Excel and Solver. If you want to distribute your workbook with VBA code written for Solver, you should write the code using the earliest expected version of Excel (e.g., Excel 2000), so it will work on all versions that users may have installed (e.g., Excel 2000, 2002, and 2003). When the workbook is first opened on a given computer, it finds the references resources, or more recent versions if available.
This sounds easy, but sometimes it isn't. Perhaps you developed a workbook in Excel 2003 for your department to use, but you have to send it to a supplier, and the supplier hasn't upgraded past Excel 2000. Or perhaps the workbook must be shared amongst a group of users who have different versions of Excel and Solver installed. In these cases, a computer with an earlier version of Solver installed will choke on the reference to a later version of Solver.
It is possible, of course, to install add-ins and set references using VBA. This can be tricky, and in Microsoft Office 2002 and later, the user has to grant permission for VBA code to access any VB projects. Without this permission, references to installed components cannot be set.
To avoid issues with installing add-ins and setting references to various resources, your code can be modified so that it is called using Application.Run. Without a reference to the add-in, you lose IntelliSense and the Object Browser, and your code suffers from a small (probably imperceptible) performance penalty. However, you gain simpler, more reliable execution. The syntax is straightforward: Application.Run is followed by the procedure name in double quotes, followed by a comma separated list of arguments being passed to the procedure:

Application.Run "SubName", Argument1, Argument2,...

If Application.Run is used to return the calculated result of a function, the syntax is slightly different, with a variable set equal to Application.Run, with the procedure and arguments enclosed within parentheses:

MyVariable = Application.Run("Function", Argument1, Argument2,...)

The SolverMacro2 procedure above is easily modified to use Application.Run:

Sub SolverMacro3()
'
' SolverMacro3 Macro
' Macro fixed up by Jon Peltier
' Edited to use Application.Run to avoid reference problems
'
    Application.Run "SolverReset"
    Application.Run "SolverAdd", "$B$5:$B$6", 1, "4"
    Application.Run "SolverOk", "$B$8", 1, "0", "$B$5:$B$6"
    Application.Run "SolverSolve", True
End Sub

A more general version of a Solver procedure is shown below. This includes more informative comments, and it provides a notice to the user about the success of the Solver optimization.

Sub RunSolver()
  '' Adjusted for Application.Run() to avoid Reference problems with Solver 
  '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved.

  ' reset
  Application.Run "Solver.xla!SolverReset"

  ' set up new analysis
  Application.Run "Solver.xla!SolverOk", "Blah1", 1, , "BlahBlah1"

  ' add constraints
  Application.Run "Solver.xla!SolverAdd", "Blah2", 3, 0
  Application.Run "Solver.xla!SolverAdd", "Blah3", 2, "BlahBlah3"

  ' run the analysis
  Result = Application.Run("Solver.xla!SolverSolve", True)

  ' finish the analysis
  Application.Run "Solver.xla!SolverFinish"

  ' report on success of analysis
  If Result <= 3 Then
    ' Result = 0, Solution found, optimality and constraints satisfied
    ' Result = 1, Converged, constraints satisfied
    ' Result = 2, Cannot improve, constraints satisfied
    ' Result = 3, Stopped at maximum iterations
    MsgBox "Solver found a solution", vbInformation, "SOLUTION FOUND"
  Else
    ' Result = 4, Solver did not converge
    ' Result = 5, No feasible solution
    Beep
    MsgBox "Solver was unable to find a solution.", vbExclamation, "SOLUTION NOT FOUND"
  End If

End Sub

The results of the SolverSolve function include:

      0  Solver found a solution. All constraints and optimality conditions are satisfied. 
      1  Solver has converged to the current solution. All constraints are satisfied. 
      2  Solver cannot improve the current solution. All constraints are satisfied. 
      3  Stop chosen when the maximum iteration limit was reached. 
      4  The Set Cell values do not converge. 
      5  Solver could not find a feasible solution. 
      6  Solver stopped at user's request. 
      7  The conditions for Assume Linear Model are not satisfied. 
      8  The problem is too large for Solver to handle. 
      9  Solver encountered an error value in a target or constraint cell. 
     10  Stop chosen when maximum time limit was reached. 
     11  There is not enough memory available to solve the problem. 
     12  Another Excel instance is using SOLVER.DLL. Try again later. 
     13  Error in model. Please verify that all cells and constraints are valid.

Preparing Solver for First Use

One frequent complaint about automating Solver is that it doesn't work using VBA until it has been used at least once manually. This is because Solver installs itself in a kind of "on demand" mode. Unlike a regularly-installed add-in, it is not opened until it is first used. And until it is first used, it hasn't run its Auto_Open procedure, which is what actually prepares it to run. Using VBA you can bypass the initial manual Solver operation with this command:

Application.Run "Solver.xla!Solver.Solver2.Auto_open"

This command should be run before the first Solver optimization procedure is executed. I have developed a Solver initialization routine that first makes sure the computer even has Solver, then it installs it and runs its Auto_Open procedure. The procedure is written as a function, which returns True if Solver is available and ready to use. I usually call this procedure from the parent workbook's Workbook_Open event procedure. If CheckSolver is False, I usually have the workbook close itself after a brief warning to the user.

Function CheckSolver() As Boolean
  '' Adjusted for Application.Run() to avoid Reference problems with Solver 
  '' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved.
  '' Returns True if Solver can be used, False if not.

  Dim bSolverInstalled As Boolean

  '' Assume true unless otherwise
  CheckSolver = True

  On Error Resume Next
  ' check whether Solver is installed
  bSolverInstalled = Application.AddIns("Solver Add-In").Installed
  Err.Clear

  If bSolverInstalled Then
    ' uninstall temporarily
    Application.AddIns("Solver Add-In").Installed = False
    ' check whether Solver is installed (should be false)
    bSolverInstalled = Application.AddIns("Solver Add-In").Installed
  End If

  If Not bSolverInstalled Then
    ' (re)install Solver 
    Application.AddIns("Solver Add-In").Installed = True
    ' check whether Solver is installed (should be true)
    bSolverInstalled = Application.AddIns("Solver Add-In").Installed
  End If

  If Not bSolverInstalled Then
    MsgBox "Solver not found. This workbook will not work.", vbCritical
    CheckSolver = False
  End If

  If CheckSolver Then
    ' initialize Solver 
    Application.Run "Solver.xla!Solver.Solver2.Auto_open"
  End If

  On Error GoTo 0

End Function

The function above works fine for English versions of Excel, but in other languages, the name of the add-in may not be "Solver Add-In". We have to be a bit more clever, and introduce a loop to check the filenames of all add-ins. The CheckSolverIntl function below calls two additional functions which perform the loops. This function still relies on Solver being named "solver.xla". If this is not the case, change the value of the constant sAddIn in this procedure, and please email me about it.

Function CheckSolverIntl() As Boolean
  '' Adjusted for Application.Run() to avoid Reference problems with Solver
  '' Adjusted for international versions of Excel
  '' Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
  '' Returns True if Solver can be used, False if not.

  Dim bSolverInstalled As Boolean
  Dim bAddInFound As Boolean
  Dim iAddIn As Long
  Const sAddIn As String = "solver.xla"

  '' Assume true unless otherwise
  CheckSolverIntl = True

  On Error Resume Next
  ' check whether Solver is installed
  bSolverInstalled = IsInstalled(sAddIn)
  Err.Clear

  If bSolverInstalled Then
    ' uninstall temporarily
    bAddInFound = AddInInstall(sAddIn, False)
    ' check whether Solver is installed (should be false)
    bSolverInstalled = IsInstalled(sAddIn)
  End If

  If Not bSolverInstalled Then
    ' (re)install Solver
    bAddInFound = AddInInstall(sAddIn, True)
    ' check whether Solver is installed (should be true)
    bSolverInstalled = IsInstalled(sAddIn)
  End If

  If Not bSolverInstalled Then
    MsgBox "Solver not found. This workbook will not work.", vbCritical
    CheckSolverIntl = False
  End If

  If CheckSolverIntl Then
    ' initialize Solver
    Application.Run "Solver.xla!Solver.Solver2.Auto_open"
  End If

  On Error GoTo 0

End Function

Function IsInstalled(sAddInFileName As String) As Boolean
  Dim iAddIn As Long
  
  IsInstalled = False
  
  For iAddIn = 1 To Application.AddIns.Count
    With Application.AddIns(iAddIn)
      If LCase$(.Name) = LCase$(sAddInFileName) Then
        If .Installed Then
          IsInstalled = True
        End If
        Exit For
      End If
    End With
  Next
  
End Function

Function AddInInstall(sAddInFileName As String, bInstall As Boolean) As Boolean
  Dim iAddIn As Long
  
  For iAddIn = 1 To Application.AddIns.Count
    With Application.AddIns(iAddIn)
      If LCase$(.Name) = LCase$(sAddInFileName) Then
        If .Installed <> bInstall Then
          .Installed = bInstall
        End If
        AddInInstall = True ' True = add-in is listed
        Exit For
      End If
    End With
  Next
  
End Function

VBA Error handling

2013-02-12T14:23:00.001-08:00

From Microsoft website:

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following is an example of the basic structure of a macro that uses error-handling:

   Sub MyMacro()

        On Error GoTo ErrorHandler
        . . .
        Exit Sub

   ErrorHandler:
        . . .
        Resume <or Exit Sub>
        . . .

   End Sub

The example contains the following elements: an On Error statement (On Error GoTo ErrorHandler) and a Resume statement. The error handler might contain an Error Statement and/or an Error function. Each of these elements is discussed in greater detail in later sections of this article.

Note that in this example an Exit Sub statement precedes the error handler label. By placing Exit Sub or Exit Function before the error handling routine, you prevent the error-handling code from being run when the macro runs without error.

On Error Statement

The On Error statement enables your application to handle errors that your macro encounters. If you do not use an On Error statement in your procedures, any run-time error that occurs is fatal: that is, Microsoft Excel will generate a run-time error message and the macro will stop running.

The following table outlines the On Error statement syntax and describes each type of statement.

This On Error statement Does the following

On Error GoTo <line>
Enables the error-handling routine that starts at <line>, which is any line label or line number. The specified line must be in the same procedure as the On Error statement.
On Error Resume Next
Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred. In other words, execution continues.
On Error GoTo 0
Disables any enabled error handler in the current procedure.

Resume Statement

Your error-handling routine will need to determine where macro control should go when an error has occurred. To end the macro when an error has occurred, place the error handling routine either immediately before the End Sub (or End Function) statement or use the Exit Sub (or Exit Function) statement. To return control to another location within the macro, use the Resume statement.

This Resume statement Does the following

Resume [0]
Resumes macro execution with the line that caused the error. [0] is an optional argument.
Resume Next
Resumes macro execution with the line following the line that caused the error.
Resume <line>
Resumes macro execution at the line number or line label specified by <line>.

NOTE: A very common mistake in writing error handling routines is to use a GoTo statement in the routine to specify where macro control should go. Generally, it is not recommended that you use GoTo in an error handling routine; you should use the Resume statement to resume macro execution outside of the error handler.

Error Statement and Error Function

You can make your error-handling routine more effective by determining what error has occurred. When a run-time error occurs, you can use the Err function to determine the error number; the Error statement is used to get the text that describes the error. For example, you can use the following statement to display the description of the error that was encountered:

   MsgBox Err & ": " & Error(Err)

If the error handling routine encountered the error 13 (a type mismatch error), the following text would appear in a dialog box:

13: Type Mismatch

EXAMPLES OF MACROS THAT USE ERROR HANDLING

In these examples, the error handler may be called if there is no disk in drive B, if the B:\XLFiles path cannot be located, or if there is no Book1.xls file in the B:\XLFiles folder (directory).

Example 1--Basic Error Handling Macro

   Sub MyMacro()

      Dim MyWorkbook As Workbook

      ' Run the Error handler "ErrHandler" when an error occurs.
      On Error GoTo Errhandler

      ChDrive "B:"
      ChDir "B:\"
      ChDir "B:\XLFiles"
      Workbooks.Open "Book1.xls"

      ' Disable the error handler.
      On Error GoTo 0

      Set MyWorkbook = ActiveWorkbook
      MsgBox "The destination workbook is " & MyWorkbook.Name

      ' Exit the macro so that the error handler is not executed.
      Exit Sub

   Errhandler:

      ' If an error occurs, display a message and end the macro.
      MsgBox "An error has occurred. The macro will end."

   End Sub

This example uses the On Error statement to display a message and end a macro when an error occurs. If an error occurs in the macro, the error handler displays the following error message and the macro execution is halted:

An error has occurred. The macro will end.

If the workbook Book1.xls is successfully opened, a message is displayed, showing the destination workbook, and the macro ends because there is an Exit Sub statement before the error handler label "ErrHandler."

Example 2--Error Handling Macro That Displays a Specific Error Message

This next example is similar to the macro in Example 1; however, this macro implements the Error statement and the Err function to show a more descriptive error message when an error is encountered.

   Sub MyMacro()

      Dim MyWorkbook As Workbook

      ' Run the Error handler "ErrHandler" when an error occurs.

      On Error GoTo Errhandler
      ChDrive "B:"
      ChDir "B:\"
      ChDir "B:\XLFiles"
      Workbooks.Open "Book1.xls"

      ' Disable the error handler.
      On Error GoTo 0

      Set MyWorkbook = ActiveWorkbook
      MsgBox "The destination workbook is " & MyWorkbook.Name

      ' Exit the macro so that the error handler is not executed.
      Exit Sub

   Errhandler:

      Select Case Err

         Case 68, 75:    ' Error 68: "Device not available"
                         ' Error 75: "Path/File Access Error"
            MsgBox "There is an error reading drive B."

         Case 76:        ' Error 76: "Path not found"
            MsgBox "The specified path is not found."

         Case Else:      ' An error other than 68, 75 or 76 has occurred.
            ' Display the error number and the error text.
            MsgBox "Error # " & Err & " : " & Error(Err)

      End Select

      ' End the macro.
   End Sub

If an error occurs in the macro one of the following will occur:

If the error is either 68 or 75, the message "There is an error reading drive B" will be displayed, and the macro will end.
If the error is 76, the message "The specified path is not found" will be displayed, and the macro will end.
If the error is an error other than 68, 75, or 76, the message "Error <error number>: <error text>" will be displayed and the macro will end.

If the Book1.xls workbook is successfully opened, then a message will be displayed showing the destination workbook and the macro will end because there is an Exit Sub statement before the error handler label "ErrHandler."

Example 3--Macro that Uses the Resume Statement

This next example uses the Resume statement to resume macro execution based on choices that the user makes when an error occurs.

   Sub MyMacro()

   Dim Result as Integer
   Dim ErrMsg as String
   Dim MyWorkbook as Workbook

      ' Run the Error handler "ErrHandler" when an error occurs.
      On Error GoTo Errhandler

      ChDrive "B:"
      ChDir "B:\"
      ChDir "B:\XLfiles"

      Workbooks.Open "Book1.xls"

   NewWorkbook:

      ' Disable the error handler.
      On Error GoTo 0

      Set MyWorkbook = ActiveWorkbook
      MsgBox "The destination workbook is " & MyWorkbook.Name

      ' Exit the macro so that the error handler is not executed.
      Exit Sub

   Errhandler:

      Select Case Err

         Case 68, 75:  ' Error 68: "Device not available"
                       ' Error 75: "Path/File access error
            ErrMsg =  "There is an error reading drive B. Please " & _
               "insert a disk and then press OK to continue or " & _
               "press Cancel to end this operation."

            Result = MsgBox(ErrMsg, vbOKCancel)

            ' Resume at the line where the error occurred if the user
            ' clicks OK; otherwise end the macro.
            If Result = vbOK Then Resume

         Case 76:     ' Error 76: Path not found
            ErrMsg = "The disk in drive B does not have an XLFiles " & _
               "directory. Please insert the correct disk."

            Result = MsgBox(ErrMsg, vbOKCancel)

            ' Resume at the line where the error occurred if the user
            ' clicks OK; otherwise end the macro.
            If Result = vbOK Then Resume

         Case Else:   ' A different error occurred.

            ErrMsg = "An error has occurred opening " & _
                 "B:\XLFiles\Book1.xls. Use the active workbook as " & _
                 "the destination?"

            Result = MsgBox(ErrMsg, vbYesNo)

            ' Resume at the label "NewWorkbook" if the user clicks Yes;
            ' otherwise end the macro.
            If Result = vbYes Then Resume NewWorkbook

      End Select

   ' End the macro.
   End Sub

If the workbook Book1.xls is successfully opened, a message will be displayed showing the destination workbook as Book1.xls and the macro will end because there is an Exit Sub statement before the error handler label "ErrHandler." If an error occurs in the macro, the error handler will do one of the following:

If the error is either 68 or 75, then the following message is displayed:
There is an error reading drive B. Please insert a disk and then press OK to continue or press Cancel to end this operation.
If the user clicks OK in this dialog box, the macro resumes at the line where the error occurred. If the user clicks Cancel, the macro will end.
If the error is 76, then the following message will be displayed:
The disk in drive B does not have an XLFiles directory. Please insert the correct disk.
If the user clicks OK in this dialog box, the macro resumes at the line where the error occurred. If the user clicks Cancel, the macro will end.
If the error is an error other than 68, 75 or 76, then the following error message is displayed:
An error has occurred opening B:\XLFiles\Book1.xls. Use the active workbook as the destination?
If the user clicks Yes in the dialog box, the macro resumes at the line labeled "NewWorkbook." The currently active workbook is displayed as the destination workbook. If the user clicks No, the macro will end.

Example 4--Centralizing Error Handling

You can reduce the length of overall code in your application by centralizing the error handling. You can centralize error-handling by creating one or more procedures that handle common errors.

The following is a procedure called ErrorHandling that will display a message corresponding to the error number (ErrorValue) that was passed to it and, where possible, allow the user to choose a button to specify which action should be taken following the error. Based on the choice that the user makes, the ErrorHandling procedure will return a value (ReturnValue) for the course of action to the calling procedure. The ReturnValue can be Err_Exit (exit the macro where the error occurred), Err_Resume (resume at the line in the macro where the error occurred), or Err_Resume_Next (resume at the line following the line in the macro where the error occurred).

   Public Const Err_Exit = 0
   Public Const Err_Resume = 1
   Public Const Err_Resume_Next = 2

   Sub ErrorHandling(ErrorValue As Integer, ReturnValue As Integer)

      Dim Result as Integer
      Dim ErrMsg as String
      Dim Choices as Integer

      Select Case ErrorValue

         Case 68:     ' Device  not available.

            ErrMsg = "The device you are trying to access is either " & _
               "not online or does not exist. Retry?"
            Choices = vbOKCancel

         Case 75:     ' Path/File access error.

            ErrMsg = "There is an error accessing the path and/or " & _
                 "file specified. Retry?"
            Choices = vbOKCancel

         Case 76:     ' Path not found.

            ErrMsg = "The path and/or file specified was not found. Retry?"
            Choices = vbOKCancel

         Case Else:   'An error other than 68, 75 or 76 has occurred

            ErrMsg = "An unrecognized error has occurred ( " & _
               Error(Err) & " ). The macro will end."
            MsgBox ErrMsg, vbOKOnly
            ReturnValue = Err_Exit
            Exit Sub

      End Select

      ' Display the error message.
      Result = MsgBox(ErrMsg, Choices)

      ' Determine the ReturnValue based on the user's choice from MsgBox.
      If Result = vbOK Then
         ReturnValue = Err_Resume
      Else
         ReturnValue = Err_Exit
      End If

   End Sub

This next macro demonstrates how you could use the ErrorHandling procedure when an error is encountered:

   Sub MyMacro()

      Dim Action As Integer

      ' Run the Error handler "ErrHandler" when an error occurs.
      On Error GoTo Errhandler

      ChDrive "B:"
      ChDir "B:\"
      ChDir "B:\XLFiles"
      Workbooks.Open "Book1.xls"

      ' Exit the macro so that the error handler is not executed.
      Exit Sub

   Errhandler:

      ' Run the ErrorHandling macro to display the error and to
      ' return a value for Action which will determine the appropriate
      ' action to take (Resume the macro or end the macro)

      ErrorHandling Err, Action

      If Action = Err_Exit Then
         Exit Sub
      ElseIf Action = Err_Resume Then
         Resume
      Else
         Resume Next
      End If

   End Sub

Exit Sub in error handler

2013-02-12T13:26:00.000-08:00

Exit Sub statement precedes the error handler label. By placing Exit Sub or Exit Function before the error handling routine, you prevent the error-handling code from being run when the macro runs without error.

Close workbook with or without saving VBA

2013-02-12T11:09:00.001-08:00

Sub CloseNoSave() 'Close the workbook without saving it ThisWorkbook.Close savechanges:=False End Sub Sub CloseForceSave() 'Save the workbook, then close it ThisWorkbook.Close savechanges:=True End Sub

Resize Array

2013-02-08T15:37:00.000-08:00

Dim n As Integer n = 1 ReDim Preserve MyArray(1 To n) MyArray(n) = "StressScenarioReport" n = n + 1 If AssumptionInput.chkStressLeverage.Value = True Then ReDim Preserve MyArray(1 To n) MyArray(n) = "StressScenarioReport_Leverage" n = n + 1 End If ' MyArray now will contain "StressScenarioReport" and "StressScenarioReport_Leverage". If remove "Preserve", this will erase any previous data existing in MyArray

Open text file and convert containing text to string

2013-01-15T11:43:00.000-08:00

Sub Test()
Dim fname As String
fname = ActiveWorkbook.path & "\Aggregated periods.txt"
strSql = OpenTextFileToString2(fname)
'replace word in string
strSql = Replace(strSql, "& core_id", core_id)
'Extract port of string embraced by "Aggregated_periods"
strSql = ExtractPartOfPath(strSql, "Aggregated_periods")
End Sub

Function OpenTextFileToString2(ByVal strFile As String) As String
Dim hFile As Long
hFile = FreeFile
Open strFile For Input As #hFile
OpenTextFileToString2 = Input$(LOF(hFile), hFile)
Close #hFile
End Function

Function ExtractPartOfPath(path As Variant, DivMark As String) As String
Dim first, second As Integer
first = InStr(path, DivMark)
second = InStr(first + 1, path, DivMark)
ExtractPartOfPath = Mid(path, first + Len(DivMark), second - first - Len(DivMark) - 1)
End Function

Open Embedded file or Object using VBA

2013-01-08T08:18:00.002-08:00

Sub ActivateObj()
'// Opens object
Dim oEmbFile As Object
Application.DisplayAlerts = False
'// Either reference the OLEObject by index
'// or by name if you Name the Object
'// Note:
'// Sheets(2) substitute with your Sheet
Set oEmbFile = ThisWorkbook.Sheets(1).OLEObjects(1)
oEmbFile.Verb Verb:=xlPrimary
Set oEmbFile = Nothing
Application.DisplayAlerts = True
End Sub

Getting text file into a VBA string variable

2013-01-08T08:01:00.002-08:00

Sub Tester4()
Dim fname As String
Dim sVal As String
fname = "C:\test\test.txt"
sVal = OpenTextFileToString2(fname)
Debug.Print sVal
End Sub

Function OpenTextFileToString2(ByVal strFile As String) As String
Dim hFile As Long
hFile = FreeFile
Open strFile For Input As #hFile
OpenTextFileToString2 = Input$(LOF(hFile), hFile)
Close #hFile
End Function

VBA Null value

2012-12-10T11:28:00.003-08:00

VBA data type date and number cannot hold Null value, but database can. Try to set data type to variant in VBA instead.