Live-Forum - Die aktuellen Beiträge
Datum
Titel
07.05.2024 21:30:42
Bilder aus URL in Zelle einfügen - Code anpassen
07.05.2024 20:36:09
xverweis / name Funktion
07.05.2024 20:10:42
Datum z.B. 1897 wird nicht als Datum angezeigt
07.05.2024 19:47:07
Excel Lohnabrechnung anhand 2 Suchkriterien
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1924to1928
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1924to1928
1924to1928
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Funktion Werte aus Sub übergeben

Funktion Werte aus Sub übergeben
13.04.2023 08:05:43
Joachim

Hallo,
ich lese per Sub aus einer geschlossenen Datei einzelne Zellen aus. Die Sub ruft dazu eine Funktion auf:


Sub faktoren_holen()
Dim erdg As Double, heiz As Double
erdg = xl4Value("'W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx]EG_Strom'!R2C2")
heiz = xl4Value("'W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx]EG_Strom'!R5C2")
End Sub

Die Funktion:

Function xl4Value(strParam As String)
xl4Value = ExecuteExcel4Macro(strParam)
End Function

Das funktioniert auch einwandfrei. Nun möchte ich einer anderen Funktion die Werte übergeben, damit diese rechnen kann. Sie sieht so aus:

Function COZWEI(Jahr As Integer, Verbrauch As Double, Vertragsart As Integer, EVU As Double) As Double
COZWEI = Verbrauch * erdg
End Function

Diese Funktion soll als Formel in der Tabelle verwendet werden.
Wie schaff ich es, dass die Funktion COZWEI die Werte der Variablen erdg und heiz bekommt?
Mit call faktoren_holen geht's jedenfalls nicht.

Gruss
Joachim

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Funktion Werte aus Sub übergeben
13.04.2023 08:39:51
volti
Hallo Joachim,

eine Sub gibt keine Werte zurück sondern verarbeitet in der Regel alles intern. Benötigst Du Werte auch außerhalb der Sub, musst Du sie entweder per Referenz beim Aufruf übergeben oder Sie außerhalb der Sub dimmensionieren oder auch als Public Variable.

Macht in Deinem Fall m.E. wenig Sinn. Warum gehst Du über drei Stufen?

Ich würde die Sub weglassen oder gleich alles in der Funktion erledigen

Function COZWEI(Jahr As Integer, Verbrauch As Double, Vertragsart As Integer, EVU As Double) As Double
COZWEI = Verbrauch * xl4Value("'W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx]EG_Strom'!R2C2")
End Function
Gruß KH


Anzeige
AW: Funktion Werte aus Sub übergeben
13.04.2023 08:58:30
Joachim
Hallo,

wenn ich das so schreibe, bekomme ich den Fehlerwert #WERT!

Gruss
Joachim


AW: Funktion Werte aus Sub übergeben
13.04.2023 10:03:43
Oberschlumpf
Hi Joachim

warum machst du es dir - grundsätzlich - so schwer, unbedingt aus - geschlossenen Dateien - DAten auszulesen?

Gibt nix Leichteres, als mit VBA Datei öffnen - gewünschte Daten auslesen - Datei wieder schließen, z Bsp


'...nur der Teil des Codes, den ich eben erwähnte
Dim erdg As Double, heiz As Double

WorkBooks.Open("W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx")
With ActiveWorkbook
    erdg = .Sheets("EG_Strom").Range("B2").Value
    heiz = .Sheets("EG_Strom").Range("B5").Value
    .Close False 'DAtei ohne Speichern schließen
End With
'ab hier deine Werte in erdg + heiz weiterverarbeiten
Hilfts?

Ciao
Thorsten


Anzeige
AW: Funktion Werte aus Sub übergeben
13.04.2023 10:27:45
Daniel
Hi
Variablen, die wie erdg und heiz von mehreren Subs und Funktionen verwendet werden solle, müssen als Prohektweit gültig deklariert werden.
Dazu erfolgt die Deklaration in einem allgemeinen Modul (Modul1) oberhalb der Subs und Functions mit Public:
Option Explicit
Public erdg As Double
Public heiz As Double

Sub faktoren_holen()
   erdg = xl4Value("'W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx]EG_Strom'!R2C2")
   heiz = xl4Value("'W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx]EG_Strom'!R5C2")
