Learn Visual Basic For Application - VBA

How to use Index + Match with Indirect Function In Excel

If you want to create a VBA function to read a date from a cell in Excel, you can use the following example:

Function ReadDateFromCell(cell As Range) As Date
On Error Resume Next
ReadDateFromCell = CDate(cell.Value)
On Error GoTo 0
End Function

This function takes a cell as an argument and attempts to convert its value to a Date type using CDate. If the conversion is successful, it returns the date; otherwise, it returns the default date value (January 1, 1900).

You can use this function in your Excel workbook by entering a formula like this in a cell:

=ReadDateFromCell(A1)

In VBA (Visual Basic for Applications), objects are a fundamental concept. Objects are instances of classes, and they can represent various elements in an application, such as worksheets, ranges, cells, charts, etc. Here are some common types of objects in VBA along with examples:

Workbook Object:
Represents an Excel workbook.

Dim wb As Workbook
Set wb = Workbooks.Add
Worksheet Object:
Represents a worksheet within a workbook.

Dim ws As Worksheet
Set ws = wb.Sheets(1)
Range Object:
Represents a cell or a range of cells in a worksheet.

Dim rng As Range
Set rng = ws.Range("A1:B10")
Chart Object:
Represents a chart in a worksheet.

Dim chart As ChartObject
Set chart = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
Application Object:
Represents the Excel application itself.

Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
FileSystemObject:
Represents the file system, allowing you to perform operations on files and folders.

Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dictionary Object:
Represents a collection of key/value pairs.

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Outlook Application Object:
Represents the Outlook application.

Dim outlookApp As Object
Set outlookApp = CreateObject("Outlook.Application")
Word Application Object:
Represents the Word application.

Dim wordApp As Object
Set wordApp = CreateObject("Word.Application")
ADO Connection Object:
Represents a connection to a database using ADO (ActiveX Data Objects).

Dim conn As Object
Set conn = CreateObject("ADODB.Connection")
These examples demonstrate the creation and use of various objects in VBA. Keep in mind that the specific objects available may vary depending on the application (e.g., Excel, Word, Outlook) and any additional libraries or references you have added to your VBA project.

Please Like Comment and subscribe for more updates regarding Excel, Power BI and SQL.

LearnExl - YouTube Hello, I am Ashish Maurya.This is a dedicated channel for Excel and Power BI In this channel we are going to cover many things like excel formulas and functi...

if you want to merge multiple excel files from a folder and specific sheet's data so you can use below code for merge files into one workbook.

Sub consolidation()
Dim s*t As Worksheet
Dim s*tName As String
Dim P As Long

Dim Path As String
Dim Filename As String
Dim Sheet As Worksheet
Dim Searchs*t As String
Dim s*tsearch As Boolean
Dim activewkb As Workbook

Path = "D:\Daily work\WMS Data-Minutly - Copy (2)\Portfolio\"
Filename = Dir(Path & "*.xlsx") 'change excel format
Do While Filename ""

Workbooks.Open Filename:=Path & Filename, ReadOnly:=True

P = Sheets.Count

s*tName = InputBox(prompt:=Enter_sheet_Name, Title:="Search Sheet", Default:="1-MIN REPORT")

For P = 1 To P

If Sheets(P).Name = s*tName Then

'Exit Sub
End If
Next P

On Error Resume Next
ActiveWorkbook.Sheets(s*tName).Select
s*tsearch = (Err = 0)
On Error GoTo 0
ActiveSheet.Copy after:=Workbooks("codes.xlsm").Sheets(Workbooks("codes.xlsm").Sheets.Count)
Workbooks("codes.xlsm").Activate

ActiveSheet.Select
Range("j2:j30000").Value = Filename
Application.CutCopyMode = False

Workbooks(Filename).Close

Filename = Dir()
Loop
End Sub

Sub merge()
Dim i As Integer
Workbooks("codes.xlsm").Activate

For i = 1 To Worksheets.Count
Worksheets(i).Select
Range("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks("Book2").Activate
Sheets("sheet1").Select
Range("a1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues
'Application.CutCopyMode = False
Workbooks("codes.xlsm").Activate
Next i

End Sub

Hello guys,

You can ask any question regarding excel and vba on whatsapp.