.bas File
.bas File
(OP)
I have a couple .bas files someone once made for us to perform certain tasks in excel. I have manipulated them slightly to do things I needed but now I need something I cant figure out.
I was wondering if someone could write the code to do something fairly simple?
The Completed Formula when we add it to the spreadsheet would look something like...
=Weld_Ratio(A1,D1,G1) The three cells would be the cells we select for it to use.
We will pretend that A1= 2.5, D1= 1.0 & G1= 1.0
It would look at the numbers in the three cells and look for particular combinations
1) If the ratio between A1 & D1, or D1 & G1 is more than 3 to 1, it will return "3 to 1 Ratio"
2) If the ratio between the two outside cells A1 & G1 are more than 2 to 1, it will return "2 to 1 Ratio"
3) If the Total of the three cells is larger than 6.0 it will return "6mm Weld exceeded"
So with the numbers listed in the above example cells, since the two outside cells A1 & G1, A1 (2.5) is more than twice the number in G1 (1.0) this formula would return "2 to 1 Ratio"
If multiple infractions are found, I would want it to list all of the infractions.
"2 to 1 & 3 to 1 ratios"
"2 to 1 Ratio & 6mm"
3 to 1 Ratio & 6mm"
2 to 1, 3 to 1, & 6mm"
I am sorry for the trouble if this is a bigger deal than I realize. I am fair with excel formulas but when it comes to these .bas files code, I am nearly as green as they come. All I know is from looking at the couple we have. I do appreciate any help.
I was wondering if someone could write the code to do something fairly simple?
The Completed Formula when we add it to the spreadsheet would look something like...
=Weld_Ratio(A1,D1,G1) The three cells would be the cells we select for it to use.
We will pretend that A1= 2.5, D1= 1.0 & G1= 1.0
It would look at the numbers in the three cells and look for particular combinations
1) If the ratio between A1 & D1, or D1 & G1 is more than 3 to 1, it will return "3 to 1 Ratio"
2) If the ratio between the two outside cells A1 & G1 are more than 2 to 1, it will return "2 to 1 Ratio"
3) If the Total of the three cells is larger than 6.0 it will return "6mm Weld exceeded"
So with the numbers listed in the above example cells, since the two outside cells A1 & G1, A1 (2.5) is more than twice the number in G1 (1.0) this formula would return "2 to 1 Ratio"
If multiple infractions are found, I would want it to list all of the infractions.
"2 to 1 & 3 to 1 ratios"
"2 to 1 Ratio & 6mm"
3 to 1 Ratio & 6mm"
2 to 1, 3 to 1, & 6mm"
I am sorry for the trouble if this is a bigger deal than I realize. I am fair with excel formulas but when it comes to these .bas files code, I am nearly as green as they come. All I know is from looking at the couple we have. I do appreciate any help.





