Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Excel VBA scripts on Office for MAC 2011

This may be a long shot but...I have a couple spreadsheet macros and thanks to the help of the fine folks on this forum they all work fine in Excel 2007 and 2010. My user received a MAC for christmas with Office for MAC 2011. None of my scripts work on his MAC. I did some research and VBA is supposed to be supported in that version.

So for starters, does anyone know some code I can add to the front end of the scripts that will determine whether it is running on a MAC or a PC so I can differentiate different segments of code for each? I would like it to work on both.

Secondly I suspect that at least two of my scripts do not work because of something to do with file/folder structure differences. Here is one of those scripts:

Sub ImportINV()
'
' ImportINV Macro
' Imports inventory from last month's file
'
'
  Dim sPath As String
  Dim fName As String
  Dim s As String
  Dim Wk1 As String
  Dim Wk2 As String
  Wk1 = ActiveWorkbook.Name
  s = CurDir
  sPath = ThisWorkbook.Path
  ChDir sPath
  MsgBox ("Please select the spreadsheet from the previous period that you want to import the data from.")
  fName = Application.GetOpenFilename( _
   Filefilter:="XLSM Files (*.XLSM),*.XLSM")
  ChDrive s
  ChDir s
  If LCase(fName) = "false" Then Exit Sub
    Workbooks.Open Filename:=fName
    Wk2 = ActiveWorkbook.Name
    Sheets("Sheet2").Select
    Range("L3:L524").Select
    Selection.Copy
    Windows(Wk1).Activate
    Sheets("Sheet2").Select
    Range("E3:E524").Select
    Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Windows(Wk2).Activate
    Sheets("Sheet2").Select
    Range("D3:D524").Select
    Selection.Copy
    Windows(Wk1).Activate
    Sheets("Sheet2").Select
    Range("D3:D524").Select
    Selection.PasteSpecial Paste:=xlValues
    Windows(Wk2).Activate
    Application.CutCopyMode = False
    ActiveWorkbook.Close SaveChanges:=False
End Sub
So all this does is allow you to select another spreadsheet and it copies some of the data from that spreadsheet to the current one.

Here is an additional problem for me, currently I have no access to a MAC with Office for MAC 2011 to use for testing to figure out how to fix this. Which also unfortunately means I do not know off hand where it is failing. I am working on getting something I can use for testing and development on this but since I am not really willing to put out money to buy my own copies of OS X and Office for MAC 2011 to resolve this issue that may not happen.

If by some miracle someone who does have said operating system and Office version looks at this and knows what's wrong, I would surely appreciate a version of this code that runs on MAC. I believe I could then modify at least one of the other scripts and get it working as well.

All help or suggestions appreciated.


Post your answer or comment

comments powered by Disqus
I have Excel for Mac 2011 and am having problems with a VBA macro I'm developing.

I have a 5-column table in a worksheet for which I have developed a macro to add new items to it and expand the table. This table has a name ("EmployeeList"). The macro extends the named range without having to define it as a dynamic named range.

After adding an item via the macro I want to copy the entire range to another sheet in the same workbook (by extending the macro to do it automatically). This sheet already has the 5-column table but will need to have its length extended to allow for new items. It also needs these additions to be done by inserting cells/rows (down) so as not to overwrite other tables under it. I thought of doing this by defining the target table with a dynamic range name but can't get DNRs to work in my version of Excel (via Insert->Name->Define, using the =OFFSET formula). Is there another VBA way to do this?

Hi Guys,

Just wondering if someone could help me ...

I had a macro that worked perfectly in Excel 2010 however have recently changed over to a MacBook Pro and the macro is coming up with an error when it is ran in Excel for Mac 2011.

The "Update Lists" button in the top right hand corner on the Master Database tab is what runs the macro.

Option Explicit

