How To Call .Xla Functions In To .Xlsm Excel File

Here I describe about call a  function from .xla  file in to .xlsm file.I explain it step by step
with pictures, let's see-
1)Open excel sheet and save with 'FileName.xla' extension (here i give 'Functions.xla' )
    and insert  a module then write  a simple 'Add' function.

2)Open another excel sheet and save it with 'FileName.xlsm' extension(here as a 'TesttFunction.xlsm')
   insert a button with assign a micro.

3)Assign the micro to button and write this code and see highlighted box,here we call the 'Add'

    from 'Functions.xla' file.

4)Save the both files in same folder/directory.

5)And click the button ,result will appear.

Excel Range Name With Spaces And Ampersand

I discuss about excel range name with spaces and ampersand, when  we create a list and
save the rang name it replace the space or other special  character with underscore("_") ,
it can not save with space and ampersand. Just see Microsoft link.
1)If range name  is  "My range" it save as  "My_range".
2)If range name  is  "My&range" it save as  "My_range".

Create A Drop Down List Of Micros In Excel(VBA)

We can  create a drop down list of micros in excel.But here is some rules  we should  know-
1)First you create a list name of micros and make a  drop down list of it.
2)Then you create a micros.
                              I am upload a video for simply create a drop down list of micros-

Create A Simple Dependent Drop Down List In Excel

Here we learn how to create a simple drop down list in excel.But firstly we know
that how to create a drop down list. I am  upload the videos for both process
please see these video and try to do same.
    Create A Simple Drop Down List In Excel   

      Create A Dependent Drop Down List In Excel
              

Looping Through Files In A Directory(VBA)

When we want go through the files, located in directory, then we need loop in side the directory.
Below i write a  sample code for the same(Blue color text is main loop go to through the file in folder)-

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim objFolder
    Set objFolder = fso.GetFolder("C:\ForSample")       
    Dim i As Integer: i = 1
    Dim FileName As Variant
    Dim LineText As String
    Dim str As String
    Dim objFile
    For Each objFile In objFolder.files
        str = ""
         FileName = objFile
         Open FileName For Input As #50
         While Not EOF(50)
            Line Input #50, LineText
            str = str & LineText
         Wend       
        Close #50
    Next

Difference Between Xla And Xalm AddIns

There is compatibility in old version and new version of VS. We not use micros features  with this extension .Both are Add-In Files. The main difference is that XLAM cannot be opened  in earlier office versions (Pre 2007). And hence when you are creating Add-Ins which has  to be compatible with earlier versions then always create an XLA instead of XLAM. –  Whenever we save  the excel sheet, we choose the required extension according enabling micros or  other features.But in case of .xla and .xlam file is not work in MsOffice 2010, let's do for test:- 1) I take a 2010 MS Office and Open excel. 2) Create a micro in excel sheet and save it with .xla extension and other sheet with .xlam. 3) When you open the files the whole worksheet is disable. 4) Because of these extension is uses for earlier version like 2003. They not work in ms office 2010.

Create A Graph For Data In Excel Worksheet.

Here We Learn To Create A Simple Graph In Excel Worksheet:-

This Can Be Achieve In Some Steps:-
1)Open the Ms_excel.
2)Prepare the data which is required ,in two columns for with respect to X-axis and Y-axis.
3)After the data entry ,go to 'Insert Tab' and select 'Line' as shown in picture.                                                          
                
4)The new dialog box is appear ,Just delete it's lines and texts for entering your new lines (for
   data ) and text.

5)After that go to 'Design' Tab (It appear after select 'Line'), select 'Select Data'.

6)Open a new Dialog box.Where we select the data for X-axis(Horizontal) and Y-axis(Vertical).

                       

7)Select the data from columns. Here Above red line is for Vertical data and below is for 

   horizontal data, blue circle is input data.For Horizontal click 'Edit' open a new dialog box,Enter the 

   range in text box and click on 'Ok'.

8)Your graph is prepared-

Copy Text File In WorkSheet And Some Important Query On Vba Programming.

Here i write a some queries and code which  is generaly use in vba program:-

Copy Text File In Worksheet :-
We create  a  module for this process and give the address of file where  it is locate.
Just see the below code and try it.

