How use cell value in a calculation? 1

[Rixn]

Hi All!

1) In the Visual Basic Editor I want to know what is the correct way to refer to a cell so I can use its value in a calculation?

Here's my code:

Function CalcValue(Arg1 As String, Arg2 As String) As Long
If Arg1 = "1" And Arg2 = "2" Then
CalcValue = Worksheets("SheetName").Range("B14").Value * 2.5
ElseIf Arg1 = "2" And Arg2 = "2" Then
CalcValue = 76
End If
End Function

I've also tried:
CalcValue = Worksheets("SheetName").Cells(14, 2) * 2.5
but it didn't work.

The cell in Excel has this formula:
=calcValue(C14;F14)


2) How do I comment code in the VB Editor (like the // in PHP and -- in Lingo)?

Regards
/Rixn

 

[PHV]

An UDF called in an excel formula CAN'T play with ANY cell reference...
You may try this:
Function CalcValue(Arg1 As String, Arg2 As String, Arg3 As Double) As Double
' Here a comment
If Arg1 = "1" And Arg2 = "2" Then
CalcValue = Arg3 * 2.5
ElseIf Arg1 = "2" And Arg2 = "2" Then
CalcValue = 76 'Yet another comment
End If
End Function

And the formula:
=CalcValue(C14;F14;B14)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TonyJollans]

Hi Rixn,

What do you want to happen? And what is going wrong? Your code appears to work for me.

PHV,

An UDF called in an excel formula CAN'T play with ANY cell reference...?
Yes it can. It can't UPDATE any cell but it can reference them.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[PHV]

OOps, you're right Tony (as usual).

 

[Rixn]

Thank you both for your answers. It sounds logic but it still doesn't work for me. I think it's some other thing that is wrong.

When I choose Tools/Macro (Alt + F8) I can't see any macro there, but when I click Alt + F11 the code shows in the module.

If I write the macro name in the macro window (Alt + F8) and click Run I get this erros message:

"The argument isn't free of choice"
(Direct translation from Swedish)

I also get this error message when I change values in one of the cells that is send to the VB code as arguments.

"Can't find the macro SheetName.xls!Downrollbarlistwindow2_Change"
(Direct translation from Swedish)

1) Do all of theese depend on the same error?
2) What is wrong?
3) How can I fix it?

Regards
/Rixn

 

[TonyJollans]

Hi Rixn,

The reason you can't see tha macro via the Alt+F8 Dialog is because it takes mandatory arguments - there is no mechanism to pass parameters via the dialog so the macro can't be invoked from it, so it doesn't appear in the list.

I'm afraid I don't understand the second error. If you could answer a few questions it might help.

Where is your CalcValue macro?
Does it actually run when entered as a UDF on a worksheet?
If so, what goes wrong with it?
Do you have any other macros?
If so, what modules are they in?
And are any of them Event macros?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[byundt]

Rixn,
Worksheets("SheetName").Range("B14") is looking for a worksheet named SheetName. Capitalization is important--the code will fail if the sheet is named sheetname.

If you want to refer to a cell on the same worksheet that contains the formula calling the UDF, then you might refer to it as:

Dim ws As Worksheet
Set ws=Application.Caller.Parent
CalcValue = ws.Range("B14").Value * 2.5

Brad

 

[PHV]

Capitalization is important
I disagree.
Worksheets("SheetName") And Worksheets("sheetname") reference the same object in the Worksheets collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[byundt]

Right you are PHV on the capitalization issue.
Brad

 

[Rixn]

Ok, now it looks like this:

In my Excel sheet in one cell I have this formula:

=CalcValue4(C14;F14;B14)

and in the VB Editor I now have:

Sub CalcValue4(Arg1 As Long, Arg2 As Long, Arg3 As Long)
If Arg1 = 1 And Arg2 = 2 Then
CalcValue = Arg3 * 2.5
ElseIf Arg1 = 2 And Arg2 = 2 Then
CalcValue = 76
End If
End Sub


It still doesn't work. I get the same error message:

"Can't find the macro SheetName.xls!Downrollbarlistwindow2_Change"
(Direct translation from Swedish)

Should this work or is it some problem other than the code that I have to address?

I don't have any other macros.

It all started out like a small project when I had to create a nested IF formula longer than seven IFs. I found this solution to the problem:

http://www.techonthenet.com/excel/macros/if_custom.htm

I tried that code and it worked fine but then I realized that I needed to pass more than one argument.

When I thought I've found the solution it didn't work any more. That is the code you see above.

I don't know what to do next?
/Rixn
_____

 

