beginner to VBA: adding formula on Visual Basic

[Fmmleohb]

Dear all,

I am a beginner to VB and i trust this forum can help me to sharpen my skills.

how do I add the simple calculations on VBA instead of the spread sheet. ie i have an excel sheet with data that has formulas, i want these formulas to disappear in excel however it should only appear in MACROS. formulas such as (multiply, % change , add , subtract, avg, divide)

can you kindly explain with an example ?
much appreciated
regards,
leo

 

[Z51]

Let's say you have numbers in A1 and B1. In C1 you could use something like "=AVERAGE(A1:B1)" to get an average of the two cells.

In VBA, you could create a macro to do this, or write your own UDF (User-Defined Function). For code that calculates and returns results, it's often sufficient to use a UDF. For code to DO things, like change layouts/rows/columns, etc., a macro is used. (Macros can also call Functions to handle calculations and return values to the macro.)

To create a UDF, in the VBA Editor (ALT+F11), click Insert > Module. In that module add the following code per our earlier example of averaging a range of cells:

Public Function myAvg(r As Range)
myAvg = WorksheetFunction.Average(r)
End Function

Then in C1, you could use the formula:

=myAvg(A1:B1)

This is obviously a simple example - it can get much more complex, of course.

 

[OaklandJim]

I am trying to understand your need. Do you want just values in the worksheet without formulas? So a macro would go through the formulas and replace them with the calculated value?