Sub ParseItems()
'Author:    Jerry Beaucaire
'Original Date:    11/11/2009
'Summary:    Based on selected column, data is filtered to individual sheets
'        Creates sheets and sorts sheets alphabetically in workbook
'        6/10/2010 - added check to abort if only one value in vCol
'        7/22/2000 - added ability to parse numeric values consistently
Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long, pCol As Long
Dim ws As Worksheet, MyArr As Variant, vTitles As String, Oops As Boolean

Application.ScreenUpdating = False

'Column to evaluate from, column A = 1, B = 2, etc.
   vCol = 1     'column A for division
   pCol = 15    'column O for status
   
'Sheet with data in it
   Set ws = Sheets("Master Database")

'Range where titles are across top of data, as string, data MUST
'have titles in this row, edit to suit your titles locale
    vTitles = "A9:Z9"
   
'Spot bottom row of data
   LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row

'Get a temporary list of unique values from column A
      ws.Columns(vCol).SpecialCells(xlConstants).AdvancedFilter _
        Action:=xlFilterCopy, CopyToRange:=ws.Range("EE1"), Unique:=True

'Sort the temporary list
    ws.Columns("EE:EE").Sort Key1:=ws.Range("EE2"), _
        Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

'Check for more than one value in list
    If ws.Range("EE" & Rows.Count).End(xlUp).Row > 2 Then

'Put list into an array for looping
'(values cannot be the result of formulas, must be constants)
        MyArr = Application.WorksheetFunction.Transpose(ws.Range("EE2:EE" _
            & Rows.Count).SpecialCells(xlCellTypeConstants))

'clear temporary worksheet list
        ws.Range("EE:EE").Clear

    Else
        ws.Range("EE:EE").Clear
        Oops = True
        GoTo ErrorExit
    End If
    
'Turn on the autofilter, one column only is all that is needed
    ws.Range(vTitles).AutoFilter

'Loop through list one value at a time
'In case values are numerical, we convert them to text with ""
    For Itm = 1 To UBound(MyArr)
        ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm) & ""
        ws.Range(vTitles).AutoFilter Field:=pCol, Criteria1:="<>Inactive"
    
        If Not Evaluate("=ISREF('" & MyArr(Itm) & "'!A1)") Then    'create sheet if needed
            Sheets("Regional Staff").Copy After:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = MyArr(Itm) & ""
        Else                                                      'clear sheet if it exists

        End If
        
        With Sheets(MyArr(Itm) & "")
            .Range("B10:N" & .Rows.Count).Clear
                
            If ws.Range("A" & Rows.Count).End(xlUp).Row > 10 Then
                ws.Range("B" & Range(vTitles).Row + 1 & ":N" & LR).Copy .Range("B"
& .Rows.Count).End(xlUp).Offset(1)
                
                .Range("B9:N12").Sort Key1:=.Range("B10"), Order1:=xlAscending, _
                                      Key2:=.Range("C10"), Order2:=xlAscending, _
                                      Header:=xlGuess, OrderCustom:=1, _
                                      MatchCase:=False, Orientation:=xlTopToBottom, _
                                      DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
            End If
            MyCount = MyCount + .Range("B" & .Rows.Count).End(xlUp).Row - Range(vTitles).Row
        End With
        
        ws.Range(vTitles).AutoFilter Field:=pCol
        ws.Range(vTitles).AutoFilter Field:=vCol
        
    Next Itm
    
'Cleanup
    ws.AutoFilterMode = False
    MsgBox "Rows with data: " & (LR - Range(vTitles).Row) & _
        vbLf & "Rows copied:     " & MyCount & _
        vbLf & "Inactive Rows:   " & Application.WorksheetFunction.CountIf(ws.Range("O:O"),
"Inactive") _
        & vbLf & vbLf & "Hope they match!!"

ErrorExit:
    If Oops Then MsgBox "Only one value found, aborting parse process..."
    Application.ScreenUpdating = True
End Sub


Hello,

I'm making an Excel/VBA insurance pricing tool for users with Excel 2003. However I've only got Excel 2002 (XP) and am having trouble finding useful information on what differences there are between 2002 and 2003. Particularly with respect to VBA and the Excel object model. I'm guessing there isn't much, but would appreciate any pointers.

