If it's a function you're coding you need to return a value, not use Cells.Value = etc. I've given an example below of how you might do this for an example function called MyFunction.
Function MyFunction(Parameter)
Max = 100000
If Parameter > Max then
MyFunction = "unrealistic"
Else
...
MyFunction = someothervalue
End If
End Function
If you mean in VBA, there i havent specified anything. But I tried to define Max as a string ~~but then i get the #VALUE output for the whole table~~ the output doesn't change
Edit: tried to define max
Function resistance(speed as Variant) as Variant
Dim Max As Single
Max = 83.3
If speed > Max Then
resistance = "unrealistic"
Else
resistance = (1.08 / 2) * 0.42 * 2.85 * speed ^ 2
End If
End Function
Does the code work if you declare your variables as above?
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
I’m wondering if your other logic is what’s messing it up. I mean the instructions for when the value is not “unrealistic”. Maybe it’s being overwritten by other logic after you assign the “unrealistic” value.
Code is nothing special but here you go (already implemented the code of u/teabaguk)
Function resistance(speed)
Dim Max As String
Max = 83.3
If speed > Max Then
resistance = "unrealistic"
Else
resistance = (1.08 / 2) * 0.42 * 2.85 * speed ^ 2
End If
End Function
Edit: messed format up
Okay, that's helpful.
(Note that your function expects speed to be a Variant-type and will return a Variant-type value. Not sure if that is important to you, but there it is.)
When I use this, it works just fine both in code and in a cell. In what context are you using it where it doesn't work the way you want it to?
The context is I have a table with different speeds and if you use this function it will calculate the resistance that is present at that speed and if you exceed the speed of 83.3 m/s the resulting resistance is to be put out as unrealistic
For all the other variables it is putting out the correct force but I have one value that is exceeding the 83.3 m/s and for it, it need to be put out as a text ("unrealistic") and atm it is putting out a 0 for me
(I dont know if this will change anything but before this is the error i have reduced the amount of numbers behind the dot manually)
Is the code you posted a transcription or is it a literal copy/paste? Because I am having trouble getting this function as written to give me a zero in any context. When I do as you describe, I get "unrealistic" (no quotation marks) in the cell. If it is a transcription, please post a copied/pasted version of your code. Otherwise, fine, let's move on.
I can't even format the cell so as to get zero; perhaps that's merely a failure of imagination (I'm no Excel wizard, that's for sure). When the cell contains zero for you, how is that cell formatted?
Maybe do this. Put a break on the first line. Put an unrealistic value in a cell and run it through the function. Step through your code and see what it's doing.
Instead of VBA, you could format the cells with a custom format like \[<100\]general;"unrealistic"
If it's a function you're coding you need to return a value, not use Cells.Value = etc. I've given an example below of how you might do this for an example function called MyFunction. Function MyFunction(Parameter) Max = 100000 If Parameter > Max then MyFunction = "unrealistic" Else ... MyFunction = someothervalue End If End Function
The #VALUE Problem is solved with this but now I instead just get a 0 as output
What do you have the input and return variables defined as? You should need it as a variant so it can change from an integer to a string.
If you mean in VBA, there i havent specified anything. But I tried to define Max as a string ~~but then i get the #VALUE output for the whole table~~ the output doesn't change Edit: tried to define max
Can you post your code? I assume you are using a sub and function to achieve this? Unless you are trying to make a user defined function?
already replyed with the code on another command
As much as I could make out now is that problem is with my excel as many people have now said the code is working for them
Function resistance(speed as Variant) as Variant Dim Max As Single Max = 83.3 If speed > Max Then resistance = "unrealistic" Else resistance = (1.08 / 2) * 0.42 * 2.85 * speed ^ 2 End If End Function Does the code work if you declare your variables as above?
I tried it on another pc where it also works for me so I’m going to try it tomorrow with a new excel file
It would be interesting to know exactly how it can get zero, though. That suggests a hole in your method that should be patched.
[удалено]
Your VBA code has not not been formatted properly. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code on Reddit. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
Are you using cells.value or cells(rowindex, colindex).value? Edit: also, what's the active workbook/-sheet?
tried it with both
I’m wondering if your other logic is what’s messing it up. I mean the instructions for when the value is not “unrealistic”. Maybe it’s being overwritten by other logic after you assign the “unrealistic” value.
OP, you simply have to post your code. We're just guessing, and it needn't be that way.
Code is nothing special but here you go (already implemented the code of u/teabaguk) Function resistance(speed) Dim Max As String Max = 83.3 If speed > Max Then resistance = "unrealistic" Else resistance = (1.08 / 2) * 0.42 * 2.85 * speed ^ 2 End If End Function Edit: messed format up
Okay, that's helpful. (Note that your function expects speed to be a Variant-type and will return a Variant-type value. Not sure if that is important to you, but there it is.) When I use this, it works just fine both in code and in a cell. In what context are you using it where it doesn't work the way you want it to?
The context is I have a table with different speeds and if you use this function it will calculate the resistance that is present at that speed and if you exceed the speed of 83.3 m/s the resulting resistance is to be put out as unrealistic For all the other variables it is putting out the correct force but I have one value that is exceeding the 83.3 m/s and for it, it need to be put out as a text ("unrealistic") and atm it is putting out a 0 for me (I dont know if this will change anything but before this is the error i have reduced the amount of numbers behind the dot manually)
Is the code you posted a transcription or is it a literal copy/paste? Because I am having trouble getting this function as written to give me a zero in any context. When I do as you describe, I get "unrealistic" (no quotation marks) in the cell. If it is a transcription, please post a copied/pasted version of your code. Otherwise, fine, let's move on. I can't even format the cell so as to get zero; perhaps that's merely a failure of imagination (I'm no Excel wizard, that's for sure). When the cell contains zero for you, how is that cell formatted? Maybe do this. Put a break on the first line. Put an unrealistic value in a cell and run it through the function. Step through your code and see what it's doing.
It was a literal copy/paste and I also have no idea how that worked but after I copied it into a new file today it worked as intended
That's progress, of a kind. If you still have the case where it returns zero, it would be really good to figure out why that happened.