Excel Vba Macro Programming 1 Create a Macro: With Excel VBA you can automate tasks in Excel by writing so called macros. Hi Michael, ----> Diese Seite Freunden empfehlen <------, =SUM(IF(LEFT(Ges!E2:E10000,1)="D",1/COUNTIF(Ges!E2:E10000,Ges!E2:E10000))), Range("A2").FormulaArray = "=SUM(IF(LEFT(Ges!E2:E10000,1)=""D"",1/COUNTIF(Ges!E2:E10000,Ges!E2:E10000)))", Cells(11, 7).FormulaArray = "=SUM(IF(B5!R2C5:R10000C5<>"""",1/COUNTIF(B5!R2C5:R10000C5,B5!R2C5:R10000C5)))", Public Function fktBlatt_da(strName As String, Optional WB As Workbook) As Boolean. Lesezeit: < 1 Minute Sie können in Excel VBA ganz einfach Makros erstellen und diese dann miteinander verbinden. 14 2014-03-31 21:49:43 Buras. VBScript ... Meine Bitte wäre es etwas zusammenzustellen, was das Makro pro Datei in dem Ordner mit der Endung GA.XLSB ausführ ohne Excel jeweils zu öffnen. Do you have a Idea how I can get them back Working? For i = 1 To collCountries.Count VBA Sleep function is a windows function present under windows DLL files which is used to stop or pause the macro procedure from running for a specified amount of time after that certain amount of we can resume the program.. Thanks Big Al! is there any way I can see all input values which I have given previously as soon as launch the GUI. Of course you can also do this by pressing the F5 key or the Run Macro button in the VB Editor, but what if your macro contains arguments? 24. when running a macro, (processing 100,000s rows) when the cursor is in the ‘active worksheet window’ i.e. Each time you hit it, it will run the highlighted code, then move to the next line. Die Prozedur über eine Schaltfläche oder eine Form ausführen 94. Next C Expertly explained for a Excel VBA simpleton like me who is looking to expand on the basics. Thanks! O’Reilly members get unlimited access to live online training experiences, plus books, videos, and digital content from 200+ publishers. Dim collCountries As New Collection, Dim sheetRead As Worksheet Zum einen können Sie ein Makro mit den Namen Auto_open() einsetzen, welches automatisch ausgeführt wird, wenn die Arbeitsmappe geöffnet wird. After a bit of practice I am finding now that I can usually find some combination of workarounds that work constantly for a given procedure. This is the best adsense alternative for Thanks for sharing! Visual Basic Programmierung mit dem Excel-VBA-Editor Eine Übersicht der wichtigsten Eingaben Um ein neues Programm anzulegen, müssen Sie die folgenden zwei Schritte ausführen. Learn how to get answers about your Excel file, quickly run macros, debug your code, and more. Hey Tom, DoEvents: DoEvents Danke, Carsten Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform. Think of it like a blank cell in a worksheet. Excel VBA-Funktion aktualisiert nur bei Änderung (1) . In this chapter, learn how to create a simple macro. Visual Basic Script (VBS) / Active Server Pages (ASP) » Excel Macro mit VBScript ausführen ohne Excel öffnen Schleife für mehrere Dateien. When you click inside the Immediate Window you will just see a blank box with the text cursor flashing. ( hopefully )” Even if the user changes the fill color of the sheet, the contents in cell A1 will still be hidden after the code is run. Greetings from Santiago of Chile. Once the process sets the variables, you can mouse over the variable (the actual text) and it will show you what it's set to. Veränderungen auf dem Tabellenblatt können direkt eine ereignisgesteuerte Prozedur auslösen. http://www.eileenslounge.com/viewtopic.php?f=30&t=29579#p229156 You can use Windows API calls in VBA to determine the window state and cursor position. For R = 1 To UBound(Arr, 1) Finally I changed the lSht variable to 5 by using the equals sign (lSht = 5). In the code window, select Workbook from the left drop down list. That’s very convenient and quick to understand. I used to use the Watch window and have the code stop at a certain value of my variable but now I can set that value straight away. The VBA terms Private and Public declare the access allowed to the term it's attached to. The code in the image above is included in the free sample file you can download below. Das wird natürlich in den seltensten Fällen ausreichen. Arr = Range(“A1:J12”) VBA Immediate Window Examples.xlsm (109.9 KB). With that “xxxxx” being some variable that the code is calculating or producing. wide 8rows down section on the same a4 sheet .need a vba code for this. Jan Karel’s suggestion allows you to run a multi-line macro in the immediate window by using the colon “:” as a line separator. ‘Application.VBE.Windows(“Direktbereich”).SetFocus There are some workarounds using sendkeys and also this solution posted on Daily Dose of Excel. Drag it out of the VB Editor window. If I use the For…next in code to send e-mail the next row overwrite existing row and in e-mail I see only one row with last data of loop. As well as referring to cells on a spreadsheet with Range you can use Cells.The Cells property has an Item property that you use to reference the cells on your spreadsheet:. Many developers will setup their VB Editor differently than the default layout. […]. So many thank jon, He wanted to know how I made the Immediate Window float on top of the Excel application in some of the screenshots. The following screencast shows a few examples of how we can use the Immediate window to get the value, number format, and fill color of the active cell. You can use the Row or Column property of any of those methods to return the row and column numbers. Another bonus to this type of formatting is that cells containing data points formatted this way won’t show up on a chart either, so it’s possible to use this to hide points or even series names based upon conditions. Hi Jon, Plus weekly updates to help you learn Excel. I tried to open from different modules but still can get both VAB editor and immediate window showing up at the same screen. My Immediate Output: I want to move all the values displayed in immediate window to a text file. How to you run a Private Sub, and execute each line???? End Sub The VBA Immediate Window is an awesome tool that allows you to get immediate answers about your Excel files, and quickly execute code. Please leave a comment below with your suggestions, or any questions. When you left-click and hold the Immediate Window and drag it over the very bottom of the VB Editor window, the dotted outline will expand horizontally across the bottom. The immediate window becomes a free floating window that you can put on top of Excel. Verwenden Sie unter anderem Formel in den VBA-Makros um umfangreiche und komplizierte Berechnungen auszuführen. Nutzen Sie Excelmakros als benutzerdefinierte Platzhalter um Ihre tägliche Arbeit mit Excel zu vereinfachen. This is its default location. ... 0 Application.OnTime VBA ohne Schließen und Öffnen Excel; 2 VBA - Warten 1 Minute vor der nächsten Sub; Beliebte Fragen. Hilfe suche eine Formel zur Zeitberechnung!!!! The simplest use for the Immediate window is to quickly get information about the workbook that you currently have open and active in the background. I need to print 11 clmns. Left-click and hold on the top bar of the immediate window. At first glance this doesn't look too exciting, but the Immediate window can be a very powerful and useful tool. Was also ist VBA? I’m not sure which window you are referring to. Umfangreiche Informationen und Beispiele erhälst Du indem Du einfach auf den jeweiligen Befehl klickst. after things like message boxes or shapes. I would like to use this copy/string in to the code as a contens of e-mail. Next iRow strnewRow = “” I’ve done all the VBA commands to run faster to ‘false’ , cleaned up my registry, repaired Office 2016 countless times, removed pre-fetch… etc…. trying to execute code, an uncertain how, pressed F8 to run through Private Sub and not working can someone help? Das folgende Makro wird beispielsweise ausgeführt, wenn in der Zelle A1 bzw. More about me... © 2020 Excel Campus. Application.SendKeys Keys:=”^g” Please let me know if you have any other questions. Microsoft Excel stellt fünf unterschiedliche Varianten zur Auswahl, ... Für dieses Training sollten Sie gute Excel-Kenntnisse mitbringen, VBA-Vorkenntnisse sind nicht erforderlich. At the moment I am having some success with procedures of the following form. I don’t have any experience with that issue yet. Next i The Debug.Print line creates a list of empty sheets in the Immediate Window, and we can then manually check each of those sheets to make sure they are really blank. MsgBox collCountries.Count Thank You Very Much. This is a simple example, but the Immediate Window can come in handy when stepping through code. You should be able to get the Immediate Window to redock at the bottom by dragging it over the bottom of the VB Editor screen. Zeitaufwändige Arbeiten erledigt das Programm für Sie ganz alleine. Some people have issues with it. Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac. Wenn der Projekt-Explorer nicht angezeigt wird, können Sie zum anzeigen > Projekt-Explorers wechseln, oder drücken Sie STRG + R . Notice that, in default, the Open will be displayed in right … ‘Application.SendKeys “^g ^a {DEL} {HOME}” through workbook name. I really appreciate your support! I made a good decision to enroll in your VBA Pro course. any other useful sources you can give a beginner like me would be greatly appreciated. I have a post & video series on finding the last used row and column, and there is also training on this inside the course. Hi Sam, There are plenty of different ways to write this code, but in this example I use the “lSht” variable in a For Next loop to loop through the worksheets and then add the sheet name to the active sheet. The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel. Brauche ich, um mein makro ausgeführt, alle 10 Minuten . This is a great set of tips. Yes, you can ask questions while in break mode. Debug.Print is telling VBA to print that information in the Immediate Window. Thank you in advance. For hiding the contents of a cell: Rather than making the text color the same as the fill color, a custom number format like “;;;” will hide the contents of a cell without the need to adjust it at any point, even if font color and fill color are tweaked. Hopefully MS will address the issue. Thanks Mjo! Hello and welcome! Debug.Print sh.Name In der täglichen Excel-Praxis sollen Makros oft nur dann ausgeführt werden, wenn Werte in bestimmten Zellen erfasst oder geändert werden. Dies ermöglicht es, ... excel excel-vba vba. Quelle Teilen. You can also use the following line of code to make a worksheet “very hidden”. Alle Makros in diesem Modul werden im Bereich auf der rechten Seite aufgelistet. […] If you do any programming in Excel, read Jon Acampora’s tips for using the Immediate window. For my own part, I will usually have a “read me” tab in any workbook that has macros, data connections, hidden cells, hidden rows, hidden columns or hidden sheets that describes the location and function of any of these things, especially if I’m using them in an unusual way. I need to display a msgbox with some text like “Total count is” and some value which is a count from one of the collections in the macro. Have you ever seen VBA code on the internet that contains a line similar to the following? You can also step through the code by pressing the Step Into command on the Debug menu. Another example is to hide the contents of a cell by making its font color the same as its fill (background) color. thanks so much. Free file to download contains VBA code samples. It’s great to see that for the most part every time I google info on VBA code you come up. I also have been getting inconsistent results with VBA coding to control the immediate window. The example below is a simple macro that enters the current date (Now) in the cell , and changes the font color to blue (Color = vbBlue). Wie kann ich den DOS-Befehl PING von VBA aus ausführen und das Ergebnis auswerten? This post will explain 5 different uses for the Immediate Window. Geben Sie den folgenden Code in das Modulblatt ein: VBA Macro to Create Power Query Connections for All Excel Tables, How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar, Excel Roundup 20140929 « Contextures Blog, http://www.eileenslounge.com/viewtopic.php?f=30&t=29579#p229156, free video training series on macros & VBA, VBA code to compare two cells for same value not working 100%, free 3-part video series on getting started with macros & VBA, post & video series on finding the last used row and column, video with more info on those critical assumptions. It is possible to copy a content of Immediate window to the variable as a string by code VBA in Excell? But it will take some experimenting, as sometimes a DoEvents or two in the wrong place can have the opposite effect and stop a previously working code from working!!!! If Excel VBA cannot find the ID (flag is still False), it adds the record to the next empty row. 06/08/2019; 2 Minuten Lesedauer; o; o; In diesem Artikel. with workbook name, how to find path in immediate window. If I pass a range which is a part of a row i.e. The variable emptyRow is the first empty row and increases every time a record is added. Arrayformeln in VBA dürfen nur 128 Zeichen lang sein, bei mehr bekommst du einen Fehler. Really very useful information.. helps a lot.. Double click ThisWorkbook in Project – VBAProject pane to open the ThisWorkbook (Code) window.. 2. Run VBA code when close or open workbook. Jon, how to pull text from another window? Otherwise, thanks for the great information on the immediate window and it’s many wonderful uses. I want to move all the output in the immediate window to a text file. Debug.Print strnewRow, that’s something that has bothered me a bit recently as I started to work with array and had issues to debug (not having an IT education), I am already sure I’ll really save some of my time in the future, […] document.write(''); First off, make sure your variables are referring to the correct cell… It looks like CellA is referring to cell "D31" and CellB is "G31", but you're changing cell "G32" to "Pick a value…" Secondly, to walk through the code, make sure your cursor is in the macro you're wanting to run, and hit F8. So please help me. As an example, the following example would return the address of the last used row in column A to a variable. Debug.Print “Don’t blink, or you will miss this!.. 31. Public Sub TryAgen() Er erklärt Ihnen zunächst die notwendigen VBA-Grundlagen und wichtigsten Werkzeuge sowie VBA-Abläufe des VBA-Editors. This is a great website that explains some very handy methods for using the Immediate Window: 5 Ways to Use the VBA Immediate Window – Excel Campus […]. You can concatenate or add to the string in the loop. for many years i use immediate window in VBA but i just use it for print debug now i realized how to manipulate variable in debugging and step ruining of subroutines. By the way do you know a way for add some data in a code a force the VBA to read data and set some variables. This is great for writing and debugging code. And thanks for stopping by! The ultimate goal of this macro may be to delete all empty (blank) sheets in the workbook, but we might want to test the code first before actually deleting any sheets. Howdy! In your illustrations you have the immediate window sat on top of a sheet, how do you do this or is just to illustrate what happens when you do the code? So können alle oder bestimmte Zellen beliebig per VBA überwacht werden. i found it helpful. I hope that helps get you started. Can you please give me some tips that how to loop all the rows and print each row or load the range to an array and print the array. A suggestion for the future, if it were possible, would be for you to introduce such a feature: ( At other sites, you typically get that “deep link” if you click on the date next to the person’s name who commented ) (Jan Karel Pieterse added this feature to his existing comments a few years back quite quickly, so maybe he might have an idea how to do it ) Thanks again! Often by pasting into a cell it is more difficult to see the mistake. The following example is a macro that creates a list of all the sheets in the active workbook. Automatische Makros Auto Macros. Funktionsprozeduren ausführen 97. You don't have to write a whole macro if you just need to perform one line of code to your workbook. How to empty the window via VBA code would interesting as well, since running a macro several times will always append Debug.Print information. Double-clicking the header will re-dock to the last position that the window was docked to. The problem is that when “Runnning” the code it doesn’t work at all. Thank you very much. Convert Pivot Table to SUMIFS Formulas – Free VBA Macro. So if the Immediate window was docked to the right or left side, it will re-dock to that location when it is double clicked. Thanks, Release the left mouse button to dock it. In the image above I used the question mark to check the value of the lSht variable. All Rights Reserved. Putting the question mark (?) It is built into the Visual Basic Editor, and has many different uses that can be very helpful when writing macros, debugging code, and displaying the results of your code. Erweitern Sie im VBA-Projekt-Explorer auf der linken Seite den VBA-Projekt Ordner für Ihre Arbeitsmappe, und doppelklicken Sie dann auf das ThisWorkbook -Modul. Erstellen 31 mär. ?range(“C15”).Formula it has only one dimension then fine .Value2(1) = 1, .Value(2) = 2. Die Prozedur vom Dialogfeld Makro aus ausführen 92. Pete asked a great question in the comments below. umsetzen können Sie unter dem folgenden Link nachlesen: … pl.help. This will effectively skip some of the sheets in the loop because I changed the variable from 2 to 5. Once you understand the capabilities of this tool, you will find yourself using it all the time. Kapitel 1 Was ist VBA? I’m looking to find the row and column of the cell that my loop ends in, not it’s value. Blockieren Sie die Neuberechnung systematisch durch Setzen von Datei / Optionen / Formeln / Arbeitsmappenberechnung auf "Manuell" und deaktivieren Sie … Daten eingeben, ein Tastendruck zum ausführen des Makrosgenügt und fertig ist das Ergebnis. Thanks for the suggestion! my question is: Wie kann ich mein Makro alle 10 Minuten ausführen lassen? You can do this with any of the windows in the VB Editor including the Project, Properties, Locals, and Watch windows. Type this into the immediate window and hit enter: For Each sh In Worksheets:Debug.Print sh.Name:Next. How can I achieve that? not the window inside text. With the code paused, the Immediate Window can be used to evaluate or change variables. ( hopefully )” with new monetization method. The contens of the Imediate windows is as table from debug.print with From….next loop. I strive to be clear about what’s going on in most reports that I make, because I’ve been frustrated before by a transformation that occurs on a hidden worksheet or something to that effect. Hi Sumanth, I’m trying to find a template or plugin that might be able to correct this problem. Keyboard shortcut is Alt,d,i. , Thanks guys. But the column can be a number or letter: You can evaluate any line of VBA code in the Immediate Window, and it will immediately give you the result. Here is an article on the best keyboards for Excel with more info on that. Worksheets(“Sheet1”).Visible = xlVeryHidden. This makes it very fast and easy to type code in the Immediate Window. Your instruction was straightforward, but now I can’t get the immediate window to dock again. If you are new to VBA, the Immediate Window will be very useful as you start learning and writing code. Any suggestions. It is built into the Visual Basic Editor, and has many different uses that can be very helpful when writing macros, debugging code, and displaying the results of your code. Application.VBE.Windows(“Direktbereich”).Close ‘ German Excel Thanks for sharing. Or to be more precise, I can’t get it to dock at the bottom like it was before I undocked it. Remove the question mark at the front of the statement and the Immediate Window will execute or perform that line of code. Clear explanations about a topic. I find that much more efficient then pasting into the cell whilst developing the formula, as I can easily see a mistake in the string and correct it. There could be a lot of things causing that. Great Explanation Jon easy to follow. If you are stepping through your code (F8) or add a break point (F9) or add a STOP line in your code, then the code will pause. For C = 1 To UBound(Arr, 2) Hi Ray, I want to print a row data in one line and next row data should be print in next line in immediate window or Is there any way to print entire excel sheet data in tabular form in immediate window. Some comments: 4 VBA ein Makro ausführen, wenn Zelle geändert wird, aber nicht wenn durch ein Makro; 2 Excel VBA - Ausführen eines Makros, wenn eine Zelle geändert wird; 1 Excel VBA-Makro auf alle markierten Zellen anwenden Unfortunately it’s not a very straightforward process. The VBA Immediate Window is a great tool that can help any Excel user, even if you are not writing macros. It is not allowing me to Step into by pressing F8. Thanks a lot Jon! I never thought that imm. excel vba excel-vba 37k . Do you know how to make your site mobile friendly? For Each sh In Sheets:sh.Visible=True:Next, Thanks for this awesome suggestion Jan Karel! Great question German! Navigieren Sie im Projekt-Explorer zu dem Modul mit dem Makro, das Sie ausführen möchten, und öffnen Sie es. Application.OnTime earliesttime:=Now + TimeValue(“0:00:01″), procedure:=”TraShit” Great suggestion Garcon! Die Prozedur von einer anderen Prozedur aus ausführen 96. thank you so much for your great service to the excel enthusiasts.! Die Funktionsprozedur aus einer Sub-Prozedur aufrufen 97. Teil I excel vba – die ersten schritte 29. Yes, you can have the immediate window sit on top of the Excel window. Often there are some nasty long combinations of quotes , strings, VBA variables etc.. getting the syntax correct can be assisted greatly I find by pasting my string into the Immediate window ( Debug.Print MyStringVariable ). The Immediate Window can also be used to get answers about the procedure (macro) that is currently running. In the second video we cover how to write a For Loop. Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??" My best guess would be that the Quick Analysis or Screen Animations have something to do with it. strnewRow = strnewRow & ” ” & Arr(R, C) You can also play around with code in the immediate window to see exactly what the code will do. However, you can condense it down to one line using the colon between lines. You will need to concatenate or join the string that is being created by your SubMatches macro. Debug.Print “Don’t blink, or you will miss this!.. But it can take a bit of time to hit that winning combination!!! Print #n, M.SubMatches(1) ‘ write to file The example above shows how you can specify the arguments after the macro name. 2 MsgBox: The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program. Just wanted to clarify for anyone else reading this. This is a very nice clear and concise Blog. Application.SendKeys “^g ^a {DEL} ” ‘ {HOME}” Here’s one I used today that removes the page break lines on the sheet. 30. So können Sie z. Wie mit vba ausführen alle 10 Minuten? in the ‘Tray’ portion of the screen I get 6,000 rows a min processed, in the active window I get ~800 rows. Für diesen Fall, der eher der Normalfall ist, stellt der VBA-Editor Module zur Verfügung, und jedes Modul wird … ?range(“C15”).FormulaLocal, I just remembered another small observation… Once in a while suggesting Ctrl+G to get the Immediate window has caused some confusion. but it gets window title Hi Jon, _2a) Sometime I try to build up some complicated formula strings in VBA to then past into a cell or use within the VBA Evaluate(“ “). For arguments that are string variables (text), you will need to wrap the variable in quotation marks. Das Modulblatt hinter „Tabelle1“ wird geöffnet. However, you can call the macro from the Immediate Window. Aufruf einer Funktion von einer Formel in einer Tabelle aus 98. procedure:= Yes, it is possible. Um noch mehr aus Excel herauszuholen, empfiehlt sich die VBA-Programmierung, mit der Sie Excel auf Ihre Bedürfnisse zuschneiden können. If it's not there you can press Ctrl+G or View menu > Immediate Window. That is very interesting behavior. I’ll add it to the list for future videos. Was ist mit VBA möglich? You might want to try opening it from the Developer tab in the ribbon by pressing the Visual Basic button. For example, to find out how many sheets are in the active workbook, type the following line of code in the Immediate window and then press the Enter key. (i.e. Here is a link to the first video. Debug.Print collCountries(i) Sub ResetImmediate2() Sorry, I’m not sure what would be causing that. _2b) I often need to translate formulas from German to English Excel. Setting a variable’s new value is a cool trick Jon. Klicken Sie auf der Registerkarte Entwicklertools auf Visual Basic, um den Visual Basic-Editor (Visual Basic-Editor ) zu starten. End If I use that a lot when I need to get some quick and dirty things done, like getting a list of all worksheet names or aligning a bunch of shapes horizontally. I can’t tell you how much I’ve learned from Jan Karel’s site at http://www.jkp-ads.com/, I always knew Jan Karel would trot out his colon party trick sooner or later. collCountries.Add sheetRead.Cells(iRow, 1) can this be called from immediate window? Then I used the variable to get the sheet name of the sheet that is currently being processed in the loop. Application.OnTime earliesttime:=Now, procedure:=”TryAgen” Do you have any video of that? Please log in again. Running macros with arguments and single lines of code will be so helpful! Zum … Public Sub TraShit() End Sub In addition to the coding forms given , I also find that I may need to add a code line such as The VBA Immediate Window is an awesome tool that allows you to get immediate answers about your Excel files, and quickly execute code. The forums are probably going to be the best place to post the question or look for solutions. Application.VBE.Windows(“Immediate”).SetFocus On Error Resume Next ‘Debug.Print String(5, “*”) & ” Hi there mom. On some other similar Blog sites , I can get a “deep link”, that is to say a URL that takes me to a specific comment. My weblog looks weird when browsing from my apple iphone. I would like to pass it on to people when I try to explain the immediate window. Every Excel user can benefit from the Immediate Window, even if you're not writing macros. but my Imideate window does not work, it may have to do with the fact that my (Righthand mouse key) copy or paste do not work eather . ‘Application.VBE.Windows(“Direktbereich”).SetFocus Enable the workbook, press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.. 2. Wie Sie dies in Excel einstellen bzw. 0. .Value2(1,1) = 1, .Value(1,2) = 2 etc). Application.OnTime earliesttime:=Now, procedure:=”TryAgen” is there some ‘window state’ that excel use to recognize where the cursor is and due to all the new options, SKYPE, YAMMER, O365, etc. Subscribe above to stay updated. ‘Debug.Print collCountries.Count, Dim i As Long I certainly agree with the auditing comment. Here is a video with more info on those critical assumptions. Thanks very much Application.VBE.Windows(“Immediate”).Close ‘ English Excel Other sets of tasks … I hope that makes sense. Using the Immediate window with simple commands like these is a nice way to get a formula translation which I can then copy from the Immediate Window Well the Immediate Window is very similar, so let's look at 5 examples that will help you get the most out of this magical box. 32. Please let me know if this is possible. If you have any suggestions, please share. If I pass a 2D range and then pick a row or column with the .Rows() method then the resulting range still has 2D even though one dimension only has one element (e.g. Think of it in terms of a private company versus a public company. It's pretty boring until you add a formula to it, right? I realize this is an old post, but I thought it would be helpful to chime in on the docking of the immediate window. Intellisense is the drop-down menu that displays the properties, methods, and members of the object I'm referencing. You can also use the Address property to return the address of the cell. I hope that helps. Hi Yogesh, VBA in Excel - Easy Excel Macros Excel Vba Macro Programming - I think it is very interesting and useful. MsgBox (“Total number of customers from US is ” + collCountries.Count) Dim Arr() As Variant Bitte bedenke, dass nicht alle Befehle für jede Excel Version kompatibel sind und sich teilweise in der Verwendung unterscheiden können. Good catch. This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Now from this list, you want to extract all the hyperlink URL addresses. Ich würde . _1) You also have some interesting comments. If sheetRead.Cells(iRow, 2) = “United States” Then Dim C As Long Debug.Print M.SubMatches(1) ‘ write to immediate Read the comments on that page before using the code. I have created a GUI using VBA for an EXCEL sheet. I agree that it would be a “nice to have” feature though. Jon, Thanks a lot for your information. Range(“A1”).Font.Color = Range(“A1”).Interior.Color. So sparen Sie sich in Zukunft unzählige Mausklicks. Some prefer to have the project and properties windows on the right side instead of the left. Q mark equaling a “question” for the immediate window: The Q mark is actually a shortcut for “Debug.Print” and functions this way in many programming languages. You can use the Resize property of the Range object for that. I’M HUNG UP WITH A PROBLEM THAT AFTER ALT F11 THE DISPLAY OF THE EDITOR DISAPERS IN LESS THAN ONE SECOND!?