Macro to Calculate Efficiency of Spend

This macro was used in a spreadsheet to calculate the efficiency of marketing spend across many different channels. You can add easily adapt this macro to work for your own particular calculations.
' EfficiencyOfSpend Macro
' Pending the type of metric (e.g., CPI, visits), this function will calculate whether or not you are on or off target.
'
' Keyboard Shortcut: Ctrl+Shift+I
'

 
If KpiType = "CPI" Then
 
    'If Target = 0 Then EfficiencyOfSpend = 0

    If Target > ActualValue Then
    EfficiencyOfSpend = (Target - ActualValue) / Target
 
 
    ElseIf Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
    EfficiencyOfSpend = -1 * EfficiencyOfSpend
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
 
    End If
End If
 
If KpiType = "Visits" Then
    MsgBox "clickvisitetc"
    If Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target > ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
    End If
End If
 
If KpiType = "Impressions" Then
    MsgBox "clickvisitetc"
    If Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target > ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
    End If
End If
 
If KpiType = "Clicks" Then
    MsgBox "clickvisitetc"
    If Target < ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target > ActualValue Then
    EfficiencyOfSpend = (ActualValue - Target) / Target
 
    ElseIf Target = ActualValue Then
    EfficiencyOfSpend = 1
    End If
End If
 
 
 
End Function
Posted in VB

Highlight Rows in a Range that Meet Some Criteria

Option Explicit
Sub highlight()
 
' SelectValues Macro
' Selects values that will become criteria for shading rows
'
' Keyboard Shortcut: Ctrl+Shift+U
'
    'First, read in the values for shading
    Dim l As Double
    Dim m As Double
 
    l = Sheets("Criteria").Range("B2").Value
    m = Sheets("Criteria").Range("B3").Value
 
   'Set up variables that you'll need for below code
   Dim c As Range
   Dim r As Range
   Dim n As Double
 
   'Input Range to be evaluated
   Set r = Application.InputBox(prompt:="Input your range", Type:=8)
 
   'On error in below loop, just keep going
   On Error Resume Next
 
   'loop through all the values in the range
   For Each c In r
 
      'convert to number if value happens to be text
      c = Val(c)
 
      'evaluates and colors rows based on your criteria spreadsheet
      If c > l And c < 0 Then c.EntireRow.Interior.ColorIndex = 6
      If c < l And c > m Then c.EntireRow.Interior.ColorIndex = 44
      If c < m Then c.EntireRow.Interior.ColorIndex = 3
 
   Next c
 
End Sub
Posted in VB

Count Unique Objects in a Range

Sub UniqueReport()
 
Application.ScreenUpdating = True
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
Dim varray As Variant, element As Variant
 
varray = Application.InputBox(prompt:="Input your range", Type:=8)
 
'Generate unique list and count
For Each element In varray
    If dict.exists(element) Then
        dict.Item(element) = dict.Item(element) + 1
    Else
        dict.Add element, 1
    End If
Next
 
'Paste report somewhere
Sheet2.Range("A1").Resize(dict.Count, 1).Value = _
    WorksheetFunction.Transpose(dict.keys)
Sheet2.Range("B1").Resize(dict.Count, 1).Value = _
    WorksheetFunction.Transpose(dict.items)
 
Application.ScreenUpdating = True
End Sub
Posted in VB

C++ String Class

Screen print of code
This is a simple string class that I constructed as an assignment for one of my classes. Most programmers would just use C++'s built in string preprocessor, but it's a good exercise because in some cases you may need to write your own to serve your specific needs.
#include 
using namespace std;
 
class String {
private:
    char* strval;     //holds string value
    int strln;           //holds length of string value
public:
    //constructors
    String();           //initializes default value of null string
    String(char* s);   //initiializes string to value of cstring s
 
 //destructor
 ~String();   
 
    //access functions
    bool operator==(const String s);   //true if value equal to s, else false
    bool operator(const String s);   //true if value greater than s, else false
    int length();                      //returns length of string value
/*    String operator+(String s); */    //returns concatenation of s to string value
 
    //modifier functions
    void tolowercase();
 
    //input/output functions
    //reads in string value from istream
    friend istream&amp; operator&gt;&gt;(istream &amp;strm, String &amp;strng);
    //outputs string value to ostream
    friend ostream&amp; operator&lt;(String s)    //true if value greater than s, else false
{
    int shorter_len = ((strlns.strval[i]))
        return_value = true;
    else
        if ((i==s.strln) &amp;&amp; (strln&gt;s.strln))
            return_value = true;
    return return_value;
}
 
int String::length()
 {return strln;}
 
istream&amp; operator&gt;&gt;(istream&amp; strm, String&amp; strng)
{
 char* temp = new(char[101]);
    strm &gt;&gt; temp;
    for (strng.strln=0; strng.strln &lt; strlen(temp); strng.strln++)
 {
        *(strng.strval+strng.strln) = *(temp+strng.strln);
 }
    return strm;
 delete [] temp;
}
 
ostream&amp; operator&lt;&gt; s1;
 
}
Posted in C++