Sub Test(ByRef objRange As Range)
objRange.Value = "Hi"
End Sub
Sub TestTheTestMethod()
With ThisWorkbook. Sheets("Sheet1")
Set objRange = .Range(.Cells(1, 1), .Cells(i - 1, 3))
objRange.Value = "Hi" 'This works fine !
Test (objRange) 'But here... Getting ERROR 424 -- Object Required
End With
End Sub
I have a code below which need some input from user. This input will also be serve as the input of the subroutine which i am going to call. However, i do not know how to go assign this input to the subrountine which i will be calling, can anybody help ?
For example, the "input" variable will also be served as an input in subroutine test2 ...
I used to be pretty good with VBA, and I know I am a quite decent programmer in other languages, but I have been working on this for six hours and have no idea what I am doing wrong. Any help would be appreciated.
Here is my class (I know I can do better with access modifiers/ properties in a serious version):
'The class is called ConsCell
Public Car As Variant Public Cdr As Variant
Private Sub Class_Initialize() End Sub
Here is the code from the module which tries to call it:
Function MakeCell() Cell = New ConsCell 'Execution always stops here-- but with no error message Cell.Car = 15 Cell.Cdr = "NIL" MakeCell = Cell End Function
Function PrintCell(c As ConsCell) PrintCell = c.Car End Function
The code for a cell in the spreadsheet itself is this:
I am using excel to stop and start a service on the network and have that part done when using a txtfield to enter in the PC ID. What i want to do is use a list of PCIDs and pass them to my service object to stop and start the service.
Private Sub CommandButton1_Click()
Worksheets("PCIDs"). Range("B2").Select 'my issue is here Range("B2").Activate 'and here
Do Until IsEmpty(ActiveCell) Call StopService("ServiceName") ActiveCell.Offset(1, 0).Select Loop
Range("B1").Activate Do Until IsEmpty(ActiveCell) Call StartService("ServiceName") ActiveCell.Offset(1, 0).Select Loop
Worksheets("ServiceName").Select End Sub
and with this function i need it to pass as a string to txtDeviceID. I have tried just simply setting txtDeviceID as ActiveCell but it didn't like that.
Public Function StopService(ServiceName As String) As Boolean
Dim oSysInfo As New ActiveDs.WinNTSystemInfo Dim oComp As ActiveDs.IADsComputer Dim oSvcOp As ActiveDs.IADsServiceOperations Dim sCompName As String Dim sSvc As String Dim lRet As Boolean
I copied a routine from this forum that would email a single sheet from a 10-sheet workbook to a recipient. This worked great for a long time, then all of a sudden I started getting the "ActiveX control can't create object" error. Here is my
I'm trying to open a file and perform various tasks. This is the structure:
Dim xlApp As Object Dim xlWb As Object
Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set xlWb = xlApp.Workbooks.Open(BackupFile) ' backupfile evaluates to c: esting est.xls xlWb.Worksheets(1).Activate
Debug.Print xlWb.Worksheets.Count Dim ws As Worksheet For Each ws In xlWb.Worksheets Debug.Print ws.Name ws.Activate Next ws I think I'm totally missing something. The above works, except for the loop. None of the names show up in my immediate window, and none of the worksheets are activated. xlWB evaluates to nothing, which is where the problem has to be...but why would my first .Activate and Debug.Print lines work? Also, If I declare xlWb as Workbook then absolutely nothing works after the file is opened.
Basically what I want to do is open the file, do a bunch of a stuff with it, then save and close it. I have this all working without creating another object, but the opening of the file is still visible to the user. Seems like turning off ScreenUpdating doesn't truly work.
I want to create a macro to open a webpage, and with that webpage fill out the fields on the webpage and then hit enter and move to next record. I am having great difficulties with establishing Internet Explorer as my object and maneuvering around inside of it. My data would being in column A so i would expect to use the string below when referencing my data.
Do Until Workbooks(2).Worksheets(1). Range("A" & row) = ""
I am attempting to call a Function from a cell and pass it a range and an Integer. The Integer works fine, but I cannot get the Range to pass into the function correctly.
I am using a spreadsheet with two sheets, basically, one sheet has a combo box control, you select a name from there and click on "Go". The function is supposed to go into the next sheet (Sheet2) and retrieve a value from there (in the same row as the selected name).
I am using the following code (on button click):
Dim strValue As String Sheets("Sheet2").Select strValue = Range("A1").Value MsgBox (strValue)
I've simplified this, but essentially my problem is this - it isn't picking up any value at all - there is text entered in A1 so what is the problem?
I am trying to generate excel report output from access by click of a button. Part of the code is as follows:
Dim lngColumn As Long Dim xlx As Object, xlw As Object, xlc As Object Dim DBS As DAO.Database Dim rst As DAO.Recordset Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
blnEXCEL = False
'If we do not want the first row we set Header to false blnHeaderRow = False
'Establish an EXCEL application object On Error Resume Next Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then Set xlx = CreateObject("Excel.Application") blnEXCEL = True End If Err.Clear
I worked on development on a server and worked fine. However when I have moved the code and database to the desktop of mine I am having the following error in the line
Following bit of code runs fine if placed in Word VBA:
Code: Public Sub TaskUsageInExcel() Dim tsk As Task For Each tsk In Tasks 'Gives RTE 429 when run through Excel Debug.Print tsk.Name Next tsk End Sub
So I referenced 'Microsoft Word 12.0 Object Library' in Excel and ran the code and it gave me this error 429. Noticeable part was, no instance of word was running at that time.
So I modified the code as below:
Code: Public Sub TaskUsageInExcel2() Dim wdApp As Word.Application Dim tsk As Task
i have two arrays that I want to use in a trend function. I don't think i can just use the array as is in the fucntion so my guess is that I need to pass the array into a range of data, and help on how I can do this? (also this is in VBA, fyi)
VBScript code snippet below is being used in an ASP page but the call to GetObject never attaches to a running instance of Excel. Since visible property is set to True, I can see the instances accumulate in the TaskBar each time the ASP page is called, so there are obviously instances to attach to.
Dim excelApp Set excelApp = Server.GetObject(,"Excel.Application") If (err <> 0) Then Set excelApp = Server.CreateObject("Excel.Application") End If excelApp.UserControl = True excelApp.Visible = True excelApp = Nothing
GetObject errors out with the following error: Error - Number:429 Source:Microsoft VBScript runtime error Description:ActiveX component can't create object
I need to pass range information (eg. WorkSheets("abc").Range("A1") as text or ??) from a cell (ie the above text is in a cell on some worksheet, say "XYZ") to a VBA procedure or could directly use it in the procedure, similar to...
Sub MyProc(RngInfo as string [or?]) dim RRng as range Set RRng= RngInfo ??? it is this part I'm really not sure how to do
where RngInfo is a worksheet that has a "named" cell that contains the above cell with the indicated range in it.
eg. RngInfo is worksheets("XYZ").Range("D1") where D1 contains the text (or ?)WorkSheets("abc").Range("A1")
I've only indicated these as text items because I'm not sure what/how you would do this. The end purpose of this is to pass variable Range information from a cell on a WS either into (or use within directly) a VBA Sub. I guess I could pass the WS and Range Address as a single string separated by a "Char" and use Split to separate them and then recombine using Worksheets(Parm(0)).Range(Parm(1)) but it seems like it should be easier than that.
Have a spreadsheet for creating employee schedules (any number of employees, 3 rows per employee, 5 columns for each day). I want to move all employees' info for one day (5 columns and XX rows) to another area and then clear the employees' data for that day. I have the code working for Monday but I don't want to duplicate it 6 more times for Tue -Sun. I have set up named ranges (i.e. MonFT, TueFT, etc.
I would like to call one subprocedure to do this and pass parameters for each day so the ranges can be changed to reflect the desired day?
I am doing this for holidays; i.e. if there is a Wed. holiday, store all of Wednesday's data and clear Wed. Then restore all Wed data when preparing the schedule for the following week.
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet.
Sub ConsDiscChart() ActiveCell.Offset(29, 11).Range("A1").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Offset(0, -1).Range("A1:C24").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub
I am setting a variable to the value of an absolute cell reference. I want to set the value of another variable to just the row value of that absolute cell reference.
I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error:
FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error.
Dim OutApp As Object Dim OutMail As Object Dim SigString As String Dim Signature As String
'Set OutApp = CreateObject("Outlook.Application") Set OutApp = GetObject(, "Outlook.Application") If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0)