Sub GetDataFromTextFiles()
 Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim objFolder
    Set objFolder = fso.GetFolder("C:\ForSample")       
    Dim i As Integer: i = 1
    Dim FileName As Variant
    Dim LineText As String
    Dim str As String
    Dim objFile
    For Each objFile In objFolder.files
        str = ""
         FileName = objFile
         Open FileName For Input As #50
         While Not EOF(50)
            Line Input #50, LineText
            str = str & LineText
         Wend
        ActiveSheet.Cells(i, 1).Value = str
        i = i + 1
        Close #50
    Next
End Sub



Copy the data   from to sheet to anther in workbook :-
1) Sheets(SheetName1).Range(A1,D2).Value = Sheets(SheetName2).Range(A1,D2).Value
2) Sheets(SheetName1).Cells(row,col).Value = Sheets(SheetName2).Cells(row,col).Value

How to call a Excel functions in Vba:-
Here 'd'  ,'c' and 'e' as integer and we write this code inside the Vba module-

 d = Application.WorksheetFunction.Power(d1, 0.5)
 e = Application.WorksheetFunction.Power((c * d), 0.25)
Note- The power function is not accept the negative(-ve) values.

Copy Data From One Workbook To Another Workbook

Copy Data From One Workbook To Another Workbook

For This ,both the workbooks is in the same directory or  it can be  in other directory ,
Let us-  Source excel sheet name is "Example.xlsx" and sheet name is  "OscarTorres" .
then firstly we have to find out the location(path) of this workbook. and similarly doing this with another one, where we want  to copy the data. Below I write the simple example  for this, here  is the excel sheet is in the same directory.

Requirement:-

1. We need two worksheet(it may be perform with multiple worksheet).

2. Define the rang.

3. Find out the path of workbook location.

We Can Write this code with module and assign to Form Control Button,and  another way is write in ActiveX Controls Command Button.

Go through this-  Excel Sheet --->Developer Tab--->Insert---> Form Controls---> Button

Here I create one 'Micro' ,and it can be assign to button or run indusualy.

Sub CopyData()

Application.DisplayFullScreen = False

Dim SourceWorkBookName As String

Dim SourceSheetName As String

Dim SourceWorkBook As Excel.Workbook

Dim SourceSheet As Excel.Worksheet

Dim workingDirectory  As String

SourceWorkBookName = "Example.xlsx"

SourceSheetName = "OscarTorres"

workingDirectory = Application.ActiveWorkbook.Path

Set SourceWorkBook = Workbooks.Open(workingDirectory & "\" & SourceWorkBookName)

'ActiveWindow.Visible = False

Set SourceWorkBook = Application.Workbooks(SourceWorkBookName)

Set SourceSheet = SourceWorkBook.Worksheets(SourceSheetName)

 

ThisWorkbook.Sheets(1).Range("A1", "A43").Value = SourceSheet.Range("AE531", "AE573").Value

SourceWorkBook.Close

Application.DisplayFullScreen = True

End Sub

Describe About Ranges With Last Row In Excel Sheet

Describe About Different  Type  Of  Ranges With Last Row In Excel Sheet:-

Last Row For Any  Column Or Row:-

Let us we define LastRow is as a integer and need  to caculate the last row of  perticulre column for range in sheet. Here is  some syntax for last row with discription.

  

1. LastRow = ActiveSheet.UsedRange.Rows.Count

It give the value of used  number of current sheet's row . 

2. LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

It also give the value of used  number of current sheet's row with the search direction(From where is the used last row ) like a  xlPrevious, xlAfter, xlNext.

3. LastRow = Range("A65536").End(xlUp).Row

It give directly the value of  rows in specified column(Here is "A65536") .

4. LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

It  simply calculate the last cell in column or  row (Here we write row ).

5. LastRow = Sheet1.Coluumn(2).Find("*",,,,xlByRows,xlPrevious).Row

From this code we can find out last row with respect  to  column index and  same as explain in 2 method  written in above.

Range For Excel Sheet With Last Row:-




1. This syntax is for simply known range for user,here is "xlwbook" is name of 

sheet and "rng" is excel range and  A2 to B2 is range.

rng=xlwbook.Range("A2","B2")

2.This syntax is for find out last row in perticulre column(here is "A") ,it 

select whole rows of column A. 

Workbooks().Sheets().Cells(Rows.Count, "A").End(xlUp).Row

3.If we want to find or calculate last row according to our need. We can Write the range as:-

  rng=xlwbook.Range("A2","B2"& LastRow)   

Here is " LastRow" is gives just  number of  rows in Column.