End Sub

Function xl4Value(strParam As String)
   xl4Value = ExecuteExcel4Macro(strParam)
End Function

Function COZWEI(Jahr As Integer, Verbrauch As Double, Vertragsart As Integer, EVU As Double) As Double
   COZWEI = Verbrauch * erdg
End Function
Du musst allerdings sicherstellen, dass Faktoren_holen zuerst ausgeführt wird, also vor COZWEI.

Eine andere Alternative wäre, dass du erdg und heiz selbst als Funktion erstellst, wobei das langsam wird, wenn du die beiden Werte oft verwendest.

Function erdg() as double
   erdg = ExecuteExcel4Macro("'W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx]EG_Strom'!R2C2")
End Function


Function heiz() As Double
   heiz = ExecuteExcel4Macro("'W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx]EG_Strom'!R5C2")
End Function

Function COZWEI(Jahr As Integer, Verbrauch As Double, Vertragsart As Integer, EVU As Double) As Double
   COZWEI = Verbrauch * erdg()
End Function


Anzeige
AW: Funktion Werte aus Sub übergeben
13.04.2023 11:01:56
Joachim
Hallo Daniel,

ich will ja COZWEI als Formel in einer Zelle schreiben: =COZWEI(A2;B2;C2;D2)
Deine Idee mit erdg als eigene Funktion geht leider auch nicht, Fehlerwert #WERT!.
Die Funktion erdg selbst klappt, hab mir erdg in eine Zelle schreiben lassen, aber von COZWEI aus erdg laufen zu lassen, klappt nicht.
Die Funktion COZWEI ruft nicht die Funktion erdg auf.

Joachim


AW: Funktion Werte aus Sub übergeben
13.04.2023 11:23:49
volti
Hallo,

eine Idee wäre noch, die "einmalige" Ermittlung bei Blattaktivierung oder auch Arbeitsmappenaktivierung durchzuführen und später nur noch die global definierten Variablen zu benutzen...

Private Sub Worksheet_Activate()
 ' Faktoren_holen
   erdg = ExecuteExcel4Macro("'W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx]EG_Strom'!R2C2")
   heiz = ExecuteExcel4Macro("'W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx]EG_Strom'!R5C2")
End Sub
Gruß KH


Anzeige
AW: Funktion Werte aus Sub übergeben
13.04.2023 12:19:35
Joachim
Hallo,

die Dateien, wo die Formel verwendet werden soll, sind Exporte mit bis zu 3500 Zeilen aus einer Datenbank. Mit dem Worksheet_Activate müssten die dann xlsm sein. Geht also nicht.
Einzige Alternative sehe ich momentan, anstatt einer Formel den Kollegen eine UserForm (als Add-In) anzubieten, wo sie eintragen, in welcher Spalte welche Werte stehen und dann rechnet das Makro von Zeile 2 bis Ende die Werte aus und schreibt sie in eine zusätzliche Spalte.
Eine Formel wäre mir aber lieber...
Vielleicht hat noch ein andere eine Idee.

Joachim


AW: Funktion Werte aus Sub übergeben
13.04.2023 12:45:45
Oberschlumpf
andere Idee = Datei mit VBA öffnen, den 3500 Zeilen langen Range in PUBLIC Array-Variable übergeben, Datei ohne Speichern wieder schließen, mit Array-Inhalten weiterarbeiten

aber DAS, bzw so öhnlich hatte ICH ja schon vorgeschlagen, wobei DU nich mal geantwortet hattest


Anzeige
AW: Funktion Werte aus Sub übergeben
13.04.2023 13:01:52
Joachim
dein Vorschlag mit Datei öffnen, auslesen und wieder schließen hatte ich ausprobiert. Lief leider auch auf Fehler #Wert!.
Die Kollegen, die damit arbeiten sollen/werden, haben mit VBA nix am Hut. Daher geht nur eine Formel oder eine UserForm.
Sie sind nur Anwender.

Joachim


AW: Funktion Werte aus Sub übergeben
13.04.2023 13:30:13
Oberschlumpf
ähhh??? aber für deine favorisierte "Formel" (Funktion) benötigst du doch auch VBA! Makros müssen also in jedem Fall aktiv sein....aber ok, du schaffst das schon...irgendwie


