Use VBA to manipulate the general class library of Excel

Source: Internet
Author: User

The following class excellibrary is a very common class that uses VBA to manipulate excel. It is my summary, arrangement, and reference in my work (I have used some examples from Microsoft) I have made many improvements and usage, and I will share them with you here.
At first, the program had no comments. For ease of understanding, I just added some comments.

'Manipulate the Excel general class library VB. NET

Imports Excel

Public class excellibrary
'Excel application indicates Excel itself
Private mexcelapp as Excel. Application
'Workbook is the workbook in Excel.
Private mworkbook as Excel. Workbook
'Worksheet is the sheet1, sheet2...
Private mworksheet as Excel. Worksheet

Public sub new ()
'Start an Excel application
Mexcelapp = new excel. Application
End sub

Public sub open ()
'New workbook
Mworkbook = mexcelapp. workbooks. Add ()
End sub

Public sub open (byval template as string)
'Create a workbook using the template
Mworkbook = mexcelapp. workbooks. Add (Template)
End sub

Public sub setactivesheet (byval sheetname as string)
'Set the sheet of the current activity
Mworksheet = mworkbook. worksheets (sheetname)
End sub

Public sub addnewsheet (byval sheetname as string)
'Add a new sheet.
Mworksheet = mworkbook. worksheets. Add ()
Mworksheet. Name = sheetname
End sub

Public sub hidesheet (byval sheetname as string)
'Hide a sheet (sometimes this function is also required)
Mworkbook. worksheets (sheetname). Visible = false
End sub

Public sub copyrange (byval sheetname as string, byval cell1 as string, byval cell2 as string)
'Copy in Excel
Setactivesheet (sheetname)
Mworksheet. Range (cell1, cell2). Copy ()
End sub

Public sub pasterange (optional byval sheetname as string = "")
'Regional paste in Excel
If sheetname <> "" then
Setactivesheet (sheetname)
End if
Mworksheet. paste ()
End sub

Public sub changesheetname (byval sheetoldname as string, byval sheetnewname as string)
'Change Sheet Name
Mworkbook. worksheets (sheetoldname). Name = sheetnewname
End sub

Public sub setvalue (byval cellposition as string, byval value as string)
'Set the value of a cell.
Mworksheet. Range (cellposition). formular1c1 = Value
End sub

Public sub setvalue (byval rangex as string, byval rangey as string, byval value as string (,))
'Specifies the value of a region. This function is very useful.
Dim RG as range
Rg = mworksheet. Range (rangex, rangey)
RG. value = Value
End sub

Public sub saveas (byval filename as string)
'Save
Mworkbook. saveas (filename)
End sub

Public sub close ()
'Close
Mworksheet = nothing
Mworkbook = nothing
Mexcelapp. Quit ()
Mexcelapp = nothing
GC. Collect ()
End sub
End Class

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.