RE: .bas File
I believe this will accomplish what you are asking. Open the VBA editor and create a new module, then paste this code. It should be easy enough to customize the return strings to exactly how you want them to read. if you want it to report the ratios only if it exceeds the ratio then remove the "=" from the comparison operators. I also added a check in both directions for the ratios, you can delete the ones that are not applicable. Good luck. You can download my workbook, here, in case you have trouble getting the code inserted,
CODE --> VBA
Function Weld_Ratio(A1, D1, G1) As String Weld_Ratio = "" If (A1 / D1 >= 3) Or (D1 / A1 >= 3) Or (G1 / D1 >= 3) Or (G1 / A1 >= 3) Then Weld_Ratio = "3 to 1 Ratio" End If If (A1 / G1 >= 2) Or (G1 / A1 >= 2) Then If Not Weld_Ratio = "" Then Weld_Ratio = Weld_Ratio + ", " Weld_Ratio = Weld_Ratio + "2 to 1 Ratio" End If If (A1 + D1 + G1 > 6) Then If Not Weld_Ratio = "" Then Weld_Ratio = Weld_Ratio + ", " Weld_Ratio = Weld_Ratio + "6mm Exceeded" End If End FunctionRE: .bas File
It seems to work right for what I explained. Unfortunately I realize now I missed explaining something because I am so used to the fact I didnt think of it.
These are for weld stackups and there will often be 2 metals as well as three metals. So in this case, I only explained the 3 metal. Often the third cell will be blank and the code would return one of those results if one of the cells is 2 or 3 times the other cell that is not blank. I was so focused on the three metals at the time that I completely forgot to think about the two metal welds. Sorry.
If you can help that easy enough I would appreciate it. In the mean time I am going to take some stabs at seeing if I can figure it out for my own learning purposes. lol
RE: .bas File
CODE
Function Weld_Ratio2(A1, D1, Optional G1) As String Weld_Ratio2 = "" If IsMissing(G1) Then If (A1 / D1 >= 3) Or (D1 / A1 >= 3) Then Weld_Ratio2 = "3 to 1 Ratio" End If If (A1 + D1 > 6) Then If Not Weld_Ratio2 = "" Then Weld_Ratio2 = Weld_Ratio2 + ", " Weld_Ratio2 = Weld_Ratio2 + "6mm Exceeded" End If Else If (A1 / D1 >= 3) Or (D1 / A1 >= 3) Or (G1 / D1 >= 3) Or (G1 / A1 >= 3) Then Weld_Ratio2 = "3 to 1 Ratio" End If If (A1 / G1 >= 2) Or (G1 / A1 >= 2) Then If Not Weld_Ratio2 = "" Then Weld_Ratio2 = Weld_Ratio2 + ", " Weld_Ratio2 = Weld_Ratio2 + "2 to 1 Ratio" End If If (A1 + D1 + G1 > 6) Then If Not Weld_Ratio2 = "" Then Weld_Ratio2 = Weld_Ratio2 + ", " Weld_Ratio2 = Weld_Ratio2 + "6mm Exceeded" End If End If End FunctionSkip,
for a NUance!
RE: .bas File
Thanks for responding.
When I used this code, it works for the three metals, but when G1 (or in my actual case AH1) is empty, I am receiving a result of "#VALUE!"
RE: .bas File
If you want code that always uses 3 arguments, but the third may have no value, then that's another code modification.
Skip,
for a NUance!
RE: .bas File
I found out someone I work with here actually works with this stuff some too. He is no expert but knows a lot more than I do. He was able to take the code I got here and match it to other code he had and make it work. Not sure if it the correct way it should be done, but this is the code after his alterations.....
Function Weld_Ratio(A1 As Double, D1 As Double, Optional G1 As Double = 0) As String
Weld_Ratio = ""
If G1 = 0 Then
If (A1 / D1 > 3) Or (D1 / A1 > 3) Then
Weld_Ratio = "3 to 1 Ratio"
End If
If (A1 + D1 > 6) Then
If Not Weld_Ratio = "" Then Weld_Ratio = Weld_Ratio + ", "
Weld_Ratio = Weld_Ratio + "6mm Exceeded"
End If
Else
If (A1 / D1 > 3) Or (D1 / A1 > 3) Or (G1 / D1 > 3) Or (G1 / A1 > 3) Then
Weld_Ratio = "3 to 1 Ratio"
End If
If (A1 / G1 > 2) Or (G1 / A1 > 2) Then
If Not Weld_Ratio = "" Then Weld_Ratio = Weld_Ratio + ", "
Weld_Ratio = Weld_Ratio + "2 to 1 Ratio"
End If
If (A1 + D1 + G1 > 6) Then
If Not Weld_Ratio = "" Then Weld_Ratio = Weld_Ratio + ", "
Weld_Ratio = Weld_Ratio + "6mm Exceeded"
End If
End If
End Function
I do appreciate the help. I keep trying to learn more about code but it is something that my brain just doesnt seem to grasp very well.
RE: .bas File
The one that worked for you assumes a Default value of 0, if nothing is passed as an argument or a Null Value is passed.
So having G1=0 obviously will give you an infinite value for the following operation,
If (A1 / G1 > 2). You have a separate condition for G1=0 but the code is also calculating the last option
and generating the value based on that calculation as well. But if it works for you then that's good :)
SkipVought's code requires you to change the formulaand use only 2 cells instead of 3 (which could be a big pain)
RE: .bas File