lundi 20 avril 2015

VBA written in Excel for Windows not working on Mac

I have a set of macros to hide and unhide columns based on the contents of a specific row. They were all written in Excel 2013 for Windows (running in parallels on my MBA, if that's relevant) and work fine there. But when I open the worksheet in Excel 2011 for Mac, the macros give odd results. The "unhide all columns" macro works fine; the other functions get as far as hiding all columns but not as far as unhiding the ones I want to see.

I can only assume Excel for Mac is having a problem with what's in the FOR EACH loop, but I can't figure out what! I'd appreciate any guidance: I need to get this system working on both Windows and Mac.

Code below.

This function works:

Sub GANTT_Filter_Show_All()

Dim rngDates As Range

Set rngDates = Range("GANTT_Dates")

rngDates.EntireColumn.Hidden = False

End Sub

But this one only hides all the columns:

Sub GANTT_Filter_This_Quarter()

Dim intCurrentMonth As Integer, intCurrentYear As Integer, rngDates As Range, cell As Range
Dim intCurrentQuarterMonths(3) As Integer

Set rngDates = Range("GANTT_Dates")
intCurrentMonth = DatePart("m", Date)
intCurrentYear = DatePart("yyyy", Date)

'loading months of current quarter into an array intCurrentMonth

Select Case intCurrentMonth
    Case 1 To 3
        intCurrentQuarterMonths(0) = 1
        intCurrentQuarterMonths(1) = 2
        intCurrentQuarterMonths(2) = 3
    Case 4 To 6
        intCurrentQuarterMonths(0) = 4
        intCurrentQuarterMonths(1) = 5
        intCurrentQuarterMonths(2) = 6
    Case 7 To 9
        intCurrentQuarterMonths(0) = 7
        intCurrentQuarterMonths(1) = 8
        intCurrentQuarterMonths(2) = 9
    Case 10 To 12
        intCurrentQuarterMonths(0) = 10
        intCurrentQuarterMonths(1) = 11
        intCurrentQuarterMonths(2) = 12
    End Select

'hiding all columns

rngDates.EntireColumn.Hidden = True

'comparing each column to array of months in current quarter and hiding if false

For Each cell In rngDates
    For Each v In intCurrentQuarterMonths
        If v = DatePart("m", cell.Value) And DatePart("yyyy", cell.Value) = intCurrentYear Then cell.EntireColumn.Hidden = False
    Next v
Next cell

Application.Goto Reference:=Range("a1"), Scroll:=True

End Sub

How to press the second button with VBA Excel Internet Explorer

I am trying to press a button with ID "run-report" the issue is that there is two buttons with the same ID and I want to press the second button.

Set tags = ie.Document.GetElementsByTagname("input") 
For Each tagx 
In tags If tagx.Value = "Run report" 
Then tagx.Click 
End If 
Next

This works for the first button but can not find a way to press the second one. Anyone got any tips?

Thanks in advance

Print Date and time in fixed column instead offset for Log of events

Gentlemen, I´d like to create a log of events. With a form I´ll fill cells on the same row starting in column C and I´d like date and time printed automatically in columns A and B. Sometimes information might start from column D and C would be empty. The code below does that through an offset from column C and C only. Even if I expand the range for Intersect, the date and time will be carried out and not stay fixed in A and B. Does anyone would have a hint for me?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rChange As Range

    On Error GoTo ErrHandler
    Set rChange = Intersect(Target, Range("C:C"))
    If Not rChange Is Nothing Then
        Application.EnableEvents = False
        For Each rCell In rChange
            If rCell > "" Then
                With rCell.Offset(0, -2)
                    .Value = Date
                    .NumberFormat = "[$-2C09]ddd, DD/MM/YYYY"
                    .HorizontalAlignment = xlLeft
                    .EntireColumn.AutoFit
                End With
                With rCell.Offset(0, -1)
                    .Value = Time
                    .NumberFormat = "hh:mm"
                    .HorizontalAlignment = xlCenter
                    .EntireColumn.AutoFit
                End With
            Else
                rCell.Offset(0, -1).Clear
                rCell.Offset(0, -2).Clear
            End If
        Next
    End If

ExitHandler:
    Set rCell = Nothing
    Set rChange = Nothing
    Application.EnableEvents = True
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

VBA Code to shift sourcedata in an excel chart

I am trying to update my sourcedata in a chart. I Don't want a dynamic range because the chart will pull each time from the new range with each button click. If I use a dynamic or named range, I am locked into that range.

I have tried:

ActiveSheet.ChartObjects("Chart 2").Activate

ActiveChart.PlotArea.Select

ActiveChart.SetSourceData Source:=Range("A1:C3").Offset(0,3)

This only works once, though. To be clear, I have a button to update a chart on a spreadsheet. When the button is clicked, the macro will look at the current data in the active chart and shift the source over 3 columns. This will happen each time the button is clicked and the data must stay on the sheet. I've exhausted everything I know and can find online the Goracle search.

Any help?

Thanks in advance, Lilith

vba create a new sheet and change name

I am a VBA new user. My purpose is copy a existing worksheet to a new one. And then change the new worksheet name to a proper name. However, I got the VBA Error Message like,

vba runtime error 9 subscript out of range(at the line Set new_ws =Active...)

The VBA code will be the following; thanks in advance

Sub CreateWS()
Dim ws As Worksheet
Dim new_ws As Worksheet

    Set ws = ActiveWorkbook.Worksheets("Bus Voltage")
    ws.Name = "Bus Voltage_All"
    ws.Copy Worksheets(Sheets.Count)
    Set new_ws = ActiveWorkbook.Worksheets("Bus Voltage_All(2)")
    new_ws.Name = "Bus Voltage"
End Sub

How to record mouse clicks in Excel VBA?

I am trying to make a macro that records what a user clicked, which then records the mouse coordinates and the delay between the clicks. This will then repeat after some other SendKey changes. How can I detect when I click the mouse when the macro is running? I already know how to get the coordinates and record the delay, but what is the best course of action for detecting the mouse click and also what would be the best way to save all this information? A text file? Here is a snippet of the mouse click events that I use:

Public Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
Public Type POINTAPI
x As Long
y As Long
End Type
Public pos As POINTAPI ' Declare variable

Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10

Public Sub SingleClick()
Dim xval, yval
xval = GetSetting("Will's Program Sheet", "DPS Calibration", "PROGRAM X")
yval = GetSetting("Will's Program Sheet", "DPS Calibration", "PROGRAM Y")
Select Case xval
Case Is = "" 'Runs calibrate if it can't find an xval
    Call CALIBRATE
    End
End Select
  SetCursorPos xval, yval  'x and y position
  mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub

There is another macro that calls SingleClick where it moves to a constant x and y, clicks, does some magic, and returns to the position before the macro started. So to reiterate, is there a simple or easy to understand method to record multiple clicks and delays between clicks and replay them through Excel VBA?

Compare two sets of name lists and create a report showing matching names in VBA

I have two sets of lists: 1) customer list in sheet1: name, last name, DOB 2) black list 1 in sheet2: name last name, DOB 2) black list 2 in sheet3: name last name, DOB 2) black list 3 in sheet4: name last name, DOB

I need a VBA code to check the customer list and identify the ones that appear in one or more of the blacklists. Return the results in a seperate sheet showing the following info:

  1. Customer name, last name, DOB
  2. Which blacklists does the customer appears to be in?
  3. Matching results from the blacklists

I would greatly appreciate any input that can help me generate a VBA code for this task. Thanks!