Ich verstehe nicht so ganz...
13.04.2023 21:17:08
Yal
Reden wir tatsächlich nur von 2 Werte, die aus einer anderen Datei "geholt" werden sollten?

Warum nicht einfach 2 Zellen dafür reservieren und die Formel darin schreiben? Die Zelle könnten sogar benannt werden, um per Name in den anderen Formeln gerufen zu werden.

Das gesamt läuft dann komplett ohne VBA
(wenn es ohne VBA nicht "cool" genug ist, könnte man sich etwas mit einer Bluetooth-Verbindung noch ausdenken. Kleine Scherz.)

VG
Yal


Anzeige
AW: Funktion Werte aus Sub übergeben
13.04.2023 11:00:37
volti
Hallo zusammen,

hier noch ein Gedanke von mir zum Thema...

Die Funktion funktioniert in der Tabellenfunktion (bei mir) nicht. Ein Aufruf aus der VBA-Ebene dagegen schon.
Außerdem ist zu Bedenken, dass bei häufiger Nutzung als Tabellenfunktion, wie schon von Daniel angesprochen, die Ermittlung aus der Datei (ob nunr aus geschlossener oder temporär geöffneter (Oberschlumpf)) das zeitmäßig nicht zweckmäßig ist.

Mein u.a. Versuch ist gescheitert....
Public erdg As Double

Function COZWEI3(Verbrauch As Double) As Double
' Einmalige Ermittlung
  If erdg = 0 Then
     erdg = ExecuteExcel4Macro("'C:\Users\volti\Desktop\MyTools\[Mappe1.xlsx]Tabelle1'!R2C2")
  End If
  COZWEI = Verbrauch * erdg
End Function

Function COZWEI(Verbrauch As Double) As Double
  COZWEI = Verbrauch * ExecuteExcel4Macro("'C:\Users\volti\Desktop\MyTools\[Mappe1.xlsx]Tabelle1'!R2C2")
End Function

Sub Test()
  MsgBox COZWEI(8)  'klappt, in Tabellenfunktion klappt's nicht
End Sub
Gruß KH


Anzeige
AW: Funktion Werte aus Sub übergeben
14.04.2023 01:23:32
onur
Eine Funktion kann zwar mittels ExecuteExcel4Macro Werte aus geschlossenen Dateien holen, ABER diese Funktion darf NICHT als Formel in einer Zelle stehen, da keine Excel-Funktion etwas anderes machen darf, ausser einen Wert auszuspucken. Eine Funktion in einer Zelle darf nicht andere Zellen verändern oder auf fremde Dateien zugreifen, das wird mit "Wert!" bestraft. Das gilt ebenso für alle anderen Zell-Funktionen, die diese Funktion in irgendeiner Weise benutzen/aufrufen.
"Mit dem Worksheet_Activate müssten die dann xlsm sein. Geht also nicht." - Häääh? Du musst die Sub zum Auslesen in die Mappe einbauen, die sowieso schon die von dir geposteten Makros enthält.
Das gehört in ein STANDARDMODUL
Public erdg As Double
Public heiz As Double
DAS auch:
Public Function COZWEI(Jahr As Integer, Verbrauch As Double, Vertragsart As Integer, EVU As Double) As Double
   COZWEI = Verbrauch * erdg
End Function
Das gehört in Workbook_SheetActivate der Mappe ( oder Workbook_Open), damit erdg und heiz schon befüllt werden, bevor sie gebraucht werden:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   erdg = ExecuteExcel4Macro("'W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx]EG_Strom'!R2C2")
   heiz = ExecuteExcel4Macro("'W:\Zentrales\40_CO2-Äquivalente\[CO2-Äquivalente.xlsx]EG_Strom'!R5C2")
End Sub
Function xl4Value ist völlig überflüssig.
Dann kannst du COZWEI in Excel-Zellen verwenden
Warum hast du in 
Function COZWEI(Jahr As Integer, Verbrauch As Double, Vertragsart As Integer, EVU As Double)
Jahr, EVU und Vertragsart als Parameter, wenn diese bei der Berechnung von COZWEI nicht die geringste Rolle spielen ???

Anzeige

Links zu Excel-Dialogen

UserForm

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige