VBA "IF' statement

[boarder236]

I am currently working on a macro that will decode the cell above it with an "if" statement.
There are around 60 different possibilities, so the statement is long.

I typed the equation into a cell on excel.
From there I recorded macro, and entered it into all the needed cells in the workbook.

It is very large and I think that may be the problem.
How could I break this macro up so the different macros work together, meaning if I run 1 then 2, so 2 doesnt just cover up 1 if 1 was true.

Any help would be great.

Thanks

 

[boarder236]

Its all working just how I want, I have been trying to get it to open when the file is opened. So I put it in that function but you cant put a function in a funciton so how can i do this?

 

[Joe4]

So I put it in that function but you cant put a function in a funciton so how can i do this?

What's your second function?
I only know of one Function and one Sub Procedure.

You can use Functions in Sub Procedures (like I did in my last post), and you can call Sub Procedures from other Sub Procedures.<!-- / message -->

 

[boarder236]

The other function is the one that converts the data the machine gives into an excel format.


Here is that:

Function AnaOpen()
Dim AnzAnalog As Integer
Dim AnzMessungen As Integer
Dim Abtastrate As Integer
Dim FloatWert As Single
Dim Zeit As Long
Dim AnzWerte As Integer
Dim myRange As Range

setdir
AnalyseDatei = Application.GetOpenFilename("Analysis of Data Files (*.m*),*.m*,All (*.*),*.*", 10, "Import Data Analysis")
If AnalyseDatei = False Then
GoTo EndeAnaOpen
Else
Open AnalyseDatei For Binary Access Read As 200 '200 ist Filekennung
Worksheets("Sheet1").Activate
Cells.Select
Selection.ClearContents
Range("A1").Select
' Worksheets("Tabelle1").Clear
tstString = InputB(75, 200)
Get #200, , AnzMessungen
Get #200, , Abtastrate
tstString = InputB(4, 200)
'MsgBox Abtastrate
Get #200, , AnzAnalog
'MsgBox AnzAnalog
Cells(1, 1) = "Zeit"
Cells(2, 1) = "ms"
Rows(1).Font.FontStyle = "Fett"
Rows(2).Font.FontStyle = "Fett"

tstString = InputB(258, 200)
For i = 1 To AnzAnalog
Cells(1, i + 1) = Input(14, 200) '1.Kanalnamen einlesen
tstString = Input(12, 200) 'Bytes einlesen
Cells(2, i + 1) = Input(8, 200) 'Einheit ausgeben
tstString = Input(4, 200) 'Bytes einlesen
Next i


ReDim Werte(1 To AnzAnalog) As Single
AnzWerte = AnzAnalog + 1
ReDim WerteReihe(1 To AnzWerte) As Single
Application.ScreenUpdating = False
Cells(3, 1) = 0 ' Zeit Anfang

For i = 1 To AnzMessungen
Get #200, , Werte
For j = 1 To AnzAnalog
WerteReihe(j + 1) = Werte(j)
Next j
tstString = Input(8, 200) '2 Digitalworte
Get #200, , Zeit
WerteReihe(1) = Zeit
Set myRange = Range(Cells(i + 2, 1), Cells(i + 2, AnzWerte))
myRange.Rows = WerteReihe
Next i
Application.ScreenUpdating = True
End If
Close
EndeAnaOpen:

'Convert ms to seconds

Dim myLastRow As Long

' Find last row of data in column A
myLastRow = Cells(Rows.Count, "A").End(xlUp).Row

' Insert column B
Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

' Insert formula in column B
Range("B3:B" & myLastRow).FormulaR1C1 = "=RC[-1]*0.001"

'Adds title Seconds
'
' TitleSeconds Macro
'
'
Range("B2").Select
ActiveCell.FormulaR1C1 = "s"
Range("B3").Select


End Function

So when this opens, I want it to auto start the code you helped me with. I placed a code for auto adding a column for something else I was doing but that didnt have a function or sub, it was just the code.

How could I go about doing this?

 

[Joe4]

How that other function working?

Seems to me like it should be more of a Sub Procedure than a Function.
Typically, which Functions, you usually return/calculate a single value based on some inputs (think of Excel's native functions).

Things which are a bunch of steps and change a whole range of cells at once are typically set up as Sub Procedures.

I think you want one "Main" Sub Procedure which calls all your different Sub Procedures/Functions in the order you want them in.

 

[boarder236]

I have been playing around with it for a while and I cant figure it out. How can I place the function you wrote up that has all the conversions, with my other subs. do i keep them all seperate?

When I run it through pushing F8 it works, now I need to figure out how to do it automatically

 

[Joe4]

Like I said in the previous post, create a "Main" Sub that runs them all in order.

Let's say you have three subs that you want to run, Sub1, Sub2, and Sub3, then your Main Sub would look like this:

Sub Main()
   Call Sub1
   Call Sub2
   Call Sub3
Exit Sub

So then, you would only need to run your Main Sub, and it runs them all in that order.

 

[boarder236]

Awesome, i got it to work.

Because I have been toying around with vba so much, I randomly saved a bunch of VBA's and I cant find how to delete them.

Its buggin me because there are 5 I dont even want

 

[Joe4]

Do you mean deleting the actual Module itself (because you can remove all the VBA code simply by highlighting it and hitting delete)?

To remove the Modules, right click on their name in the VBA Project Explorer and select "Remove ModuleName". You can say "No" when it asks you if you would like to export a copy of it before deleting it.