Werkzeug zum Erstellen von Programmablaufplänen für Windows

PapDesigner ist ein cooles und leichtes Werkzeug zum Erstellen von Programmablaufplänen (PAP) angelehnt and die DIN 66001. Ich benutze es, um Matlab Algorithmen zu entwickeln.

Delete sheets without confirmation prompts using VBA in Excel

Hi people,

i was searching for a way to delete a worksheet without confirmation prompt in vba and i found this link.
the code is listed here:

Sub DeleteSheet(strSheetName As String)
' deletes a sheet named strSheetName in the active workbook
Application.DisplayAlerts = False
Sheets(strSheetName).Delete
Application.DisplayAlerts = True
End Sub

check if a excel workbook is open

hi people, today, i discovered this function. it is very usefull, thanks to the authors.

the link to the function is here.
and the function is here:

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
WorkbookOpen = False
On Error GoTo WorkBookNotOpen
If Len(Application.WorkBooks(WorkBookName).Name) > 0 Then
WorkbookOpen = True
Exit Function
End If
WorkBookNotOpen:
End Function

to call this function, use for example:

If Not WorkbookOpen("MyWorkbookName.xls") Then
Workbooks.Open "MyWorkbookName.xls"
End If

vba function to calculate the column value from a number

Hi, this function returns a script, which contains the value of the columns for excel. for example, if you call MyColumn(1), you'll get the string "A". If you call MyColumn(27), you'll get "AA".

Function myColumn(column As Integer) As String
'******************************
' author: tomas mezger
' tmezger_at_ffe.de
' date: 12.10.2007
'******************************
' this function returns a script, which contains the value of
' the columns for excel
' for example,
' MyColumn(1) = A
' MyColumn(26) = Z
' MyColumn(27) = AA
' MyColumn(100) = CV
' MyColumn(256) = IV
' MyColumn(257) = NULL <--- Error. Excel has only 256 columns!!!!

Dim column1 As Integer
Dim column2 As Integer


column2 = (column - 1) Mod 26 + 65
'MyColumn2 = Chr(column2)
' das liefert das hintere Teil des Bezuges
If column > 256 Then
myColumn = "NULL"
MsgBox ("Error, column must be <= 256 !!!")
Else
If column <= 26 Then
myColumn = Chr(column2)
Else
column1 = WorksheetFunction.RoundDown(((column - 1) / 26), 0) + 64
myColumn = Chr(column1) & Chr(column2)
End If
End If

End Function

very simple sound (signal) generation in matlab and vba

hi people.

for my scripts, i sometimes put a sound-generating function at the end, that lets me know the script is finished.

to do this in matlab, use this command

sound(ones(1,10))

in vba, use

beep()

check if a excel chart as a legend in vba

hi people, i had a problem with a vba script and i needed to check if a chart in excel had a legend active.

for that, use this code:

If ActiveChart.HasLegend Then
ActiveChart.Legend.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "normal"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With

'With Selection.Border
' .Weight = xlHairline
' .LineStyle = xlAutomatic
'End With
Selection.Shadow = True
End If

copy as picture from excel

Hi people.

a new trick: in excel you can copy a selection "as picture" into the clipboard. to do this, just select the table you whant to copy (or the range) and hold on shift. then click on edit (continue holding shift) and you'll see something like "picture copy" (sorry, my excel is in german, i don't know what's the name for this function in english).... there you are.

another posibility is to create a macro and give him a shortcut. put this single line in the macro:

Selection.CopyPicture Appearance:=xlPrinter, Format:=xlPicture

and that's it.

enjoy it

Speed up vba

Hi again.

a small hint for vba users. if you move large amount of data and your script takes too long to run, consider putting the following lines at the beginning of the script:

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

at the end of the script, you should put this lines:

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

"add sheets after". How to add sheets in excel

Hi people. if you have the problem that excel always create new sheets at the left side of the current one, you have to try this script. just add it to your personal vba module and make a shortcut for the keyboard.

the code is:

Sub add_sheets_after()
Dim mySheet As String
Dim myNewSheet As String
mySheet = ActiveSheet.Name
Sheets.Add
myNewSheet = ActiveSheet.Name
Sheets(myNewSheet).Select
Sheets(myNewSheet).Move After:=Sheets(Sheets.Count)
End Sub

change the Cell mode in excel from A1 to R1C1 using vba

To do this, you can write a simple macro as follows:

Sub bezugsart()
'
' bezugsart Makro
' Makro am 30.10.2007 von TMezger aufgezeichnet
'

If Application.ReferenceStyle = xlA1 Then
With Application
.ReferenceStyle = xlR1C1
End With

Else

With Application
.ReferenceStyle = xlA1
End With
End If

End Sub