I could get Excel 2003 if necessary, but would rather avoid the hassle if possible. I've got a new pc coming with Office 2007, but that's a whole 'nother thing.

A little background: I've done a lot of advanced Excel/VBA development, up until about 6 years ago. Then none. Now (perhaps foolishly) I've agreed to write this new app. It will use the workbook only for input, output and storage. No Excel calcs, charts etc. All calcs and data manipulation will via VBA, with little or no use of the native Excel data tools (pivot tables, etc). Navigation and VBA invocation will be by dynamically created hyperlinks, i.e. no buttons or custom menus etc.

Sorry for the long posting. In summary I would like to know if I'll have any problems using Excel 2002 to write an app for use in 2003. And/or if I'll be missing out on some useful 'new' features in 2003.

Any pointers greatly appreciated.

Cheers

Malcolm

execute external VBA script on a workbook

I would like to "kill" the merge cell before the exportation in Oracle database whit my program in DELPHI

Construct like

http://delphi.about.com/od/database/l/aa090903c.htm

Whit some upgrade.

so I what to execute the script :

http://www.mrexcel.com/board2/viewto...538&highlight=

I don't want any manual intervention this step must be translucency.

I need an Excel VBA script updated from Excel 5 or re-written to take texted
files from Quicken and produce reports in a specific format. I have not been
able to find such a person. We are located in the San Joaquin Valley south
of Sacramento, CA. Email or reply to this message if interested. Thank you!
--
Regards,
Louis G. Lemos, CPA

Hi guys,
I've scoured the web and suggestions are to modify the status bar, or to insert a form.

But I have Excel for Mac. "StatusBar =" doesn't generate an error, but doesn't change the status bar.

And I can insert a form, but the required properties don't seem available, so I think all the posts I'm finding are for VBA in Windows.

Could anyone point me to a routine that would work in excel for mac? (Ideally a progress indicator bar, but I'll take modifying the statusbar too).
Thanks :-)
MW

I wonder if anyone can help clarify my confusion about .NET. I have fairly large system based on Excel VBA which I've converted to an add-in (with a small amount of VBA still residing in the worksheet).

I'm using (company policy) Excel 2003 but may need to upgrade, perhaps to MS Office 2010 fairly soon. My questions are:

1. Will my existing VBA add-in still work - either "as-is" or with some changes ? and will the VBA that resides in the spreadsheet still work ?
2. Will I be forced to convert to .NET and if so does this involve purchasing any products in addition to Office 2010.
3. Even if I don't have to migrate, are there any benefit in doing so ? Such as being able to run the code on Office for Apple Mac ?

Thanks for your assistance.

Hi there,

i have a little problem with the performance of my script.

I have lots of excel workbooks, one of which is a list that extracts data from all the other files. Now the problem is, that all those files (and the list) are on a computer in the network. If I now open the list and update it, it accesses all the other files from my local computer over the network, what slows everything down.

Is there some way i can make the remote computer run the vba script instead of running it from my local computer? Ideally this event is triggered by my computer, so i can control when the script is run. For example a button in the list "update", that then makes the remote computer update the list.

Any help would be appreciated..

Thank you very much

Here's a link to a MacWorld article regarding Office for Mac 2008 (due out later this year) dropping support of VBA. However, VBA code can be rewritten in AppleScript and a new guide to help users make the transition will become available in April.

http://www.macworld.com/weblogs/edit...uide/index.php

Hope this helps,
theDude

Hi there, I've been reading about the option of viewing two workbooks side-by-side and enabling a 'Synchronous Scrolling' function. But I can't find it any where in my version of Excel for Mac. Maybe it only exists on the regular Windows versions. Any insight would be great.. thank you

