none
Convert formula to VBA code RRS feed

  • Question

  • I am new to this so please bear with me.

    I have an excel sheet that adds, subtracts and divides. It uses a formula for this purpose. I also want to add a button to clear the numbers that have been imputed previously instead of deleting each cell

    I used the VBA program to convert the formula for creating a macro but the VBA program won't except the conversion because it's in a stream. I don't have any idea how to write VBA code for a macro.

    I did find the VBA code for the clear all button but it seems when I use it the function for the formula stops working.

    Can I run a formula and a VBA macro in the same excel sheet?

    Saturday, February 27, 2016 4:02 AM

Answers

  • >>>I have an excel sheet that adds, subtracts and divides. It uses a formula for this purpose. I also want to add a button to clear the numbers that have been imputed previously instead of deleting each cell

    Do you mean that remove all formulas from a sheet but keep the results of calculations in excel VBA? if so, you could refer to below code:

    Sub DemoClearFomular()
    
        Dim ws As Worksheet
        Set ws = ActiveSheet
        ws.UsedRange.Value = ws.UsedRange.Value
    
    End Sub
    

    >>>Can I run a formula and a VBA macro in the same excel sheet?

    The WorksheetFunction object makes most of the built-in Microsoft Excel 2010 worksheet functions accessible to you as methods in Visual Basic for Applications (VBA), so you don't have to keep reinventing the wheel. Although you can access the WorksheetFunction object through the Application object by using the WorksheetFunction property, doing so is not necessary for VBA code written in an Excel workbook. You can use the WorksheetFunction object as a top-level object, and access its methods directly as shown in this simple code example.

    Dim mySum As Integer
    mySum = WorksheetFunction.Sum(100,200)

    For more information, click here to refer about Using Worksheet Functions from VBA Code in Excel 2010

    In addition could you provide more information about your issue, for example sample code, screenshot etc., that will help us reproduce and resolve it.

    Thanks for your understanding.

    • Marked as answer by David_JunFeng Saturday, March 5, 2016 3:19 PM
    Wednesday, March 2, 2016 1:32 AM

All replies

  • Hi,

    Sorry, I can hardly understand exactly what you want to do.
    So, could you share your file via cloud storage such as OneDrive, Dropbox, etc.?

    Regards.
    Saturday, February 27, 2016 11:03 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, February 29, 2016 5:53 AM
  • >>>I have an excel sheet that adds, subtracts and divides. It uses a formula for this purpose. I also want to add a button to clear the numbers that have been imputed previously instead of deleting each cell

    Do you mean that remove all formulas from a sheet but keep the results of calculations in excel VBA? if so, you could refer to below code:

    Sub DemoClearFomular()
    
        Dim ws As Worksheet
        Set ws = ActiveSheet
        ws.UsedRange.Value = ws.UsedRange.Value
    
    End Sub
    

    >>>Can I run a formula and a VBA macro in the same excel sheet?

    The WorksheetFunction object makes most of the built-in Microsoft Excel 2010 worksheet functions accessible to you as methods in Visual Basic for Applications (VBA), so you don't have to keep reinventing the wheel. Although you can access the WorksheetFunction object through the Application object by using the WorksheetFunction property, doing so is not necessary for VBA code written in an Excel workbook. You can use the WorksheetFunction object as a top-level object, and access its methods directly as shown in this simple code example.

    Dim mySum As Integer
    mySum = WorksheetFunction.Sum(100,200)

    For more information, click here to refer about Using Worksheet Functions from VBA Code in Excel 2010

    In addition could you provide more information about your issue, for example sample code, screenshot etc., that will help us reproduce and resolve it.

    Thanks for your understanding.

    • Marked as answer by David_JunFeng Saturday, March 5, 2016 3:19 PM
    Wednesday, March 2, 2016 1:32 AM