I am trying to pass a string to a function requiring a ParamArray. See below
Declare Function LibFunc1 Lib "FuncAddin" (ByVal vtSheetName, ParamArray Mylist() As Variant) As Long
Year = 2013
Period = December
MyStr = "Year#" & MyYear & """", "Period#" & MyPeriod & """"
LibFunc1("Sheet1", MyStr) 'how to declare string so it can be passed as ParaArray?
I want to display a string to users. Normally I would do that using message boxes. But that would not give me enough control over the font size, and I want to display using font size 48.
I could have used global variables, but that is not a good coding way. The other way of doing that is using property procedures. I could pass a string variable to a Label control on the form and then could format it.
1) I have a macro workbook with multiple procedures (6 main tasks)
2) Some workbooks will use all procedures, and some will not use all procedures (most use either 4 or 6 of the procedures)
3) To hide the unwanted procedures as required, the menus will not appear for those choices (in code that creates custom toolbars on activate)
1) send a paramarray argument with string elements that identify menus to use
2) send a ďshow allĒ true or false, to simplify identification of workbooks that use all procedures
Reason solution is chosen:
1) I can basically have one macro workbook this way Ė so simple implementation even where some workbooks wonít need all the macros (and should definitely not be making them menu options).
2) If I add more routines, I can just extend the ParamArray as needed and continue to identify specific toolbar options to use or not use.
1) That paramarray is interesting! It canít be passed and its initialized as an array with bounds 0 to -1. Also, it canít be tested directly to see if its Empty. So I came up with checking for the -1 UBound, combined with loading the elements into another array (seems stilted to me).
Iím not sure what Iím asking, butÖI need to determine if the paramarray is being used, and be able to pass it. Is there a simple way to do that? As Iím sitting here writing Iím realizing I could just pass a regular array and dispense with the ParamArray option -- well, Iíll post my thoughts anyway. Sorry this is so ramblingÖ
Hereís the code Iím using (basically). Note that DoSomething actually checks each element of the array to see if it matches a potential menu item (by name), and if so, marks that menu item to be added - since there's 6 menu options, I placed it in a separate method to avoid have it in the routine 6 times.
Maybe I'm just curious if anyone ever successfully uses the ParamArray option - it seems to me to be somewhat of a bother that you can't easily tell if it's been passed in or not, or use it like a regular array (check if its empty, etc.).
Sub MyToolbar(ByVal blnShowAll, ParamArray args() As Variant) Dim a() As Variant Dim x As Variant
I am trying to search through column "I" for numbers that are decimals, when the first decimal is found, use the row number that the first decimal appears in the string that populates the bookmarks stored in a word template I have created. The code I have so far checks for the decimal and then populates the bookmarks, however it requires me to input the ranges manually, meaning I can set them as the first row in my tabel. But if the decimal appears in the second row and not the first I'm bookmarking the wrong data. Here is the code I have so far;
I'm trying to simplify things a bit and create one sub with the appropriate parameters. My experience is limited with arrays so I thought I'd get snagged right where I did. I want to pass a paramarray, which contains two "columns", the access table field names and matching columns from the excel worksheet. This way, to add records to a table, I can simply pass the table name, source worksheet, and paramarray that provides the field/ column info. I'm getting the error "Wrong number of dimensions."
Sub test() Dim testArray(0 To 4, 0 To 1) As Variant 'Write the field names and corresponding columns to an array. The first array "column represents the access 'table field names, the second is the corresponding column from the excel worksheet. testArray(0, 0) = "Test1" testArray(0, 1) = 1................
Iam trying to pass a date value in a query to fetch data from a excel sheet and write the output in a different sheet.Iam able to get the output if I pass the data directly but unable to store the value and pass it in the string.
With cn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source=" & XLS_Path & _ "PZN-MTD-Opened_Header_Extract.xls;Extended Properties=Excel 8.0;" CursorLocation = adUseClient .Open End With
Set rs = New ADODB.Recordset ........................
The second subroutine below selects a range of cells, in this case a column. It selects a range that ends with the last cell that has data and starts with either the first cell that has data or the second cell has data (needed for instance if the first cell is just the heading and doesn't contain data). You'll notice I have hardcoded the "includeFirst" option right now. I don't know how to pass this as an argument when calling this subroutine.
In the first subroutine updateAll() I need to call the second subroutine and want to pass an argument to it. Maybe my approach using subs and application.run is the wrong way to do this?
Sub updateAll() includeHeader = False Application.Run "SelectColumn" ' need something like: ' Application.Run "SelectColumn(includeHeader)" ' where subroutine below has something like: ' Sub SelectColumn(includeFirst) End Sub
Sub SelectColumn() 'Select a Column or Row of Cells in a Used Range Quickly 'http://msdn2.microsoft.com/En-US/library/aa155432(office.10).aspx includeFirst = False 'includeFirst = True Dim UpBound As Range Dim LowBound As Range If ActiveCell.Row > 1 Then If IsEmpty(ActiveCell.Offset(-1, 0)) Then Set UpBound = ActiveCell........................
I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.
Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):
Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String StringReturn = Str1 & Str2 & Str3 End Function
Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3
Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Cell A1 has 123XVF1234 Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
In sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones Column B : payroll number ie 123456 Column C : shift times ie 1245-2124 Column D : job title ie floor Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
How can I pass data from a specific cell in one workbook to another worksheet using a hypertext link or command button. For example How would I be able to pass the following data from Workbook 1 R1C1 - Test Data to the same cell in Workbook 2.
I am calling a sub that I want to do a SaveCopyAs using a variable as the file name but can't get it to work. The file name displays properly in a message box but when I do a SaveCopyAs it does nothing.
I have code in a userform that shows another userform with a text box. When the user types text there and presses OK, I want that userform to close (which it does fine), and return back to the original userform (which it does fine), but I want the typed text to be stored in a variable that the code in the original userform can use.
I tried declaring a public variable, "Rresponse1", in each userform's procedure, but that doesn't work. I test for the content of the variable afterward in the original userform and it is empty.
In the original userform I have:
SaveList.Show '(this is the second userform) MsgBox "This is response1: " & Response1 '(to test if the variable is being passed)
In the userform SaveList, in the Private Sub CommandButton1_Click() procedure, I have:
Response1 = SaveList.ListName.Text Application.ScreenUpdating = True MsgBox Response1 '(to test the variable, here it shows the text fine) Unload SaveList
How do I get the Response1 variable to keep its content when control is passed back to the original userform?
Today's question concerns one sub calling another, having the called sub do some stuff that then effects what the calling sub does.
Code: public sub sub1
dim x as integer dim countCall as boolean
x = activesheet.cells(1,1).value countCall = False
call sub2(x, countCall)
Obviously the above is a very simplified version of what I'm trying to do, since everything there could of course be handled in a single sub. It is for illustration purposes only. My actual main sub needs to call the secondary sub repeatedly in some stacked loops and its in-feasible to but the second sub's code into the main thread.
The problem remains though, I know how to call a sub and pass variables to it, i just have no inkling on how to pass results back to the calling sub. I've tried a few things I've found around the web but I seem to be missing something important.
I have one worksheet with a macro which calls a macro in another worksheet. I would like to pass a variable from the first worksheet to the second worksheet.
Worksheet1 macro includes the following line of code to call Macro3 in worksheet2: Application.Run "'Worksheet2'!Macro3"
Worksheet2 has the following code in Macro3: Do While response 1 And response 2 response = InputBox("Would you like to print your report on one page or two pages?", "Select number of pages", 1)
[rest of code]
I would like to be able to pass a value of '1' to Macro3 and bypass the message box when Macro3 is called from Worksheet1. However, if the user has Worksheet2 open, macro3 should display the message box.
Is it possible to write a VBA macro that passes keystrokes to another program? I have a spreadsheet with over 650 UPC codes for new products that have to be entered into our inventory system. I have downloaded and tried a few macro recorders that record keystrokes and mouse movements, but find them to be unreliable at best.
When I use the macro recorder to automate a task in Excel, (such as moving data from one sheet to another or importing data from a text file) I always go in after I'm done recording and add additional code to bullet-proof the macro (make sure the focus is set properly, etc.). I need to have this kind of control for this task and a macro recorder doesn't give me that (at least not these lower-end shareware programs I have been trying). I know that there are some higher-end macro recorders that include scripting that you can write and edit, but this our "slow" season business-wise and my supervisor has made it clear that he doesn't want to spend any money right now.
My task is simple (just very repetitive, which is why I don't want to spend all day sitting in front of my PC doing this): 1. Open Excel spreadsheet, go to starting data cell 2. Open 3rd party application and go to the appropriate data entry screen 3. Write a macro that does the following: - checks to make sure the sheet is the active sheet - copies contents of the active cell - moves focus to other app and pastes data from active cell - moves focus back to spreadsheet, copies the contents of the cell to the right (using ActiveCell.Offset?) - makes other app active and pastes data - repeat copy/paste for the next cell to right (in spreadsheet) - press twice to save record before returning focus to the spreadsheet - move cursor down one line, repeat entire process until active cell is blank
Is this possible to do? The third party program is not able to listed as a reference in VBA, but I didn't know if it was possible to at least pass keystrokes to it. It is actually a terminal emulator (Anzio Lite). I guess the full-blown version of the program has some scripting capabilities (from the documentation I've read), but the Lite version does not.
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:
If I have cells that are formatted to a certain decimal width, say, 2 digits, cells that have the value of 3.599 will appear as 3.60. When I reference this cell in VBA, how can I have it pickup the 3.60? Currently, something like Range("A1") or Range("A1").Value will pickup the 3.599.
I feel like theres something thats like Range("A1").XXXX that will get me there, but I can't seem to guess it.
I am calling a XML Web Service within a function. The Web Service returns a XML grid of data. I need to populate cells in my worksheet with this data, but a UDF won't allow me to edit any other cells. I tried changing the function to a sub, but I need to pass parameters to the Web Service.