Hello,
I am using Excel 2011 (Office for Macs 2011). I have 86 bar charts (the data has to be represented as a bar chart), all of which have been log-transformed on the y-axis.
The x-axis of each of these charts is different - some range from 1 to 100, while others range from 1 to 1500. I am trying to make the maximum number on the x-axis the same on all of the charts (ie. I want the maximum number to be 1500 on all x-axes). This way comparison between the charts is easier. Is there a way to change the maximum value on the x-axis of a bar chart in excel? I dont mind going through each of the 86 charts one by one changing them manually if there is a way to do it.

When I try to plot the data as a different chart type (eg. scatter plot), I can change the max value of the x-axis. But as stated earlier, the data must be in a bar chart format. And in the bar chart format, there is no option to change the max value. Does anyone have any tricks they wish to share?
Any and all help is greatly appreciated.
Thanks,
L

Hi guys,
I suddendly facing a problem I was not expecting. I did many excel files on a PC using Office 2003. Most of the people who should use these files have mac ibook G4 or powerbook G4 with office for mac 2004.
I have send by email the files I have done and when trying to open it on a mac, it first convert all the image as metafiles, which takes a lot of time. And then most of my macro are link to buttons or images. Images do run the macros but not buttons. Then most of my macro linked to the buttons or images should display forms.However, When I want to display a form, I get the following error message:
"Run-time error '32809'
Application-defined or object-defined error
Moreover, In some of my macros I have some piece of codes like

	VB:
	
