paste special function in vb.net convert VBA to vb.net

04-09-2012, 05:35 AM

arun_ajay2000 arun_ajay2000 is offline

Newcomer

Join Date: Apr 2012

Posts: 1

paste special function in vb.net convert VBA to vb.net

Hi,
Hi, Im new to VB programming. can anyone help me out to convert given VBA code to vb.net. this function is designed to convert all excel workbook data to value using pastespecial function.
Thank you in advance.

Code:

Sub ConvertAllToValues()
     '
     'Originally Adapted from OZgrid.com
     '
    Dim OldSelection As Range
    Dim HiddenSheets() As Boolean
    Dim Goahead As Integer, n As Integer, i As Integer
    Goahead = MsgBox("This will irreversibly convert all formulas in the workbook to values. Continue?", vbOKCancel, "Confirm conversion to values only")
    If Goahead = vbOK Then
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
         
        n = Sheets.Count
        ReDim HiddenSheets(1 To n) As Boolean
         
        For i = 1 To n
            If Sheets(i).Visible = False Then HiddenSheets(i) = True
            Sheets(i).Visible = True
        Next
         
        Set OldSelection = Selection.Cells
        Worksheets.Select
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues
         
        Cells(OldSelection.Row, OldSelection.Column).Select
        Sheets(OldSelection.Worksheet.Name).Select
         
        Application.CutCopyMode = False
         
        For i = 1 To n
            Sheets(i).Visible = Not HiddenSheets(i)
        Next
         
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    End If
End Sub

Last edited by Cerian Knight; 04-09-2012 at 09:58 AM. Reason: Added [code]...[/code] tags

04-09-2012, 09:52 AM

Cerian Knight

Multi-Technologist

Super Moderator
* Expert *

Join Date: May 2004

Location: Michigan

Posts: 3,740

This code is native VBA, which Excel uses directly. Therefore, confirm that you are trying to automate Excel using VB.NET? Also, show us what you have tried, since it is not the intent of this forum to do complete code-conversions for you.

BTW: I split this out to its own thread (and removed a duplicate post in another thread), since it did not see any relevance to the thread topic you had posted in. Please have a look at our Posting Guidelines when you have a chance. Thanks and welcome to the forum.

__________________
"May the code that you write never work in ways that you didn't expect; and may the code that you didn't write never require you to maintain it". - Ancient Chinese Proverb

Last edited by Cerian Knight; 04-09-2012 at 09:57 AM.