VBA Tips and TricksVBA Tips and TricksVBA Tips and Tricks is blog on Excel VBA, Word VBA code, examples and methods that help programmers code. Articles
Check if Code is Trusted Outlook VBA
2008-06-01 09:54:00 Check if Code is Trusted Outlook VBA Function IsCodeTrusted() As Boolean IsCodeTrusted = Application.IsTrusted End Function For out-of-process callers that have instantiated the Application object, IsTrusted always returns False. For Outlook add-ins, IsTrusted returns True if and only if the add-in is considered trusted by Outlook. More About: Check
Explorers Collection in Outlook
2008-06-01 09:53:00 The Explorers collection contains views of all open folders. Each open folder view is an Explorer object and has a CurrentFolder object, which is the folder whose data is currently being displayed in the Explorer. The currently active Explorer object is also available as the ActiveExplorer object. Sub OL_Explorer_Collection() Dim oExpo As Explorer For Each oExpo In Explorers MsgBox(oExpo.Caption) Next End Sub When you open Outlook using code Explorers will be not be there and the count will be zero. Normally opening outlook will have one Explorer, which is the active explorer An explorer need not be visible to be included in the Explorers collection. Use the Explorers property to return the Explorers object from the Application object.
Google Search Using VBA
2008-05-26 02:35:00 Search Webpages using VBA / Search Text in Google using Excel VBA / Programmaticaly search text in GoogleThis code requires Microsoft Internet Controls reference (Tools > References from code window)The following function will search for given data in google and save the search as an HTML File. The Timeout parameter is set to 1 min.Option ExplicitPrivate ieBrowser As InternetExplorerPrivate Const sSite As String = "http://www.google.com/search?q=" ' Change this AppropriatelyPrivate Const sProofPath As String = "d: emp" ' Path to Save Searched PagesSub Check_Data_From_Google(ByVal sData As String, ByRef sReturn As String, ByRef sSavePath As String) 'Requires Microsoft Internet Controls reference (Tools > References from code window) Dim sSearchString As String ' Combination of Google Search String + Data Dim dtStartTime As Date ' Start Time Dim dtCurrentTime As Date ' Current Time Dim iMaxWaitTime As Integer ... More About: Search
Excel VBA - 1004 -- The file could not be accessed
2008-05-26 02:32:00 Solution for 1004 -- The file could not be accessed while saving the File 1004 Microsoft Office Excel cannot access the file 'C: emp'. There are several possible reasons:• The file name or path does not exist.• The file is being used by another program.• The workbook you are trying to save has the same name as a currently open workbook.1004 -- The file could not be accessed. Try one of the following:• Make sure the specified folder exists.• Make sure the folder that contains the file is not read-only.• Make sure the file name does not contain any of the following characters: ? [ ] : | or *• Make sure the file/path name doesn't contain more than 218 characters.This error occurs because of unwanted characters in the File. The following function would help in removing those characters:Function ClearCharacters(ByVal sDirtyString As String) As StringDim arUnWantedCharacter(1 To 6) As StringDim IsClear As BooleanDim i As IntegerDim strCleanString As StringDim j As...
Using Excel as Database
2008-05-10 14:22:00 VBA ADO Code for using Excel as Database Though many database systems have come , still there is a need to use Excel as Backend database. The reasons might be many -- you get Excel sheets as a Report and do not want to import that into Access or SQL ServerHere is a simple code that will allow you to do exactly thatSub Excel_ADO()Dim cN As ADODB.Connection '* Connection StringDim RS As ADODB.Recordset '* Record SetDim sQuery As String '* Query StringDim i1 As LongDim lMaxRow As Long '* Last Row in the SheetDim iRevCol As Integer '*Dim i3 As IntegerOn Error GoTo ADO_ERRORSet cN = New ADODB.ConnectioncN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:Orginal.xls;Extended Properties=Excel 8.0;Persist Security Info=False"cN.ConnectionTimeout = 40cN.OpenSet RS = New ADODB.RecordsetlMaxRow = ActiveSheet.Cells.SpecialCells(xlCellType LastCell).RowiRevCol = 2For i1 = 2 To lMaxRow Application.StatusBar = i1 sQuery = "Selec...
Create Additional Pivot Table using Excel VBA (from Existing PivotCache)
2008-05-06 11:26:00 Macro to Create a Pivot Table from Existing Pivot Cache Many times you will have a Pivot Table created from a pivot cache and you have a requirment to create another pivot table from the same data. In such cases, instead of creating a new cache, you can use the existing pivot cache to create another pivot table. This will save a good amount of memory too. Sub Create_Pivot_Table_From_Existing_Cache() Dim oPC As PivotCache Dim oPT As PivotTable Dim oWS As Worksheet oWS = ActiveSheet If oWS.PivotTables.Count Then Exit Sub oPC = oWS.PivotTables(1).PivotCache oPT = oPC.CreatePivotTable(oWS.[J1], "Pivot From Existing Cache", True) oPT.AddFields(oPT.PivotFields("Item").Nam e) oPT.AddDataField(oPT.PivotFields("Custome r"), "Quantity", xlCount) End Sub Here we are checking if any Pivot Table exist in that particular sheet; if it exists we ... More About: Excel
Create Pivot Table using Excel VBA
2008-05-06 11:13:00 Macro to Create a Pivot Table from New Pivot Cache Sub Create_Pivot_Table_From_Cache() Dim oPC As PivotCache Dim oPT As PivotTable Dim oWS As Worksheet oWS = ActiveSheet oPC = ActiveWorkbook.PivotCaches.Create(xlDatab ase, oWS.UsedRange) oPT = oPC.CreatePivotTable(oWS.[D20], "Pivot From Cache", True) oPT.AddFields(oPT.PivotFields("Item").Nam e, oPT.PivotFields("Customer").Name) oPT.AddDataField(oPT.PivotFields("Qty"), "Quantity", xlSum) End Sub PivotCache represents the collection of memory caches from the PivotTable reports in a workbook. Each memory cache is represented by a PivotCache object. The above example creates a pivotcache from existing data and then using the cache a pivot table is created More About: Excel
Create Pivot Table from VBA using Wizard
2008-05-06 11:10:00 Use PivotTable Wizard Programmatically using Excel VBA You can use PivotTableWizard method of Worksheet object to create a new PivotTable report. This method doesn’t display the PivotTable Wizard. This method isn’t available for OLE DB data sources. Use the Add method to add a PivotTable cache, and then create a PivotTable report based on the cache Sub Create _Pivot_Table_Using_Wizard() Dim oPT As PivotTable Dim oWS As Worksheet On Error GoTo Err_PT If ActiveSheet.Type = xlWorksheet Then oWS = ActiveSheet oPT = oWS.PivotTableWizard(xlDatabase, oWS.UsedRange, oWS.Range("A20"), "PivotFromWizard") oPT.AddFields("Item", , "Qty") oPT.AddDataField(oPT.PivotFields("Qty"), "Quanity", xlSum) oPT.TableRange1.Select() End If Exit Sub Err_PT: MsgBox(Err.Description) Err.Clear() End Sub
Creating a Command Button on Sheet using Excel VBA
2008-05-06 11:09:00 Adding an OLE Object (Command Button ) to a Worksheet using Excel VBA Sub Create_Command_Button_2007() ' ' Creates a Command button and Positions it ' Written by Shasur for http://vbadud.blogspot.com Dim oOLE As OLEObject ' Add a Command Button oOLE = ActiveSheet .OLEObjects.Add(ClassType:="Fo rms.CommandButton.1", Left:=220, Top:=40, Height:=30, Width:=120) oOLE.Interior.Color = vbRed ' Move and Size with cells oOLE.Placement = XlPlacement.xlMoveAndSize oOLE.Object.Caption = "Click Me..." End Sub Each OLEObject object represents an ActiveX control or a linked or embedded OLE object. An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape... More About: Creating
Document Variables in Outlook using VBA
2008-04-27 17:40:00 Add Hidden Data using StorageItem in Outlook VBA There are multiple ways to have a template in Outlook for achieving tasks. Sometimes, you will require to hold some document variable in outlook like you do with Microsoft Word. The following example shows a simple way to add some text in drafts folder. This will not be visible to user. Sub Create_Hidden_Data() Dim oNs As Outlook.NameSpace Dim oFld As Outlook.Folder Dim oSItem As Outlook.StorageItem On Error GoTo OL_Error oNs = Application.GetNamespace("MAPI") oFld = oNs.GetDefaultFolder(olFolderDrafts) oSItem = oFld.GetStorage("My Appt Template", olIdentifyBySubject) oSItem.UserProperties.Add("My Footer", olText) oSItem.UserProperties("My Footer").Value = "VBADud - Samples & Tips on VBA" oSItem.UserProperties.Add("My Body", olText) oSItem.UserProperties("My Body").Value = "Hi" & vbCrLf & "Requesting a appoin... More About: Document , Variables
How to use .Net Array.Sort Function in VBA
2008-04-27 15:43:00 Using Vb.Net Function in VBA How to use a VB.Net DLL/TLB in Excel VBAHere is an example for using the customized .NET Function in Excel VBA. Unfortunately EXcel VBA doesn't have a Array .Sort function. To overcome the shortcomings, we create our own function here in .Net and use the same in Excel VBAHere are the steps:Create a class library project in Visual StudioAdd a COM Class item (DND_SortArray in this example)In the assembly information edit the title, company and provide a meaningful description. This would be seen in the References dialog in ExcelAdd the code shown below: _ Public Class DotNetDud_SortArray #Region "COM GUIDs" ' These GUIDs provide the COM identity for this class ' and its COM interfaces. If you change them, existing ' clients will no longer be able to access the class. Public Const ClassId As String = "93534c94-9fc1-4a54-b022-338fa7d454c1" Public Const InterfaceId As String = "03787ed3-bc65-41a1-9053-d37f390ff94b" Pu...
Creating Digital Signature for VBA Project (Outlook VBA / Excel VBA/ Word V
2008-04-17 15:08:00 How to digitally sign a VBA Project To create a code-signing certificate, follow these steps:From Programs menu, choose Microsoft Office -> Microsoft Office Tools -> Digital Certificate for VBA Projects(or)Navigate to the folder where your Office applications are installed, usually C:ProgramFilesMicrosoft OfficeOffice12 for Office 2007 and run the Selfcert.exe program.SelfCert.exe is provided with Office 2000 and later. If it is not installed on your system, run Office Setup and install Office Tools Digital Signature for VBA Projects.Type a name for your certificate - just use your own name or product name and click OK to create your personal code-signing certificate.Now open the VBA project using Alt+F11 and select Tools ➪ Digital Signatureto open the dialog shown below. In this dialog, click the Choose button and select thecertificate and click OK twice to choose your certificate andsign your code project with that certificate.Save your VBA project; then exit and restart A... More About: Outlook , Word , Creating , Excel
Show the Developer tab on Ribbon (Excel) or run in developer mode
2008-04-17 15:04:00 Show the Developer tab on Ribbon (Excel ) or run in developer mode You should display the Developer tab or run in developer mode when you want to write macros, run macros that you previously recorded, or create applications to use with Microsoft Office programs. Do the following in these Microsoft Office programs: Word, Excel, or PowerPoint Click the Microsoft Office Button , and then click Excel Options Click Popular, and then select the Show Developer tab in the Ribbon check box. More About: Show , Mode
Identify Browser Version using Excel VBA
2008-04-17 15:01:00 Check Version of Browser using Excel VBAThe following code can be used to check the version of Internet Explorer or Netscape Navigator (whichever is the default browser):Sub CheckWebOptions() Dim wkbOne As Workbook Set wkbOne = Application.Workbooks(1) ' Determine if IE5 is the target browser. If wkbOne.WebOptions.TargetBrowser = msoTargetBrowserIE6 Then MsgBox "The target browser is IE6 or later." ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserIE5 Then MsgBox "The target browser is IE5 or later." ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserIE4 Then MsgBox "The target browser is IE4 or later." ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserV4 Then MsgBox "Microsoft Internet Explorer 4.0, Netscape Navigator 4.0, or later." ElseIf wkbOne.WebOptions.TargetBrowser = msoTargetBrowserV3 Then MsgBox "Microsoft Internet Explorer 3.0, Netscape Navigator 3.0, or later." Else MsgBox "The ...
Check If Workbook is Saved using Excel VBA
2008-04-17 14:57:00 Check Saved Status of Workbook using Excel VBAUse Saved property of Workbook to check the status. Saved returns True if no changes have been made to the specified workbook since it was last savedFunction IsDirty(ByRef OWB As Workbook) As BooleanIf OWB.Saved = False Then IsDirty = TrueEnd IfEnd FunctionAt times, the workbook would have been created and never saved. In that case, you can use the Path property to identify if it was saved at allFunction IsNeverSaved(ByRef OWB As Workbook) As Boolean If OWB.Path = "" Then IsNeverSaved = True End If End Function More About: Check
Set No Of Sheets in a Workbook using Excel VBA
2008-04-17 14:49:00 Change default number of Sheets using Excel VBAThere are many times when you need more than three sheets in a workbook. But when you use Workbooks.Add, it creates a New Workbook with three sheets (default for Microsoft Excel). Later you will add (or delete) the sheets for your use.Here is another way to solve the problem. Use the Application's SheetsInNewWorkbook property to set the default no. of worksheetsSub Set_No_Of_Sheets()' ----------------------------------------- ' coded for vbadud.blogspot.com by shasur' ----------------------------------------- MsgBox "No of sheets in a blank workbook is : " & Application.SheetsInNewWorkbook' set the Workbook for One SheetApplication.SheetsInNewWorkbook = 1' Workbook with only one sheet will be addedWorkbooks.Add' Reset the Workbook for Five SheetsApplication.SheetsInNewWorkbook = 3End SubHere is a way suggested by Jon Peltier, Microsoft Excel MVP (http://PeltierTech.com)Workbooks.Add([Te mplate])Template is optional, but if you u...
Check Out-Of-Office Status in Outlook using VBA
2008-04-17 14:44:00 Extract Out of Office Status using Outlook VBACheck ing out of office status using VBA can be done using PropertyAccessor. The following code uses PropertyAccessor to extract the informationUnlike NamedProperties, PropertyAccessor uses Schema to get the propertySub Check_Out_Of_Office()Dim oNS As Outlook.NameSpaceDim oStores As Outlook.StoresDim oStr As Outlook.StoreDim oPrp As Outlook.PropertyAccessorSet oNS = Application.GetNamespace("MAPI")Set oStores = oNS.StoresFor Each oStr In oStores If oStr.ExchangeStoreType = olPrimaryExchangeMailbox Then Set oPrp = oStr.PropertyAccessor MsgBox oPrp.GetProperty("http://schemas.microsof t.com/mapi/proptag/0x661D000B") End IfNextEnd SubThe PropertyPage object is an abstract object. That is, the PropertyPage object in the Microsoft Outlook Object Library contains no implementation code. Instead, it is provided as a template to help you implement the object in Microsoft Visual Basic. This provides a predefined set o...
Extract Subject and Body of eMail through Outlook VBA
2008-04-17 14:38:00 Extract eMail Data (Subject & Body ) Programatically using Outlook VBAMany automation revolves around mails; you may want to trigger some process once a mail arrives in the InBox. The following code will help you extract the subject and body content of all mails in InBoxSub Extract_Body_Subject_From_Mails()Dim oNS As Outlook.NameSpaceDim oFld As Outlook.FolderDim oMails As Outlook.ItemsDim oMailItem As Outlook.MailItemDim oProp As Outlook.PropertyPageDim sSubject As StringDim sBodyOn Error GoTo Err_OLSet oNS = Application.GetNamespace("MAPI")Set oFld = oNS.GetDefaultFolder(olFolderInbox)Set oMails = oFld.ItemsFor Each oMailItem In oMails sBody = oMailItem.Body sSubject = oMailItem.Subject 'This property corresponds to the MAPI property PR_SUBJECT. The Subject property is the default property for Outlook items.NextExit SubErr_OL:If Err 0 Then MsgBox Err.Number & " - " & Err.Description Err.Clear Resume NextEnd IfEnd SubThe Subject property is the default p... More About: Email , Extract
Check Outlook Drafts folder for messages using Outlook VBA
2008-04-17 14:35:00 Programatticaly check for draft messages using Outlook VBASub Check _Drafts _Folder ()Dim oNS As Outlook.NameSpaceDim oFld As Outlook.FolderDim oItems As Outlook.ItemsOn Error GoTo OL_ErrorSet oNS = Application.GetNamespace("MAPI")Set oFld = oNS.GetDefaultFolder(olFolderDrafts)Set oItems = oFld.ItemsIf oItems.Count 0 Then MsgBox "There are some messages in the draft"End IfExit SubOL_Error: MsgBox Err.Description Err.ClearEnd SubThe program uses the MAPI Namespace and Draft DefaultFolder.The only supported name space type is "MAPI". The GetNameSpace method is functionally equivalent to the Session property, which was introduced in Microsoft Outlook 98.A Folder object that represents the default folder of the requested type for the current profile. If the default folder of the requested type does not exist, for example, because olFolderManagedEmail is specified as the FolderType but the Managed Folders group has not been deployed, then GetDefaultFolder will return Null (Nothing... More About: Messages
Disable Drag & Drop of Cells in Excel VBA
2008-04-12 04:43:00 Enable or Disable dragging and dropping cells in Excel Sub Disable_Cell_Drag _Drop ()Application.CellD ragAndDrop = FalseEnd SubApplication.CellDragAndDrop = True enables drag & drop
Get Shared Name of a Drive using FileSystemObject
2008-04-12 04:33:00 Convert Drive Name to Sharename using VBAHere is a simple function, which uses FileSystemObject's ShareName function to get the shared name of the drivePublic Function ConvertDrive2ServerName(ByVal sFullPath As String) As String' --- Replaces the DriveName with ShareName in a given stringDim FSO As FileSystemObjectDim sDrive As StringDim drvName As DriveDim sShare As StringOn Error GoTo Err_TrapSet FSO = New FileSystemObjectsDrive = FSO.GetDriveName(sFullPath)Set drvName = FSO.GetDrive(sDrive)sShare = drvName.ShareNameIf LenB(sShare) 0 Then ConvertDrive2ServerName = Replace(sFullPath, sDrive, sShare, 1, 1, vbTextCompare)Else ConvertDrive2ServerName = sFullPathEnd IfIf Not FSO Is Nothing Then Set FSO = Nothing' ---------------------------------------' Error Handling' ---------------------------------------Er r_Trap:If Err 0 Then Err.Clear Resume NextEnd IfEnd Function
Get Device Name using QueryDosDeviceW
2008-04-12 04:30:00 Private Declare Function QueryDosDevice W Lib "kernel32.dll" ( _ ByVal lpDeviceName As Long, _ ByVal lpTargetPath As Long, _ ByVal ucchMax As Long _ ) As Long Const MAX_PATH = 260Public Function GetNtDeviceName( _ ByVal sDrive As String) As StringDim bDrive() As ByteDim bResult() As ByteDim lR As LongDim sDeviceName As String If Right(sDrive, 1) = "" Then If Len(sDrive) > 1 Then sDrive = Left(sDrive, Len(sDrive) - 1) End If End If bDrive = sDrive ReDim Preserve bDrive(0 To UBound(bDrive) + 2) As Byte ReDim bResult(0 To MAX_PATH * 2 + 1) As Byte lR = QueryDosDeviceW(VarPtr(bDrive(0)), VarPtr(bResult(0)), MAX_PATH) If (lR > 2) Then sDeviceName = bResult sDeviceName = Left(sDeviceName, lR - 2) GetNtDeviceName = sDeviceName End If End FunctionSub Trial()MsgBox GetNtDeviceName("p:")End Sub
Check Protection of VBA project using Excel VBA
2008-04-12 04:27:00 Check state of protection of VBProject Returns a value indicating the state of protection of a project.Sub Is_Project_Protected() If Application.VBE.ActiveVBProject.Protectio n = vbext_pp_locked Then MsgBox "Protected" End IfEnd Sub More About: Check , Excel
Check Default Reference using Excel VBA
2008-04-12 04:25:00 BuiltIn property returns a Boolean value indicating whether or not the reference is a default reference that can't be removed.Sub If_Reference _Is_Default()Dim i1 For i1 = 1 To Application.VBE.ActiveVBProject.Reference s.Count 'Returns a Boolean value indicating whether or not the reference is a default reference that can't be removed. If Application.VBE.ActiveVBProject.Reference s(i1).BuiltIn = True Then MsgBox "Default Reference : " & Application.VBE.ActiveVBProject.Reference s(i1).name Else MsgBox "Not Default Reference : " & Application.VBE.ActiveVBProject.Reference s(i1).name End If Next i1End Sub More About: Check , Excel
Get References of VBA Project Programmtically
2008-04-12 04:21:00 Extract References of a VBA Project The References property returns the set of references in a project. It is an accessor property (that is, a property that returns an object of the same type as the property name).Sub Get_References_in_Project()Dim i1For i1 = 1 To Application.VBE.ActiveVBProject.Reference s.Count ' Get the Name of the Reference RefName = Application.VBE.ActiveVBProject.Reference s(i1).name ' Get the Description of Reference RefDesc = Application.VBE.ActiveVBProject.Reference s(i1).Description 'Returns a Boolean value indicating whether or not the Reference object points to a valid reference in the registry. Read-only. If Application.VBE.ActiveVBProject.Reference s(i1).IsBroken = True Then RefBroken = True End IfNext i1End Sub
Save and Reopen all Workbooks (Excel VBA)
2008-03-03 03:58:00 Save All Open Workbooks and Re-open them using VBAUse SaveWorkspace method to save the current workspace and use the Open method to open the files againSub WorkPlace_Save_And_Resume()' Save WorkspaceApplication.DisplayAlerts = FalseApplication.SaveWorkspace "C:New FolderTempWorkSpace.XLW"Workbooks.Close' Do my work here without anyother workbooksWorkbooks.Open "C:New FolderTempWorkSpace.XLW"Application.Displ ayAlerts = TrueEnd SubThe files in the application will be saved and re-opened using Open method More About: Excel , Save
Run a Macro from Different Workbook
2008-03-03 03:55:00 Execute a macro in a different workbook Runs a macro or calls a function. This can be used to run a macro written in Visual Basic or the Microsoft Excel macro language, or to run a function in a DLL or XLL.Sub Run_Macro _In_Different_WorkBook()Applicat ion.Run "'C:CanBeDeleted.xlsx.xlsm'!AnotherWrkBoo k_Macro"End SubRuns a macro or calls a function. This can be used to run a macro written in Visual Basic or the Microsoft Excel macro language, or to run a function in a DLL or XLL.Sub Run_Macro_In_Different_WorkBook_With_Argu ments()On Error GoTo Err_TrapApplication.Run "'C:CanBeDeleted.xlsx.xlsm'!Function_Two_ Args", "Argument 1", "Argument 2"Err_Trap:If Err 0 Then Debug.Print Err.Number & Err.Description End IfEnd SubThe above code passes arguments to a macro in another workbookThe following errors need to be handled for forward compatibility of your macro450 - Wrong number of arguments or invalid property assignment449 - Argument not optional
Find Number of Days in a month using VBA (Excel 2007)
2008-03-03 03:52:00 Calculate Number of Days in a Month EOMONTH returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.Function No_of_Days_in_Month()Dim WrkMonths As IntegerDim StartDate As DateDim EndDate As DateStartDate = NowEndDate = WorksheetFunction.EoMonth(StartDate, 0)' if you want the days countMsgBox "No of days in current month := " & Day(EndDate)End FunctionThe above code will work only in Excel 2007 More About: Find
Find Last Day of the Month using VBA (Excel 2007)
2008-03-03 03:50:00 Calculate Last Day of the Month EOMONTH returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.Function Last_Day_Of_the_Month()Dim WrkMonths As IntegerDim StartDate As DateDim EndDate As DateStartDate = NowEndDate = WorksheetFunction.EoMonth(StartDate, 0)MsgBox "Last day of the current month is := " & EndDateEnd FunctionThe above code will work only in Excel 2007 More About: Find
Calculate End Date of the Project using Excel VBA
More articles from this author:2008-03-03 03:47:00 Calculate End Date using Excel VBAEDATE returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.Function Get_The_EndDate()Dim WrkMonths As IntegerDim StartDate As DateDim EndDate As DateStartDate = NowEndDate = WorksheetFunction.EDate(StartDate, 3)MsgBox "End Date of the Project is := " & EndDateEnd FunctionThe above will work in Excel 2007 only More About: Calculate 1, 2, 3 |
|
 |
|
 |
