Excel VBA Help

[Giri]

Hi Everyone,

I started learning VBA 2 days ago and I'm having trouble with something.

I am trying to create a macro for calculating Gross Margin and have used the code below. Is this correct? Also, when I go and click on the "Macros" tab under the "Developer" tab, I can't see this macro ("GrossMargin1")?

Any help with this would be greatly appreciated!!! BTW, everything except for lines 3,4,5,6 were in a book I am reading so I added these particular lines to get the formula working.

Thank you!!

Giri

[VBA]Public Function GrossMargin1()
Dim totalSales
Dim totalExpenses
Dim sales As Range
Dim expenses As Range

Set sales = Sheet1.Range("D22", "F22")
Set expenses = Sheet1.Range("D24", "F24")

totalSales = Application.Sum(Range("Sales"))
totalExpenses = Application.Sum(Range("Expenses"))
GrossMargin1 = (totalSales - totalExpenses) / totalSales

End Function[/VBA]

[macropod]

Hi Giri,

You'll never see your code under the macros listing because it's a Function, not a Sub.

Also, your 'totalSales' & 'totalExpenses' expressions are mis-coded. For example, instead of '.Sum(Range("Sales"))', you should use '.Sum(sales)'.

Once you've fixed the coding errors, you can either:
. change 'Function' to 'Sub' ('End Function' will become 'End Sub'); or
. call the function from another macro or via a worksheet formula (ie =GrossMargin1()).

[Giri]

Hi Macropod,

Thanks for your help!

I made the changes you suggested. With regards to the two options you gave me,

1) When I changed "function" to "sub", I received the error "Compile Error: Expected Function or variable" and this line was highlighted:

"GrossMargin1 = (totalSales - totalExpenses) / totalSales"

2) When I use it in a spreadsheet formula, what am I expected to type in the brackets when I type "=GrossMargin1(???)".

I'm sorry if these are really silly questions... as I stated above, I am very new to this... lol.

Thanks again for your help =D

Giri

[macropod]

hi Giri,

If you're using the code as a Function, then all you need in the worksheet is:
=GrossMargin1()
including the parentheses. That's because your Function takes no input and has only a single result for its output. If your Function was designed to calculate the values for a nominated range, you would then add the ranges to the formula; you'd also need to code the function a bit differently.

You could also call the Function with a Sub coded along the lines of:
Sub Demo()
MsgBox GrossMargin1
End Sub

Regarding the compile error, I should have mentioned the need to Dim 'GrossMargin1' (good practice) and change its name (eg 'GrossMargin'); otherwise you end up with a variable having the same name as its Sub.

[Giri]

Hi Macropod,

With the code below, I'm receiving the error - "Run time error 13 - Mismatch"

Am I missing something?

[VBA]Public Function GrossMargin1()
Dim totalSales
Dim totalExpenses
Dim sales As Range
Dim expenses As Range
Dim grossMargin

Set sales = Sheet1.Range("D22", "F22")
Set expenses = Sheet1.Range("D24", "F24")

totalSales = Application.Sum("Sales")
totalExpenses = Application.Sum("Expenses")
grossMargin = (totalSales - totalExpenses) / totalSales

End Function

Sub Demo()
MsgBox GrossMargin1
End Sub[/VBA]

Giri

[macropod]

Hi Giri,

Have another read of my comments about the 'totalSales' & 'totalExpenses' expressions.

[Giri]

Hi Macropod,

Didn't get a chance to work on this for awhile. But, I have now made the adjustments I THINK you were talking about.

[VBA]Public Function GrossMargin1()
Dim totalSales
Dim totalExpenses
Dim sales As Range
Dim expenses As Range
Dim grossMargin

Set sales = Sheet1.Range("D22", "F22")
Set expenses = Sheet1.Range("D24", "F24")

totalSales = Application.Sum(sales)
totalExpenses = Application.Sum(expenses)
grossMargin = (totalSales - totalExpenses) / totalSales

End Function[/VBA]

When I enter the function in excel now, I get a value of 0 even though there are numbers in cells D22,E22,F22 and D24,E24,F24. Could you please help me out with this?

Thanks in advance!

Giri

[macropod]

hi Giri,

My apologies - the function name should be the same as its result. I used:
Function GrossMargin1
...
grossMargin = (totalSales - totalExpenses) / totalSales
instead of either:
Function GrossMargin1
...
grossMargin1 = (totalSales - totalExpenses) / totalSales
or:
Function GrossMargin
...
grossMargin = (totalSales - totalExpenses) / totalSales

[Charlize]

I assume the formule is used on the same sheet as the numbers.
[vba]Public Function GrossMargin1(sales As Range, expenses As Range)
'in your sheet you say =GrossMargin1(D22:F22,D24:F24)
Dim totalSales As Long, totalexpenses As Long
'think it's for updating result when values of ranges change
Application.Volatile
totalSales = Application.WorksheetFunction.Sum(sales)
totalexpenses = Application.WorksheetFunction.Sum(expenses)
GrossMargin1 = Format((totalSales - totalexpenses) / totalSales, "0.00")
End Function
[/vba]Charlize