How do I get the year from a date in VBA?

See also questions close to this topic

• .Body & .HTMLBody Indent and Default Signature Issue

  I've set up a macro to send an e-mail through Outlook.

  .Body is read from a cell inside the file with indents. Since the value will change depending on the usage, I need to reference that cell for the body.

  However, there rises 2 issues using .HTMLbody I lose indents which are constructed with CHAR(10) but I keep the default HTML signature.

  When using just .BODY indents are displayed are correctly however the default signature is not constructed as HTML and I lose the images.

  How should I go about fixing this issue?

  My code:

  sig = .HTMLBody
  body = xlSht.Range("B4").Value
      .To = xlSht.Range("B2").Value
      .CC = ""
      .Subject = xlSht.Range("B1").Value
      .body = body & sig
      .Display
  

  I'd really appreciate your assistance.

  Thanks.
• Yahoo Finance no longer returns VBA cookie request for .getResponseHeader("Set-Cookie")

  The following Excel VBA code segment has worked for years, but stopped working around 28 Apr 2022. I receive the responseText, but the .getResponseHeader("Set-Cookie") returns null.

  Set httpReq = New WinHttp.WinHttpRequest
  
  DownloadURL = "https://finance.yahoo.com/lookup?s=" & stockSymbol
  With httpReq
      .Open "GET", DownloadURL, False
      .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
      .Send
      .waitForResponse
      response = .responseText
      cookie = Split(.getResponseHeader("Set-Cookie"), ";")(0)
  End With
  
• export excel rows to individual json files in python

  My excel file has 500 rows of data. I am trying to get 500 individual JSON files. Each file should have data only from 1 row. Thank you in advance.

  import json
  import pandas
  excel_data_df = pandas.read_excel("F:/2/N.csv.xlsx", 
  sheet_name='Sheet1')
  json_str = 
  excel_data_df.to_json(orient='records')
  for idx, row in enumerate(json_str):
  fpath = str(idx) + ".json"
  with open(fpath, "w+") as f:
  json.dump(row, f)
  
• VBA: Creating a class property that is an array of dictionaries

  In Microsoft Excel VBA I need to create a class that has two properties, "Name" and "Holdings". "Name" is just a string so the code for that is easy. But I need "Holdings" to be a variable length array containing dictionary objects. So this is what I wrote:

  Private mName As String
  Private mHoldings() As Scripting.Dictionary
  
  Public Property Let Name(vName As String)
        mName = vName
  End Property
   
  Public Property Get Name() As String
        Name = mName
  End Property
   
  Public Property Set Holdings(vHoldings() As Scripting.Dictionary)
        Set mHoldings = vHoldings
  End Property
   
  Public Property Get Holdings() As Scripting.Dictionary
        Set Holdings = mHoldings
  End Property
  

  When I try to compile it gives me this error: "Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalide Set final parameter.

  What I doing wrong?
• Excel Counting Repeating Continuous Cells

  This is my first time asking for help in a forum of any kind. hopefully this will be a great experience.

  Problem: Let's say I've got a huge list of people that work for me(200+), and i want to manage fatigue if they have been working everyday, so i can avoid for example 12 shifts in a row.

  This is a hypothetical problem i don't have people working for me lol.

  Anyway, i was wondering if there was a way for me to input the names of people working on different dates (Example every column will be a different date) and insert the names of people working on those specific dates. is there a way for me to count how many times the same person worked consecutive shifts. and for it to reset back if he didn't work 1 day.

  I hope i was clear and on the problem as English isn't my main language.
• Why does the composition of UNIQUE and FILTER distinguish between the actual number zero and the zero returned from empty cells?

  Suppose we have 2 cells, one contains zero and the other is empty, see the range A2:A3 in the figure:

  

  If they pass a FILTER we get two zeros as a result, see the output of FILTER(A2:A3, TRUE) in B2:B3, presented in the next figure. When we apply UNIQUE to the range where the previous result was stored, the output is the alone 0 (see the result in C2). But a composition UNIQUE(FILTER(...)) does not treat these zeros as equal to each other, see the output of UNIQUE(FILTER(A2:A3,TRUE)) in D2:D3:

  

  Why is that? Can we suppress this behavior and get only one zero as a result of the composition?

  ---

  p.s. I work with Office 365, Excel Version 2108
• How do I link the only the formatting between two cells in excel?

  I have two sheets; Sheet1 & Sheet2 and want to link the format from A1 Sheet1 to A1 Sheet2, i.e.

  • Sheet2 A1 should match the formatting of Sheet1 A1

  I don't want to link the value, only the formatting.

  I know I can link the value simply by going to sheet2 and typing =='Sheet1'!A1 in cell A1

  How do I link only the format?