VBA References

Referencing in VBA is most important operation due to this operation we perform various tasks. In VBA we perform various types of Referencing operations
Such as :
  • ·         Referencing with cell
  •       Referencing with one cell to another cell
  •       Referencing to Row
  •       Referencing to Column
  •       Referencing to Selection
Referencing with cel 
During this We perform our operation referencing to cell.   

     
Code :


Sub referencingCells()

           ThisWorkbook.Sheets(“Sheet1”).cells(2,1).value=”This is Referencing to Cell”

End Sub


       OutPut:

Referencing with One Cell with Another Cell
We reference our cell from one cell to another cell i.e by using this concept we take value of one cell into another cell.

Note :
This operation can be take place inside same sheet or inside another sheet also.

Inside Same Sheet

Note : Run a Micro use Alt + F8 Key



Code:

Sub referencingToSameSheet()

         ThisWorkbook.Sheets(“Sheet1”).Cells(2,1).value=
                                              ThisWorkbook.sheets(“Sheet1”).cells(3,2).value

End Sub

OutPut :



Referencing One sheet to another Sheet
Same process will be take place in this case but only changing into referencing sheet
e.g
Here sheet 2 is blank



Code:

Sub referencingToAnotherSheet()
     
           ThisWorkbook.Sheets("Sheet2").Cells(3, 2).Value=
                                                    ThisWorkbook.Sheets("Sheet1").Cells(2, 1).Value
    
End Sub

OutPut :


Referencing to Row
We explain Referencing Row with example i.e Hiding Row ,Showing Column
Here is our Original sheet


Hiding Row in Excel using VBA


Code

 Sub hideRow()
                
         ThisWorkbook.Sheets("Sheet2").Rows("2:2").Hidden = True

End Sub

 OutPut :
In this Sheet (2:2) is hidden
Show Row in Excel using VBA

In this case we unhide row(2:2) which is hidden in above sheet


Code :
  
 Sub showRow()
                  
                   ThisWorkbook.Sheets("Sheet2").Rows("2:2").Hidden = False
  
 End Sub

OutPut :
Similarly, In next Section we will discussed Hiding Column and Showing Column

 Hiding Column in Excel using VBA

 It is a complete sheet as showing below and from this sheet we hide column B using VBA code



 Code :

 Sub hideColumn()

       ThisWorkbook.Sheets("Sheet2").Columns("B:B").Hidden = True

 End Sub

OutPut :
Showing Column in Excel using VBA


Press Alt +F8 on Excel Sheet


Code:

 Sub showColumn()

                 ThisWorkbook.Sheets("Sheet2").Columns("B:B").Hidden = False

 End Sub

 OutPut:




















Comments

Post a Comment

Popular posts from this blog

Export data from mysql db to csv file using java

Image
Hi friends ! Today I am discussing about simple but very important scenario. When we create any application then during creation of application we have face many scenarios like 1.Taking information from user and storing these information inside database : Generally such type of Operation is performed by user using form application 2.Taking Data from Database and Displaying on Page : We can perform such type of Operation in various ways like using JSP,JSTL tags or you can perform such operations using JQUERY also. 3.Taking Data from Database and download it in Excel or CSV format : In this we want our data from database and store it inside Excel or text files for further use. And we have chosen third scenario from discussion for performing this Operation we have to need three files exportCsv.html  :- this contains a button for clicking and performing exporting data from database Operation ExportData.java  :- It is a servlet class which defines connectivity fro...
Keep reading

Secure Database Connectivity in node.js with mysql

Image
Hi Everyone! Yesterday my friend Andrea from Italy asked me a question about secure Database connectivity in node.js with mysql i.e. he wants to a secure database connection in node.js for some secure keys like username,password,database name that we use during database connectivity.His problem is obviously right from security point of view as we know that JavaScript is client side scripting knowledge so it is compiled on client side so after inspecting page some one can be able to view these secure keys which I discussed previously. I surf internet and found there are various ways to solve this problem I am not discussing all of them here but also I discussing here two of them and after this discussion I think Andrea and other reader who are facing with same problem can be able to resolve their problem. I choose two ways to solve this problem. Connection code in app.js file Create a Json file Connection code in app.js file : Javascript is client side language and code i...
Keep reading

Introduction to VBA

Image
VBA stands for Visual Basic Application. . It is an event driven Programming language by Microsoft   now a days   it is predominantly used with Microsoft applications such as MS –Excel , MS-Word, MS-Access. VBA Application MS-Excel Provides loads on inbuilt functions.MS-Excel Provides only basic inbuilt functions which may not be sufficient to perform complex calculations but by using VBA we can create our own functions and perform our desirable tasks How to Access VBA Editor  In excel windows Press “ALT +F11”  to open VBA editor .. Step 1:   At first open Excel sheet Step 2:  Follow Above Tricks and open VBA Editor will be open  which looks like this . Some Important VBA Terminologies  Modules : Modules is the area where code is written. In above figure there is no any modules. How to insert Module ??? To insert modules in VBA right click on Microsoft Office Objects and then  a win...
Keep reading