how do you set a return value for a function in VBA? I've looked at examples of functions and I don't understand.
All I want to do is call a function to see if a workbook is open and have it return true or false. Here is the code I have, but it won't let me use go "Return isOpen" at the end like VB would! The IDE complains.
Function IsWorkBookOpen(ByVal name As String) As Boolean
Dim wBook As Workbook
Dim isOpen As Boolean
wBook = Workbooks(name)
If wBook Is Nothing Then
isOpen = False
Else
isOpen = True
End If
'I can't reutrn the value of is open?
Return isOpen
End Function
I've noticed other VBA functions I've looked at don't use "Return"....how does does the function return a value then?
If I have a multiple workbooks open and I want to determine if a workbook is open for example "Test 1.xls", how would I do that. I want it to msgbox once to say "open" if open or msgbox "not open" if not open.
I cannot cut and paste from my working computer to the internet access computer.
This code is intended to check for a workbook already open and if it contains a particular worksheet. This function is called from a routine that reads a master workbook then opens a PCM workbook. The PCM workbook might already be open.
WB_PCM is declared as a workbook and the name is set. WS_PARAMETERS is declared as a string and its name is set to an existing worksheet. The workbook and worksheet do exist, but are not open.
Code: Public Function Is_Workbook_Open( workbook_ref As Workbook, sheet_name As String) as Boolean Dim check_sheet as Worksheet On Error Resume Next If( workbook_ref Is Nothing ) Then Is_Workbook_Open = False Else Set check_sheet = workbook_ref.Worksheets( sheet_name ) ' < error
[code].....
During this time the master workbook is open but either not in focus or is minimized within the Excel windows. If I select that worksheet and select a cell in it, then this code runs. What should I do different to get this code to check for WB_PCM being open and being able to handle it when either open or not open.
I was wondering how i can code a if then loop to determine if a workbook by the name i specify is opened.
If Windows(vCriteria & "ISPR.XLS"). = True Then MsgBox "true", vbCritical, "Ferguson Enterprises, Inc." Else MsgBox "false", vbCritical, "Ferguson Enterprises, Inc." End If
thats what i got.... not sure if that will work... i'm missing the definition for: If Windows(vCriteria & "ISPR.XLS"). i keep wanting to type isopened, but alas it is not avaliable in the list. There seems to be no extention avaliable that appears to work with such a function. Is it possible to do what i am trying to do?
Split from Macro To Insert Rows In Protected Worksheet. I have found this code wgich appears to be very close to what i want. Just to re-iterate -
A) i want to detect if any other user has the shared book open, if yes-display msg and exit - it seems to do this by just adding an appropriate msg box
B) if no - open it and lock other users out by temporarily remving share or allowing them in but as read only - not sure if it does that.
Also not sure if it works for a shared file - cannot test as i do not have access to shared network from location.
Function IsFileOpened(StrFilePath As String) As Integer Dim FileNum As Integer 'First check filepath exists If Len(Dir(StrFilePath)) > 0 Then FileNum = FreeFile() On Error Resume Next Open StrFilePath For Input Lock Read As #FileNum ' Open file and lock it. If Err.Number <> 0 Then IsFileOpened = 1 'File open Else IsFileOpened = 0 'File Closed End If Close FileNum Else IsFileOpened = 2 'File not found End If End Function
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook Copy range (c8,d69) Close work sheet Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook
open macro-
Private Sub Workbook_Open() Dim dTime As Date dTime = Time If dTime >= TimeValue("9:30 PM") And _ dTime < TimeValue("9:40 PM") Then ImportData End If End Sub
This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?
I have a userform which prompts a user to select two files from the directory. Once these two files are open I will extract data from one into the other, the only problem is these filenames will change and I need to be able to determine what their names are.
I have this code below:
Private Sub CommandButton1_Click() FiletoOpen = Application.GetOpenFilename _ (Title:="Please choose a file", _ FileFilter:="Excel Files *.xls (*.xls),")
If FiletoOpen = False Then MsgBox "No file specified." Exit Sub Else Filename1 = FiletoOpen TextBox1.Value = Filename1 End If
End Sub
If I could somehow extract the exact filename, not full path, from the variables Filename1 and Filename2 I would be fine I guess, I just don't know how to do that.
I have a presentation that I open from a short-cut. After the "Welcome Page" is opened, I want to open a second workbook in a new instance of Excel after 4 seconds.
I think that I can open the new instance of excel, but I don't know how to activate the macro after 4 seconds.
I'm sure there is a function someplace for this that can be used in a macro.
Then, after the second Workbook is opened, I want the Welcome Page "Workbook" closed, leaving the second Worbook open.
I would like to choose an option (C, Q, T) and get a result in 1 column.
In other words, if I type C in H6, I must get a different result in I6 as opposed to if I choose Q or T in the same column. However if I leave it blank I should have nothing or a 0 in I6.
In the attachment above you will see that I gave a sum for C (A3), Q (A4) and T (A5). The sum for each one is broken down into a percentage rate and a flat rate.
These rates are dependent on the initial amount entered into G6. C, Q, T stand for Cash, Cheques, Transfers. The form of transaction determines the % and flate rate to be charged.
My Splash screen opens a few seconds after the workbook has loaded. Is there a way to make the workbook open minimzed until the the splash screen closes then open properly? What I trying to say is that only the splash screen is visible until it closes.
I have a variety of different order forms in workbooks titled IT1314-0001, IT1314-0002, IT1314-0003...etc... They're all stored in the same location, but they need to be on different workbooks as each workbook is sent as an order form to a different organization, for that organization to fill in. All of the order forms have the same layout, but the user will input different information onto them.
In a summary workbook of all of these Order Forms, I need to be able to drag down an INDEX formula, such that the INDEX looks in a different workbook (the next workbook in the sequence) with each row. As you go down the rows, it should look like;
INDEX(‘[IT1314-0001.xlsPARTS A & B!$D$5:$E$5, 1, 1) INDEX(‘[IT1314-0002.xlsPARTS A & B!$D$5:$E$5, 1, 1) INDEX(‘[IT1314-0003.xlsPARTS A & B!$D$5:$E$5, 1, 1) INDEX(‘[IT1314-0004.xlsPARTS A & B!$D$5:$E$5, 1, 1)
I would like to write a Sub that will see if a workbook is open and if it is not then open it. I know how to have a macro automatically open a workbook, but I run into problems when the macro runs and tries to open an already opened workbook.
Is it possible to write code that can identify how a workbook has been opened i.e. has the workbook been opened by another workbook or has it received a double click event on the workbook itself.
If it is possible I would like to place the code in the open event of the workbook that is being opened.
Trying to determine who has a file open, the code below works for simple files. However if the workbook contains macros and user forms it seems there are multiple occurances of the search terms strFlag1, strFlag2. How can I determine the last user
Function LastUser(path As String) Dim text As String Dim strFlag1 As String, strflag2 As String Dim i As Integer, j As Integer strFlag1 = Chr(0) & Chr(0) strflag2 = Chr(32) & Chr(32) Open path For Binary As #1 text = Space(LOF(1)) Get 1, , text Close #1 j = InStr(1, text, strflag2) i = InStrRev(text, strFlag1, j) + Len(strFlag1) LastUser = Mid(text, i, j - i) End Function
Create a full copy of an open workbook (eg. activeworkbook MyFile.xls) using VBA, with the new copy (eg Book1.xls) open as well ,without having to save a copy first then open it ?
I did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet() If ActiveSheet.Index <> Sheets.Count Then Application.DisplayAlerts = False Set ws = ActiveSheet Sheets(ws.Index + 1).Delete ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2") 'Moves active sheet to beginning of named workbook. 'Replace Test.xls with the full name of the target workbook you want. Application.DisplayAlerts = True End If End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
I have the following formula (taken from the web) in my spreadsheet which let me know if a cell have bold text or not. But when the cell has 'mingled' text, i.e. only partly bold, he gave a #VALUE error.
I am looking for a macro that will run on worksheet activation. it needs to run only if the file had been saved from the previous week. would something like this work?
Private Sub Worksheet_Activate() If application.worksheet.lastsaved = msolastweek Then range("RANGE").clearcontents End If End Sub
I have a workbook with many lookups, sumproducts, dynamic named ranges and cse formulas. How do I determine what is causing my workbook to be slow? Are there more efficient formula types that I can use?
I have written a few macros, which use date type variables. Because VBA uses the 1900 date system, and some workbooks use the 1904 date system, I have to first check and see if a workbook uses the 1904 date system, and if so, subtract off 1462 days where appropriate. My macros used to check the date system by using the command:
If ( ActiveWorkbook.Date1904 = True) Then nh_stringToDate = nh_stringToDate - 1462 End If
But, sometimes the macro resides in one workbook (call it macro.xls) but the currently active workbook is some other workbook (something.xls). What I really care about is the date system of macro.xls and NOT something.xls. So, instead of "ActiveWorkbook" is there some other object I can use to refer to the workbook in which the macro itself resides? I don't want to rely on using the name of the macro workbook (macro.xls) because this could change! So I need a way to simply refer to the workbook in which the macro resides.
I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