Sheets("Sheet1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It show me the same error mesage.

I´m really stun to get this error since the files work perfectly on my PC.
I think MAc is creating problems because of the converting when I open the workbook on MAC.

Do you have any ideas?
Please, I need help.
All these files repreasent several weeks of work. I would like to avoid creating them again on a MAC.
Thanks
Face2Face

Hello,

I am attempting to automate a file in excel using vba which will send ranegs of a file as attachment in Lotus notes. I have found a Lotus notes vba script on this forum and have tried to modify the code but I became very troubled with my progress.
The file is attached and on control tab there is a box with recpients and, subject and parts of the file I want to send. The problem I have is with recipeints.
The code aim is to start at the left with file name and then pick up the `To1` column, `TO2` column subject and attach the range.
How can I modify the code to loop across to pick up the fields. Originally I could hard code but told to use this method which I am having trouble. Apologies for my poor english and I hope the attached file can make more sense.
Thank you kindry
NG

Hi there,

I have recently started a new job and trying to do some extra work to get ahead. I have taken on some spreadsheet development and have been learning some basic macro and VBA details. I have just completed my first Userform spreadsheet for my co-workers to enter details onto a spreadsheet in a nice uniform manner. However my next project is a bit beyond what i have been able to teach myself.

I am trying to create a table for my co-workers to record day they are able to do overtime.

This will have the date along the top a row, and their names in a column. What i would like is for a user box in which they would enter their name and date. At which point the VBA script would search the column for their name and then the row for the date and in the cell where they intersect insert a text (ie Overtime).

I have managed to find this bit of code on a forum that will search a column and allow me to put in available or not available for overtime now I just need to add in the bit where it searches across for the date as well.

Code:

Sub Find_UserID() 
Dim ThisCell As Range 
Dim LookupValue As String 
Dim SearchRange As Range 
    Set SearchRange = Sheets("Sheet1").Range("A1:A" &
Sheets("Sheet1").Range("A65536").End(xlUp).Row) 
    LookupValue = Application.InputBox("What is the name of the person requesting Overtime?", "Person
Lookup", , , , , , 2) 
    For Each ThisCell In SearchRange 
        If UCase(ThisCell.Value) = UCase(LookupValue) Then 
            Sheets("Sheet1").Range("B" & ThisCell.Row).Value = Application.InputBox("Avliable or
Not?", "Enter text") 
            Exit Sub 
        End If 
    Next ThisCell 
    MsgBox LookupValue & " was not found!", vbOKOnly + vbCritical, LookupValue & " Not Found" 
End Sub


Any help in explaining how i can get it to search the row before submitting the application.inputbox value would be greatly appreciated..

I recently ran into an issue while trying to group data by date in a pivot table; the error I get from excel when I right click on a date in the date column and select group is "Cannot group that selection." I did some searching on Google and found that you can't group empty cells, of which my pivot table has many.

This is a pivot table for my personal finances: I have 49 spending categories that I'd like to summarize by month/year or quarter/year. The pivot table is generated from a spreadsheet that resembles the following:

| Date | Funds Out | Funds In | Category |
| 01/01/2011 | $2.20 | | Dining:Coffee |

Obviously there are going to be many, many blank cells in the pivot table because not every category will have a transaction on the same day as every other category. Further still, many expense-type transactions are not likely to have many, if any, "Funds In" entries (but those will appear when I've returned an item for refund, for example.) Likewise, income-type transactions aren't likely to have any "Funds Out" entries. I could simplify things a little by combining "Funds Out" and "Funds In" and just using -/+ numbers in a single column, but that still doesn't solve the issue with the categories (in fact, I've tried creating a pivot table leaving out the "Funds In" column.)

Is it totally impossible to create pivot table groups when there are empty cells? As far as I'm concerned, those cells could be "$0.00" when the grouping calculations (sum) are done. I've changed the table options to show 0 for empty cells, but I think that's just a display option and doesn't reflect in the underlying blank cell, so doing that still doesn't let me group the data.

This must be a common type of report for any business (or individual) - a summary of expense by category per month or quarter. How do I deal with the empty cells? I could probably create a pivot table per category, but that would be labour intensive and seems unnecessary. Why can't excel just treat empty cells as $0.00?

I'm using Excel as part of Office for Mac 2008, so VBA macros aren't available. I'm considering buying the latest edition of Office for Mac so if you know that this isn't an issue with the latest version that'd be more incentive for me to update.

Hi Exp

i would like to Load Excel VBA Macros, in ordre for them to work with IBM Host on Demand. I work white Excel 2007; and need to transfer data in to Host on Demand. Is the a, standard way to do so ?

Ore do some of you know of Macros that all read exist ?

Hi Guys,

I am using office for mac 2011 on my MBP and would like to know how to do the following.

I would like to save a defined print area on a sheet of my workbook to the desktop of my mac and have it named from a cell on the sheet.

I am really struggling to event get started.

I have tried to create a macro to do what i want but it creates a PDF of all the sheets not just the one i want. Also i do not know how to name it from a cell value without it putting the sheet name after it.

Any help on this would be amazing.

Thanks in advance.

Here is my code

	VB:
	
 SAVE1() 
     '
     ' SAVE1 Macro
     '
     
     
     '
    Application.Goto Reference:="week" 
    Selection.Copy 
    Application.Goto Reference:="Print_Area" 
    Application.CutCopyMode = False 
    Range("A1:U38").Select 
    ActiveWorkbook.SaveAs Filename:= _ 
    "Macintosh HD:Users:adamhewitt:Desktop:Week 26     .pdf", FileFormat:=xlPDF, _ 
    PublishOption:=xlSelection 
    ActiveWorkbook.Sheets("Time Table").Select 
    ActiveSheet.Range("a1").Select 
     
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Adam

I've been playing around with the API functions (specifically, InternetOpenURL) to retrieve the contents of an HTTP webpage and store the information within a text file, using Excel VBA. It works great for any "normal" webpage not requiring a "Username" and "Password" login. I can't find any examples on how this is done for a webpage requiring such a login? I've heard you can do this via sending the appropriate cookie to the server, but I can't find any documentation on the web? Does anyone have any idea on how this functionality is achieved? Any examples?

With WINDOWS 2000 O/S, is it possible to devote system memory to Excel & allow the VBA script (which handles intense formatting) to run in the background?
What VBA commands can be used to do this?

Right now, the VBA script will only run when the Excel application is active.
I need to be able to use different applications, while Excel continues processing.
Thanks.

Hello, I'm a complete novice when it comes to using Excel VBA and am
looking for help and advice please.

I've been given the job of porting some software over from OS2 onto
Windows - this software is used to build code for the companys
systems and it includes a "screen builder" which allows users to
design screens and this also creates the necessary code which generates
the screens when run.

Assuming I can't get the actual screen builder source code to work on
Windows (still looking at this) then I will need a replacement screen
builder and I was wondering if Excel might be able to do it. I've
mocked up a simple screen which looks very similar to the system we use
but obviously it doesn't generate the source code, but the question
is can Excel do this either by using a Macro or Visual Basic?

I'd need some VB which could look at the Excel worksheet/template and
see what has been added and then using that generate some source code
and output it to an IDE, notepad would do but VIM would be better.

I've seen various tutorials for Excel VBA on the net but none of them
are really appropriate for what I want to do - is it possible? The
alternative would be for me to write a screen builder from scratch
which I don't really want to do!

Thanks.

David

Sorry if the answer to this is out there, but I've searched a bunch and haven't hit on the right topic.

I have a spreadsheet that only has dates in it. It's a huge version of this:

5/24/2004
5/26/2004
6/1/2004
6/10/2004
6/10/2004
7/5/2004
7/6/2004

In reality there are many entries (some on the same day, some not) for each day from 5/1/2004 to now.

I'm trying to find a way to make that usable by grouping and counting each entry per month, either by a list that says:

May 2004 2
June 2004 3
July 2004 2

or as a chart. I sure can't figure out a way to do either, so I'm hoping someone here can help out. I'm using Office for Mac 2011, but I can use Excel 2003 - 2010 on the PC side if I need to.

Thanks,
Gabe

I've got a large CSV exported from an Outlook address book. Of chief concern are over 2000 contact entries with multiple phone numbers for each entry. The entries have phone numbers from all over the globe, but mostly from the US.

The formatting of the phone numbers varies. Some lead with "+1" and some don't. Some have parenthesis and others don't. Some are broken into the usual segmentation of (###) ###-####, some are just strings of ##########, some use periods to break-up their segments, others use hypens and others just use spaces. Some are appended with commas and extensions. I need to get all the numbers - at least all the numbers without extensions - into a unified format of [+1 (###) ###-####].

I have succeeded in removing all spaces, hyphens, periods, "+1"s and parenthesis to bring them down to simple strings of numbers. However, I'm having difficulty using the "Format Cell" utility to reformat them into "+1 (###) ###-####."

Since I'm using Office for Mac 2011 most downloadable macros are of no use as they're not compatible.

Any suggestions?

This is extremely time-sensitive. If you're awake tonight and can offer a suggestion, that would be fantastic.

Hi All,

I need an excel vba code to use for updating an access database file.

File Name> C:TEST.mdb
Table Name> Spec (It includes 5 columns and 400 rows)

I wrote a code but need the critical part.


	VB:
	
 DataExcelToAccess() 
     
     
    Dim con As ADODB.Connection, rs As ADODB.Recordset 
     
    Set con = New ADODB.Connection 
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=C:TEST.mdb;" 
    Set rs = New ADODB.Recordset 
    rs.Open "Spec", con, adOpenKeyset, adLockOptimistic, adCmdTable 
     
     
    ....... 
     
     
     'I need a code here that take the value from Range("A1") and export it to
     'access table "Spec" to row 52 and column 4 (column 4's name is "value")
     
    ....... 
     
     
    rs.Close 
    Set rs = Nothing 
    cn.Close 
    Set cn = Nothing 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for your attention.

Tres preguntas:
1) ¿Dónde puedo conseguir un Manual o Documentación sobre Excel VBA en castellano?
2)¿ Me aconsejáis alguno en concreto ?
3) J.Pablo: quisiera adquirir el producto "1600 Excel VBA solutions on CD" ¿algún problema para hacerlo desde españa?
Muchas gracias por todo.


No luck finding an answer? You could always try Google.