[byundt]

You can't put a Sub in a worksheet formula, so you need to change your declaration to;

Function CalcValue4(Arg1 As Long, Arg2 As Long, Arg3 As Long)
If Arg1 = 1 And Arg2 = 2 Then
CalcValue = Arg3 * 2.5
ElseIf Arg1 = 2 And Arg2 = 2 Then
CalcValue = 76
End If
End Function

Brad

 

[Rixn]

I've tried that - it didn't work.
(I also saw some errors in the code I sent here. It should be CalcValue4 in all three places. I've tried that too and it didn't work out either.)

 

[TonyJollans]

Hi Rixn,

Still don't understand that message but I think we need some more information.

When do you get the message?
What happens when you enter your UDF on the worksheet?
What other code do you have in the workbook?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Rixn]

First I will give you the whole picture:
I have two cells (C14 and F14) which both contains values from 1 to 6 (Arg1 and Arg2). Theese are changeable so if I set the first cell to 3 and the second to 6 I want a certains calculation to happen. This means that I have more than seven IF statements so I need to use VB.

The calculations depends on a constant and a cell value (B14). The constant varies for each IF and is hard coded into the VB Editor (2.5 in the example above). The cell value is the Arg3 in the VB Editor.

When you set ay of theese cell values (C14, F14 or B14) the VB code should automatically run and update the answer (CalcValue4) in the cell that carries the "VB initiation" (=CalcValue4(C14;F14;B14)).

When do you get the message?
When I change cell C14.

What happens when you enter your UDF on the worksheet?
I don't understand what you mean. I know that UDF stand for User Defined Formula but I already have entered my formula in the cell E14 (=CalcValue4(C14;F14;B14)).

What other code do you have in the workbook?
None, that I know of.
/Rixn

 

[Rixn]

The big problem seems to be that it always ask for a makro in another Excel file. That file is the one I started out with. This file I'm working in now is created with the command NEW and then I copy & pasted alla the cells from the original file. All but one cell - the one with the VB call (=CalcValue4(C14;F14;B14))
which I re-entered.

Still it ask for a macro in thet file. When I put that file in the same folder it finds it but then it can't find the specific makro: "Can't find the macro SheetName.xls!Downrollbarlistwindow2_Change"

This macro doesn't seam to exist in that file either. I don't know why it ask for it. I haven't created such a macro with that name.

Any ideas?
/Rixn

 

[TonyJollans]

Hi Rixn,

I really don't understand this! I presume in your translation you are not using actual names of worksheets/workbooks (as you have sheet 'SheetName' and workbook 'SheetName.xls') - it might help if you gave actual names (it might not - I'm struggling a bit!)

You have a new workbook - with no code. When you copy cells into it and change one of them (C14) you are somehow trying to trigger a macro in another workbook. I don't know how but you must have a link to the old workbook. What do you see under Edit > Links?

If you post exactly what you want - I suspect it can be done without using VBA if that would help. If you have values from 1 to 6 in each of your 'control' cells you might consider using (nested) CHOOSE functions, for example.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Rixn]

What I want to do and why is described here:

http://www.techonthenet.com/excel/macros/if_custom.htm

The difference is that I want to:

A) Send more than one argument (CalcValue(C14;F14;B14) instead of (CalcValue(A1)).

B) Calculate a value depending on a argument (Arg3) and return the result instead of just return a value.

 

[TonyJollans]

Hi Rixn,

The code in that link is straightforward - and passing extra parameters should not be a problem. Clearly you have something else causing your problem and what we have to do is identify and fix it, which is why I ask you questions. You can see your workbook(s) - I can't; all I can do is ask questions and, based on the answers, maybe ask more in order to try and find the problem.

Or we have to find another way to do what you want - which is, again, why I am asking questions. I repeat that if your values really are 1 - 6 in each of two cells a (relatively) simple nested CHOOSE construct should give you your answer without any code at all. For example:
[blue][tt]=CHOOSE(C14,B14*CHOOSE(F14,0.5,2.5,4.5,5,6,7),CHOOSE(F14,66,76,86,96,106,116)....[/tt][/blue]
I don't know your actual requirements so can't give a proper formula.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Rixn]

Now it's working! )

I had a bad link that caused all the problems. I fixed it from Edit > Links. Thank you very much for that!

Now I'm very happy.
Thank you all once again. What would I've done without you all. I've learned a lot during this problem. Hopefully I can fix minor problem like this by my self in the future.

Bye for now.
/Rixn

 

[TonyJollans]

Glad you're sorted

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[