Application.WorksheetFunction.Index(array, row, column) If you specify a zero value for row or column, then you’ll get the entire column or row that is specified. Example: Application.WorksheetFunction.Index(array, 0, 3) This will give you the entire 3rd column. If you specify both row and column as non-zero, then you’ll get only the specific element. There is no … Read more
I could be wrong, but I think this may be something to do with the way classes are instantiated in VBA. For example: Dim oClass1 as Class1 Set oClass1 = new Class1 Is different than Dim oClass1 as New Class1 In the second case I believe the constructor doesn’t get called until the property is … Read more
Jordan’s answer is a fine one, but your problem runs deeper than whether or not you know VBA. Basically, you have rude coworkers and, if you hope to impress them, becoming a neophyte in an area in which they are highly skilled is unlikely to help. The better way (IMO) to deal with this sort … Read more
If you are wanting to just copy the whole column, you can simplify the code a lot by doing something like this: Sub CopyCol() Sheets(“Sheet1”).Columns(1).Copy Sheets(“Sheet2”).Columns(2).PasteSpecial xlPasteValues End Sub Or Sub CopyCol() Sheets(“Sheet1”).Columns(“A”).Copy Sheets(“Sheet2”).Columns(“B”).PasteSpecial xlPasteValues End Sub Or if you want to keep the loop Public Sub CopyrangeA() Dim firstrowDB As Long, lastrow As Long … Read more
Inside the VBE, Go to Tools -> References, then Select Microsoft XML, v6.0 (or whatever your latest is. This will give you access to the XML Object Library. Updated with fancy pic!
Private Sub Workbook_Open() on error goto Oops version = “1.0” Set objHTTP = CreateObject(“WinHttp.WinHttpRequest.5.1”) URL = “<WEB SERVICE>” objHTTP.Open “POST”, URL, False objHTTP.setRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)” objHTTP.setRequestHeader “Content-type”, “application/x-www-form-urlencoded” objHTTP.send (“version=” + version) exit sub Oops: ‘handle error here End Sub If you wanted to, for example, change the URL … Read more
If the user clicks Cancel, a zero-length string is returned. You can’t differentiate this from entering an empty string. You can however make your own custom InputBox class… EDIT to properly differentiate between empty string and cancel, according to this answer. Your example Private Sub test() Dim result As String result = InputBox(“Enter Date MM/DD/YYY”, … Read more
Use the WScript.Shell instead, because it has a waitOnReturn option: Dim wsh As Object Set wsh = VBA.CreateObject(“WScript.Shell”) Dim waitOnReturn As Boolean: waitOnReturn = True Dim windowStyle As Integer: windowStyle = 1 wsh.Run “C:\folder\runbat.bat”, windowStyle, waitOnReturn (Idea copied from Wait for Shell to finish, then format cells – synchronously execute a command)
The OP asked, is it possible to reference a table, not how to add a table. So the working equivalent of Sheets(“Sheet1”).Table(“A_Table”).Select would be this statement: Sheets(“Sheet1”).ListObjects(“A_Table”).Range.Select or to select parts (like only the data in the table): Dim LO As ListObject Set LO = Sheets(“Sheet1”).ListObjects(“A_Table”) LO.HeaderRowRange.Select ‘ Select just header row LO.DataBodyRange.Select ‘ Select … Read more
I like this way: ActiveSheet.UsedRange.Rows.Count The same can be done with columns count. For me, always work. But, if you have data in another column, the code above will consider them too, because the code is looking for all cell range in the sheet.