Functions Definition

[joeyjj]

Hello All,

I am kind of new to setting function and I am running into a problem while trying to make a program. Below is my situation

Static Function FE(x)
    FE = 62.5 * (2 ^ (x - 1))
End Function

Static Function LD(x)
    LD = "LD" & FE(x)
End Function

For x = 1 To 8

LD = 17 * PA ^ -0.25 * FE(x) ^ -0.85 * length2

Next x

These are the two functions I want to use. Basically FE calculates a number and LD should show up as such if x = 0 FE = 62.5 therefore LD(X) = LD62.5.

The reason for this is because when I go to assign the numbers to excel the assignments go as follows LD62.5 LD125 LD250 and so on. What is the best method for this?

 

[Kenneth Hobson]

Sub test_LD()
  Dim PA As Integer, length2 As Integer, x As Integer
  Dim LDx As Double
  
  PA = 1
  length2 = 1
  For x = 1 To 8
    LDx = 17 * PA ^ -0.25 * FE(x) ^ -0.85 * length2
    MsgBox "LDx = " & LDx & vbLf & "LD(x) = " & LD(x), , "x = " & x
  Next x
End Sub

Static Function FE(x As Integer) As Double
    FE = 62.5 * (2 ^ (x - 1))
End Function

Static Function LD(x As Integer) As String
    LD = "LD" & FE(x)
End Function

 

[joeyjj]

Thank you for your fast response!

This works great however, I would like to be able to have VBA assign LD62.5 LD125 and such as variable such that the following would work:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim DRow As Long
Dim Dws As Worksheet
Set Dws = Worksheets("Detail_Data")
Set ws = Worksheets("Input_Data")

'find first empty row in database
If ChBLine = True Then

iRow = ActiveCell.Row

Else

If ws.Range("A2").Value <> "" Then
    iRow = ws.Range("A1").End(xlDown).Row + 1
Else
    iRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
End If
End If

'Report First Row For Acoustic Summary

    DRow = Dws.Range("B" & Rows.Count).End(xlUp).Row + 2
    
If CBUnits = "Inches/Feet" Then

WidthValue = Me.Width1.Value
HeightValue = Me.Height1.Value
Lining = Me.Lining1.Value
length2 = Me.Length1.Value
RndOrRect = Me.RndOrRect.Value

Else

WidthValue = Me.Width1.Value * 0.0393700787
HeightValue = Me.Height1.Value * 0.0393700787
Lining = Me.Lining1.Value
length2 = Me.Length1.Value * 3.2808399
RndOrRect = Me.RndOrRect.Value

End If

If RndOrRect = "Rectangular" Then
PA = (2 * WidthValue / 12 + 2 * HeightValue / 12) / (WidthValue * HeightValue / 144)
Else
End If

For x = 1 To 8

LD(x) = 17 * PA ^ -0.25 * FE(x) ^ -0.85 * length2

Next x


'

'copy the data to the database
Rows(iRow).Insert Shift:=xlShiftDown
ws.Cells(iRow, 1).Formula = "=if(offset(A" & iRow & ",-1,0,1,1) = """",1, SUM(offset(A" & iRow & ",-1,0,1,1))+1)"
ws.Cells(iRow, 2).Font.Bold = True
ws.Cells(iRow, 2).Value = "Duct"
ws.Cells(iRow, 3).NumberFormat = 0
ws.Cells(iRow, 3).Value = -LD63
ws.Cells(iRow, 4).NumberFormat = 0
ws.Cells(iRow, 4).Value = -LD125
ws.Cells(iRow, 5).NumberFormat = 0
ws.Cells(iRow, 5).Value = -LD250
ws.Cells(iRow, 6).NumberFormat = 0
ws.Cells(iRow, 6).Value = -LD500
ws.Cells(iRow, 7).NumberFormat = 0
ws.Cells(iRow, 7).Value = -LD1000
ws.Cells(iRow, 8).NumberFormat = 0
ws.Cells(iRow, 8).Value = -LD2000
ws.Cells(iRow, 9).NumberFormat = 0
ws.Cells(iRow, 9).Value = -LD4000
ws.Cells(iRow, 10).NumberFormat = 0
ws.Cells(iRow, 10).Value = -LD8000
ws.Select
ws.Cells(iRow + 1, 1).Select

As you notice in my code I need LD1000 to be referenced to ws.Cells(iRow, 7).Value = -LD1000. Is this possible or am i out of my mind.

Please note this is part of my code as other parts of my code are irrelevant and may be confusing. I appreciate any advice or help.

 

[Kenneth Hobson]

You can not define the value as being both numeric and string. You could set a numeric format and simulate it.

e.g.

Sub t()
  Range("A1").NumberFormat = """-LD""#"
  Range("A1").Value2 = 63
  MsgBox Range("A1").Text
  MsgBox Format(Range("A1").Value2 + 1, """-LD""#")
End Sub

 

[joeyjj]

I am actually trying to update some old code from what appears to be from DOS. The way it is described is as follows:

 Dim TT(8), FE(8)

'Extra information that is not relavent

FL = 125

for I = 1 to 8: FE(I) = 62.5*(2^(I-1)): Next I:

'Extra information that is not relavent

for I = 1 to 8

If FE(I) < FL Then TT(I) = 10 * log((FE(I) * 10): Next I

Print Using " ###"; TT(1); TT(2)

I don't know much about this style of coding and I am wondering if it is possible to do this similar style in VBA?

 

[joeyjj]

I was able to just change my defined variables to LD(1) LD (2) and I defined LD as follows:

Dim LD(8)

I can't explain why this works but it did and helped me reduce pages of coding to 1 or 2 lines.

Thanks for all your help Kenneth!