INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

.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.

RE: .bas File

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,

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 Function 




RE: .bas File

(OP)
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

RE: .bas File

Give this a try...

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 Function 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: .bas File

(OP)
Hi Skip

Thanks for responding. clown

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

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: .bas File

(OP)
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.

RE: .bas File

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)


RE: .bas File

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close