.bas File

[Kenja824]

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.

 

[HiZ]

Kenja824,
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,

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 Function

 

[Kenja824]

Thanks HyrumZ

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

 

[SkipVought]

Give this a try...

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 Function

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Hi Skip

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!"

 

[SkipVought]

If you want the third argument to be optional, then you don't use the third argument if you have no third metal.

If you want code that always uses 3 arguments, but the third may have no value, then that's another code modification.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

Yeah thats the problem. We are gven a spreadsheet with hundreds of weld spots and I have to look up the info given in the spreadsheet and interpret certain attributes from them. It would be too difficult to add the formula to a cell and have to select the two or three cells for each spot. This is a case where I need to add the formula and drag it down the column and it works whether only two cells have numbers in them or three.

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.

 

[MechaPro]

The difference between SkipVought's code and the one you used is,


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)

 

[BUGGAR]

Ignore me if I'm not getting this but would a for-next statement help here?