Excel + VBA help needed

[OSUguy98]

In trying to simplify my life here at work, I've done a crapload of spreadsheets to calculate everything from rebar quantities to load ratings for bridges.... In trying to automate as much of the calculations as possible, I have HUGE formulas that, once written, are hard to follow.... So I'm trying to take a few of the longer formulas and create my own functions to make things easier to follow... I figured out how to make the functions, but........ I have to remember 5 independent variables to pass to the function, and I don't always remember which order they were in.....

So is there a way to make a function say what is needed next? Excel does this for it's native functions, like SUM()... it tells you what info it needs, and has hyperlinks for the definitions of the data entry... Is there any way to do this for a user-defined function? or am I SOL?


Keep on Folding!! For the [H]orde!!

 

[Hartlove]

I don't know how to get the little info pop-up, but I found something that might be useful. type in your function name, ie: =sum() and then click on the little fx at the top of the screen, right next to the formula bar. It should bring up a pop-up window with places for all of the values that are required. This works for built-in and UDF functions.

 

[OSUguy98]

Thanks for the tip... That'll help me out, but not necessarily other people in the office that may start using it.... but I've got plenty of time to figure that out... No sense doing the harder stuff until you're done tweaking the original program.....


Keep on Folding!! For the [H]orde!!

 

[craigmp]

Removed my post.....Just read the post again an realized what you were asking....my bad

 

[OSUguy98]

Well... I've been messing around with code for the last couple days... Thanks to google and a few times of beating my head against the wall.... I finally came up with a function that turns this:

Into this:

Doesn't sound like much, but considering I knew nothing about VBA as of 2 weeks ago..... I'd say it's pretty good....

I still haven't figure out how to make this little popup thing which is why I started this thread originally.... but I figured I'd share this "Don't ask me how it works, I'm not 100% sure it'll work for everything" code.... I warn you, I'm not a programmer by any stretch of the imagination, so not everything is commented, but it works for me.... so far....

Function WeightSum(size As Integer, coating As String, abutment As String, location As String)
       
    Dim maximum As Integer
    Dim i As Double
    Dim total As Double
    Dim totaltemp As Double
    Dim counttemp As Integer
    Dim marktemp As String
    Dim check As String
    Dim sizetemp1 As Boolean
    Dim sizetemp2 As Integer
    Dim numbertemp As Double
    Dim lengthtemp As String
    Dim weighttemp As Double
       
       
    maximum = Application.WorksheetFunction.max(Worksheets("Plan Rebar").Range("A:A"))
    total = 0
       
'/Begin For Statement
    For i = 1 To maximum
        totaltemp = 0
        counttemp = i + 2
        
        '/Get Mark
        marktemp = Worksheets("Plan Rebar").Cells(counttemp, "B")
        
        If marktemp = "-" Then
            sizetemp2 = 1
        Else
            '/Check Coating
            If Mid(marktemp, 5, 1) = "e" Or Mid(marktemp, 6, 1) = "e" Then
                check = "Epoxy"
            Else
                check = "Black"
            End If
            
            sizetemp1 = IsNumeric(Mid(marktemp, 2, 1))
                
            If sizetemp1 = True Then
                sizetemp2 = Mid(marktemp, 2, 1)
            Else
                sizetemp2 = Mid(marktemp, 3, 1)
            End If
        End If
               
        If check = coating Then
           If sizetemp2 = size Then
                '/Get Location of Bar
                If abutment = "Abutment 1" Then
                    If location = "Footing" Then
                        numbertemp = Application.WorksheetFunction.Sum(Worksheets("Plan Rebar").Cells(counttemp, "E"))
                    End If
                      
                    If location = "Breast Wall" Then
                        numbertemp = Application.WorksheetFunction.Sum(Worksheets("Plan Rebar").Cells(counttemp, "F"))
                    End If
                        
                    If location = "US WW" Then
                        numbertemp = Application.WorksheetFunction.Sum(Worksheets("Plan Rebar").Cells(counttemp, "I"))
                        End If
                        
                    If location = "DS WW" Then
                        numbertemp = Application.WorksheetFunction.Sum(Worksheets("Plan Rebar").Cells(counttemp, "J"))
                    End If
                End If
                    
                If abutment = "Abutment 2" Then
                    If location = "Footing" Then
                        numbertemp = Application.WorksheetFunction.Sum(Worksheets("Plan Rebar").Cells(counttemp, "G"))
                    End If
                        
                    If location = "Breast Wall" Then
                        numbertemp = Application.WorksheetFunction.Sum(Worksheets("Plan Rebar").Cells(counttemp, "H"))
                    End If
                        
                    If location = "US WW" Then
                        numbertemp = Application.WorksheetFunction.Sum(Worksheets("Plan Rebar").Cells(counttemp, "K"))
                    End If
                        
                    If location = "DS WW" Then
                        numbertemp = Application.WorksheetFunction.Sum(Worksheets("Plan Rebar").Cells(counttemp, "L"))
                    End If
                End If
                
                '/Temporary numbertemp
                '/numbertemp = Application.WorksheetFunction.Sum(Worksheets("Plan Rebar").Cells(counttemp, "F"))
                
                '/Get Length of Bar
                lengthtemp = CInches(Worksheets("Plan Rebar").Cells(counttemp, "D"))
        
                '/Get Weight of Bar
                weighttemp = Application.WorksheetFunction.Sum(Worksheets("Plan Rebar").Cells(counttemp, "O"))
        
                '/Calculate Weight of All Bars at Location
                totaltemp = numbertemp * lengthtemp * weighttemp / 12
            Else
                totaltemp = 0
            End If
        '/Add to Total
        Else
            totaltemp = 0
        End If
        
        total = total + totaltemp
        
        
    Next i
'/End For Statment
    
    If total = 0 Then
        WeightSum = "-"
    Else
        WeightSum = total
    End If
    
End Function

So what do you guys think?


Keep on Folding!! For the [H]orde!!